Don't want to plot blank cells on my chart
 

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

Don't want to plot blank cells on my chart
 

Stu

Replace "" with NA()

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
DTHIS
www.nickhodge.co.uk


"Stu" wrote in message
...
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

Don't want to plot blank cells on my chart
 

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" wrote in message
...
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

Don't want to plot blank cells on my chart
 

Works!! Thanks!!

"Nick Hodge" wrote:

Stu

Replace "" with NA()

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
DTHIS
www.nickhodge.co.uk


"Stu" wrote in message
...
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

Don't want to plot blank cells on my chart
 

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

"T. Valko" wrote:

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" wrote in message
...
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