Charting, Formulas showing as zero value

[Zorax]

Hi

I'm trying to create a graph that plots H1:H33 as a series. However, these cells contain formulas (=IF(F9>0,F9/C9,"") that appear on the graph as a zero value when the formulas do not calculate a numberical number. Is there a way to stop this happening so that only calculated numberical values appear and the formulas do not have a zero value?

Many thanks

 

[Fergus]

Hi Zorax,

If the formula is returning the "" part, Excel reads that numerically as zero, so zero is what it will plot. The only way that I know, short of searching the web for special charts, is to delete the formula where it returns "" and then go to Tools > Options, select the "Chart" tab and under the section "Plot empty cells as" check the "Interpolated" box.

HTH

 

[Zorax]

Many thanks for your reply.

I'll see if I can change the formula !

 

[Aladin Akyurek]

Hi

I'm trying to create a graph that plots H1:H33 as a series. However, these cells contain formulas (=IF(F9>0,F9/C9,"") that appear on the graph as a zero value when the formulas do not calculate a numberical number. Is there a way to stop this happening so that only calculated numberical values appear and the formulas do not have a zero value?

Many thanks

Depending on the chart type, you might want also try...

=IF(F9>0,F9/C9,#N/A)

instead of:

=IF(F9>0,F9/C9,"")

BTW, you don't seem to check whether C9 is a non-zero value.

 

[Zorax]

Hi Thanks for the reply.

I was just trying that as you replied. Using NA() works for the graph but is there a way of hiding this value in the spreadsheet itself. Until data is entered and the formula is calculated, I have an unsightly column showing rows of #NA

Thanks in advance

 

[Aladin Akyurek]

Hi Thanks for the reply.

I was just trying that as you replied. Using NA() works for the graph but is there a way of hiding this value in the spreadsheet itself. Until data is entered and the formula is calculated, I have an unsightly column showing rows of #NA

Thanks in advance

Try to whiten #N/A cells using conditional formatting with the formula...

=ISNA(Cell)

 

[Zorax]

Perfect...many thanks. I really appreciate your help.

Zorax