Charts to ignore null cells!! How?

[Dublevay]

I have an Excel spreadsheet set up with various charts etc. One of my charts
references cells that use a formula similar to:

=IF(ISBLANK(E13),"",E13/E2)

The problem that I have is that even though the cell E13 is blank, the graph
still plots a zero value. Basically, what I want to know is how to get my
charts to ignore "null" values in spreadsheets, when I have selected a
specific range for the chart, and wish to complete it day by day. I.e. I
have 30 days in my monthly chart, but I have only populated 15 of them so
far.

Cheers

JW

 

[Peo Sjoblom]

Use NA() instead of ""

=IF(E13="",NA(),E13/E2)

 

[Dublevay]

Thanks. That works a treat for the charts, but is there any way I can stop
it now showing #N/A on the worksheets?

Cheers

JW

 

[Peo Sjoblom]

Use conditional formatting, select the cell with #N/A, format>conditional
formatting,
if the cell would be H5 select formula is and =ISNA(H5), click the format
button
and select white fonts, click OK twice

 

[immanuel]

You could add custom conditional formatting to your range. Format the font
color to white on this condition (assuming your background color is white)
and you won't see the #N/As.

Your formula would look something like:

=ISNA(A1)

where A1 is the first cell in your range. Then Paste Special the Formatting
to your entire range.

/i.