Don't want to plot blank cells on my chart

S

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
 
T

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
 
H

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top