Results 1 to 5 of 5
  1. #1
    Lounger
    Join Date
    Jan 2001
    Location
    Tucson, Arizona, USA
    Posts
    45
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Not displaying certain datapoints in charts

    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

  2. #2
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    140
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Not displaying certain datapoints in charts

    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....

  3. #3
    Lounger
    Join Date
    Jan 2001
    Location
    Tucson, Arizona, USA
    Posts
    45
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Not displaying certain datapoints in charts

    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[img]/w3timages/icons/smile.gif[/img]), 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 [img]/w3timages/icons/clever.gif[/img] and experimentation [img]/w3timages/icons/frank.gif[/img] project, in addition to keeping track of our bowling averages. I don't know Excel very well, and I'm trying to learn.

    Dan

  4. #4
    Star Lounger
    Join Date
    Jan 2001
    Location
    Newcastle, New South Wales, Australia
    Posts
    81
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Not displaying certain datapoints in charts

    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

  5. #5
    Lounger
    Join Date
    Jan 2001
    Location
    Tucson, Arizona, USA
    Posts
    45
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Not displaying certain datapoints in charts

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •