Charts and zero data points

    When using a line chart, there are three options to show "zero data points". Click the chart > Tools > Options >Chart (tab), and then at the top, it has the three for empty cells (Not-plotted, zero, interpolate).


    I have Walkenbach's sample from his book Excel Charts, and it works as it should. However, in my work .xls file, following that procedure does not change anything, the charts are always plotted as zero. And if I move a copy of Walkenbach's charts into this file, it works. But if I copy that chart into the worksheet I'm using, and change the references to this worksheet, it will not change from the zero-plotting.


    Any ideas on what is wrong?


    Software: OpenOffice 3.0/NeoOffice 3.0 on Mac OS X 10.5.6
    Humanware: Older than dirt


    Old, slow, and confused - but at least I'm inconsistent!


    Rich
    (retired Excel 2003 user, 3.28.2008)

    Just played a bit, and I can replicate your problem if I change the empty cells to text by typing a (non-displaying) single quote into the cell. If I select the cell and delete its contents the empty cell formatting works again.

    Thanks, thomach, that works. But suppose I have a formula in each cell (which I do):


    =IF(B10="N/A","",A10/B10)


    This will always have a "value" even though the formula shows no value.


    Software: OpenOffice 3.0/NeoOffice 3.0 on Mac OS X 10.5.6
    Humanware: Older than dirt


    Old, slow, and confused - but at least I'm inconsistent!


    Rich
    (retired Excel 2003 user, 3.28.2008)

    Okay, if I change the formula to:


    =IF(A10="N/A",#N/A,A10/B10)


    Then it works okay. Putting "nothing" in the cell leaves something in the cell. But the chart automatically understands the #N/A (without the quotes in the formula).


    Interesting. I learned something and I solved my immediate problem.


    Thanks, thomach.


    Software: OpenOffice 3.0/NeoOffice 3.0 on Mac OS X 10.5.6
    Humanware: Older than dirt


    Old, slow, and confused - but at least I'm inconsistent!


    Rich
    (retired Excel 2003 user, 3.28.2008)

    I suggest it is better to use NA() function instead of entering #NA..


    So in your example, =IF(A10="N/A",#N/A,A10/B10)
    I will suggest =IF(A10="N/A",N/A(),A10/B10)...


    Hope this helps...
    http://peltiertech.com/Excel/Charts/index.html


    Take it from me.... If you have not seen this site, PLEASE DO.


    You can see LOT of these chart related examples at

    Thanks: ~Yogendra

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login