Don't want to plot blank cells on my chart

[Stu]

This is the formula in a cell which is plotted as a data point on a chart

=IF(B12=0,"",(100-(B13/(SUM(B12:B13))*100)))

It works fine, but I don't want the chart to plot it if the cell is blank ("")
I went into Tools - Options - Chart and selected Plot Empty Cells As Not
plotted
But it still plots the empty cell as zero
Please advise

 

[Nick Hodge]

Stu

Replace "" with NA()

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
www.nickhodge.co.uk

 

[T. Valko]

The cell isn't empty, it contains a formula.

Try this:

=IF(B12=0,NA(),(100-(B13/(SUM(B12:B13))*100)))

That'll return a #N/A "error" but will be ignored in the chart.

If you don't want to see the #N/A's displayed in the cells you can use
conditional formatting to hide them by setting the font color to be the same
as the fill color.

Condtional Foramtting
Formula Is: =ISNA(cell_ref)
Set the font color to be the same as the fill color

Biff

 

[Stu]

Works!! Thanks!!

Stu

Replace "" with NA()

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
www.nickhodge.co.uk

 

[hb]

On this note, I am having difficulty because excel is still plotting when
there is a NA in the cell. E.g. i have selected 75 rows, i only want excel to
plot when a cell is populated. I set up
=IF($B4=$A4,NA(),VLOOKUP($B4,'190'!$C$1:$AI$302,7,"FALSE")) for Y values and
=IF(B4=A4,NA(),B4) for X-values. until there is actually something to plot NA
is there, but excel still plots it on the chart.

any help is appreciated.

thanks