Not displaying certain datapoints in charts

Topic

New Reply

I have a spreadsheet that I use to track the scores and averages of the members of my bowling team. The most useful number on this sheet is the average for the season, which determines each bowler’s handicap. I’ve got the spreadsheet calculating the average correctly, and it leaves the cell blank if the bowler was absent on any given week (missed games don’t affect the average).

Then I went and created a chart to display all four team member’s averages over the course of the season. The blank averages presented me with two problems (well, the same problem, but I suspect two different answers): the blank cells get charted as zeros, which looks really bad on the chart.

So, how do I get the chart to not plot those datapoints that are blank? I would want (if at all possible) for the blank gaps to be not plotted, with the chart connecting the points on either side of the gap. Secondly, I would want the chart to just end with the most recent week we’ve bowled, and not show a line that goes until the end of the season.

I did try changing the cell formula so that it would put the previous week’s average, rather than blank, if the bowler was absent, and this was better than having them plot as zeros, but not much.

Any ideas?
Thanks,
Dan

Reply | Quote

Replies

Try making the averages the following way:

=IF(IsNumber(B1),AVERAGE(B1:B30),NA())

Explanation: AFAIK the only return which is ignored by the chart is either absolutely empty cell (*) or the NA() function. In my example I assume that if a person plays a certain day it will be present for the first game. The drawback is if you calculate the average of the average the result will be #NA.

Therefore, here some alternatives:

a) Make your averages by a VBA Sub which will _write_ the average into the appropriate cells and leaving the others empty.
I do not want to offend you, but there are probably dozzens of similar programs to be found in the internet.

(*) If only “” would be considered as empty by Excel….

Reply | Quote

My formula (incorporating your NA() idea) is:

=IF(SUM(C11:E11)>0,TRUNC(AVERAGE($C$2:E11)),NA())

(This is the formula for week 10; cell references change depending on the week.)
Columns C,D,E hold the individual game scores. So, if any of the three games is entered (it’s possible for a bowler to miss part of a night, and only bowl one or two games), it averages all of the games played so far.

The NA() solution works perfectly for the chart (so thanks for that), but now the cells that were blank show #NA. Is that inevitable? I can only get a pretty chart or a pretty spreadsheet, without resorting to VBA?

Anybody care to help with a VBA sub to do what cri suggests? I’ve never done VBA (yet; I am a programmer by profession, so I could probably figure it out, given enough time).

And, yes, I know that there are probably tons of programs to do this for me (and no, you didn’t offend me), but I’m using this particular spreadsheet as a learning and experimentation project, in addition to keeping track of our bowling averages. I don’t know Excel very well, and I’m trying to learn.

Dan

Reply | Quote

Another option that sometimes works is: In Tools Options Chart, check one of – ‘Not Plotted (leave gaps)’, or ‘Zero’ or ‘Interpolated’.
Maybe one of these will suit.
Ruth

Reply | Quote

I had already found and tried the Not Plotted options. They had no effect on the chart, since, as was previously pointed out, those cells were not actually blank.

For those who might be interested, I solved the problem as follows: I created a new column and set it’s formula to the one I specified in my previous post
=IF(SUM(E2:G2)>0,TRUNC(AVERAGE($C$2:E2)),NA())
and set it’s font color to the same color as the background, so it wouldn’t show (I tried just making it a hidden column, but then the values from that column didn’t show on the chart).

Then, I changed the formula on my displayed averages column to
=IF(ISNA(K2),””,K2)
This displays the spreadsheet the way I want it, with blank cells for those weeks not bowled. Then I changed the chart to refer to the new, “hidden” column, rather than the one you see on the spreadsheet. Voila! Everything displays the way I want!

Thanks for all your help!
Dan

Reply | Quote