Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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 |
#2
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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 |
#3
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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 |
#4
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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 |
#5
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
blank cells plot as zero values | Charts and Charting in Excel | |||
How, on a line chart, to skip blank entry rather than plot as 0? | Charts and Charting in Excel | |||
How do I force a line chart to not plot cells that are "null". | Charts and Charting in Excel | |||
Strange Problem with Chart and Plot Visible Cells Option | Charts and Charting in Excel | |||
Blank cells in a chart | Excel Worksheet Functions |