# Charting, Formulas showing as zero value

#### Zorax

##### Board Regular
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

### Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
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

I'll see if I can change the formula !

Zorax said:
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)

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

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

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

Zorax said:

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

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

=ISNA(Cell)

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

Zorax

Replies
3
Views
364
Replies
6
Views
211
Replies
0
Views
253
Replies
7
Views
448
Replies
4
Views
363

1,203,667
Messages
6,056,651
Members
444,880
Latest member
Kinger1968

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back