1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Getting A Graph In Microsoft Office Excel 2007 To Ignore "$0.00"

Discussion in 'Business Applications' started by blujein, Jan 4, 2010.

Thread Status:
Not open for further replies.
Advertisement
  1. blujein

    blujein Thread Starter

    Joined:
    May 12, 2008
    Messages:
    158
    I am desperately hoping that someone can help me with a problem I've got.

    I have a range of values (daily financial figures) that are all made from formulas and external sources (other spreadsheets in the same document). None of these values have been directly entered, and the formulas behind them are quite complex. These values (one in each cell) are all read by a graph - however, for days in the future, the graph violently plummets as it's reading the cell values as "$0.00".

    Can someone tell me how I can prevent the graph from showing the "$0.00" values? Is there a way to get it to ignore the cells with that value?
     
  2. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,445
    I thought we covered this before for you -- guess it must've been another poster.

    Expand your formulas to include an IF, such that: if formula result = zero (or blank), return #N/A, else return formula result -- e.g.:

    =IF(A1=0,#N/A,A1)

    The #N/A results should be skipped, rather than "zero-plotted".

    HTH
     
  3. blujein

    blujein Thread Starter

    Joined:
    May 12, 2008
    Messages:
    158
    It may have been me who you've helped before, but this is a slightly different problem that I'm having (thank you for both sets of replies, by the way).

    So, the formula that you've provided above, will that work when the value it is reading is "$0.00"? The cell itself isn't set to show the value as a currency, that's just the reference it's reading from. Does that make a difference?
     
  4. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,445
    Sorry, I don't understand "the value it is reading is "$0.00"" Are you saying that "$0.00" is text?

    Maybe you could upload a cut-down "desensitised" example.
     
  5. blujein

    blujein Thread Starter

    Joined:
    May 12, 2008
    Messages:
    158
    I may be able to show you what I mean without having to upload the file...

    The cells that the graph is reading from all contain this line of formula:
    Code:
    =IF(ISNA('Daily Turnover (C.Y.)'!G10),"",'Daily Turnover (C.Y.)'!G10)
    So, where the cell, G10, represents a day of the year that is yet to occur, the output of the cell is "$0.00", which the graph obviously reads and shows.
     
  6. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,445
    =IF(ISNA('Daily Turnover (C.Y.)'!G10),#N/A,'Daily Turnover (C.Y.)'!G10)

    ?
     
  7. blujein

    blujein Thread Starter

    Joined:
    May 12, 2008
    Messages:
    158
    That formula still produces the "$0.00" value.
     
  8. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,445
    =IF(('Daily Turnover (C.Y.)'!G10)=0,#N/A,'Daily Turnover (C.Y.)'!G10)

    ?

    Seriously struggling w/out something to look at.
     
  9. blujein

    blujein Thread Starter

    Joined:
    May 12, 2008
    Messages:
    158
    That one returned the "#N/A" value but the graph still displayed it as if it was a zero value.

    I've attached an example spreadsheet that might help.
     

    Attached Files:

  10. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,445
    In A2 of the second sheet,

    =IF('Daily Turnover (C.Y.)'!A2="",#N/A,'Daily Turnover (C.Y.)'!A2)

    and filled across works for me.
     
  11. blujein

    blujein Thread Starter

    Joined:
    May 12, 2008
    Messages:
    158
    That worked on the demonstration file, but not in the original file. Maybe there's something wrong with the graph? It's set to display empty cells as gaps, but it doesn't seem to be doing that.

    I'll try a few more things and get back to you if I can be more specific about the problem. Thank you so much for your help. (y)
     
  12. blujein

    blujein Thread Starter

    Joined:
    May 12, 2008
    Messages:
    158
    Okay, I realised why it wasn't working the way it should - I made a stupid error. I was reading from the wrong cells. The formula that I need to prevent from showing the "$0.00" value is this:
    Code:
    =IF(ISNA(AVERAGE($B$4:R4,MS10:$NB$10))," ",AVERAGE($B$4:R4,MS10:$NB$10))
    Can you show me what to change to make that work?
     
  13. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,445
    Due to 256 columns I have to tweak that to use it.

    All the same, the only error I can get from it is #DIV/0! ... so I don't get the "significance" of the ISNA -- sorry. :(
     
  14. blujein

    blujein Thread Starter

    Joined:
    May 12, 2008
    Messages:
    158
    Ah okay...can I ask how you'd re-write it, anyway? I'm not particularly familiar with what the ISNA reference is or why I originally put it there (it was a while ago).
     
  15. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,445
    Obvious response is -- if the formulas you're using don't return #N/A under any circumstances, you shouldn't be using ISNA.

    While it's mostly guesswork, I did the following for testing.

    1. Entered:

    =IF(ISNA(AVERAGE($B$4:R4,M10:$N$10))," ",AVERAGE($B$4:R4,M10:$N$10))

    in D8.

    2. Entered 1, 2, 3 in A8:C8.

    3. Entered:

    =MATCH(123,E:E,0)

    in B4, purely to return an #N/A.

    4. Charted A8:D8. Point 4 (D8) dropped to zero because the formula in it returned " " because B4 = #N/A.

    5. Then changed D8 formula to:

    =IF(ISNA(AVERAGE($B$4:R4,M10:$N$10)),#N/A,AVERAGE($B$4:R4,M10:$N$10))

    The chart updated. The line stopped at Point 3 = 3, i.e. no longer dropped to zero for P4.

    HTH :)
     
  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/891132

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice