Using Formulas with Charts

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Sandybam - 24 Aug 2006 01:07 GMT

I have a chart that references cells with the following formula
=if(a1="","",a1/a2). The formula is used for each month Jan-Dec. I would
like to copy the formula across all months. When I do this my data point
show as zero for months that have not happened yet. What can I do to not
show a line on the chart for a data point with a "" or null value?

Thank you,
Sandy

Reply to this Message

Jon Peltier - 24 Aug 2006 02:19 GMT

The cell with the formula isn't blank, and "" does not produce a blank.
There is not a formulaic way to simulate a blank, but for many cases, a line
chart or XY chart will treat NA() as a blank. It produces an ugly #N/A error
in the worksheet, but it isn't plotted, and you can pretty up the worksheet
with conditional formatting:

http://contextures.com/xlCondFormat03.html#Errors

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______

>I have a chart that references cells with the following formula
> =if(a1="","",a1/a2). The formula is used for each month Jan-Dec. I would
[quoted text clipped - 4 lines]
> Thank you,
> Sandy

Reply to this Message