Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    Feb 2002
    Location
    Fort Collins, Colorado, USA
    Posts
    61
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Plotting blank cells in a chart (2000)

    I am making an xy scatter plot. The y range cells have a formula of the type IF(condition, number, ""). So, some of the cells (the "legal" ones) have a number to be plotted, while others (the "invalid" ones) contain nothing. The problem is how the "nothing" is interpreted. I can apply a numerical formula to the range, such as to compute the average value, and the "nothing" cells are indeed ignored like they should be (the result is the average of the valid cells, as opposed to the average of all cells). But in the scatter plot, the "nothing" cells are interpreted as "zeros," meaning that they generate a plot symbol at the zero position. Is there any way to get around this, and have the "nothing" cells ignored in the chart?

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Plotting blank cells in a chart (2000)

    Use
    IF(condition, number, na())

    The na() is the ONLY non number that is NOT interpreted by the chart as a zero. It will be ignored and the points before and after wil be connected.

    Steve

  3. #3
    Star Lounger
    Join Date
    Feb 2002
    Location
    Fort Collins, Colorado, USA
    Posts
    61
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Plotting blank cells in a chart (2000)

    Thanks Steve! I had a feeling there was a function like this, I just couldn't find it.
    Unfortunately, while it solves my plotting problem, it destroys my AVERAGE calculation--The average formula now returns a #N/A error.
    So I'm afraid my problem remains: How can I get Excel to avoid the "invalid" cells in both numerical calculations AND charts?

  4. #4
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Plotting blank cells in a chart (2000)

    You have now to introduce a different AVERAGE formula:

    =AVERAGE(IF(Range,Range))

    which you need to confirm with control+shift+enter, instead of just enter.
    Microsoft MVP - Excel

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Plotting blank cells in a chart (2000)

    Don't you mean something like this: (confirm with ctrl-shift-enter)
    =AVERAGE(IF(NOT(ISNA(range)),Range))

    =AVERAGE(IF(Range,Range))
    will give #na error with #na and #value with any text values

    Steve

  6. #6
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Plotting blank cells in a chart (2000)

    I meant indeed to post:

    =AVERAGE(IF(ISNUMBER(Range),Range))

    which must be confirmed with control+shift+enter, not just enter.

    Thanks for pointing out the omission.
    Microsoft MVP - Excel

  7. #7
    Star Lounger
    Join Date
    Feb 2002
    Location
    Fort Collins, Colorado, USA
    Posts
    61
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Plotting blank cells in a chart (2000)

    Thanks everybody, you've been most helpful!

Posting Permissions

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