Getting Graphs to ignore Formulas that return ""

[Mel C Roberts]

Please help.

I have a simple line graph that is looking at 12 cells of data. Each of those cells contain a formula which calculates the figure to return. Each cell relates to a month in the year so for instance at the moment July onwards has a formula to calculate the data when it is available but at present there is no data to use.

The graph seems to read the formula as a "0" so the line graph dives down to 0.

Is there anyway in which i can have formulas in cells and nothing showing up on the graph until the formula has calculated a figure which is greater than ""?

Thanks Mel

ary:

 

[Aladin Akyurek]

Re: Getting Graphs to ignore Formulas that return "&quo

Modify the formula to return #N/A instead of a formula-blank (that's, "").

 

[Mel C Roberts]

Re: Getting Graphs to ignore Formulas that return "&quo

But then the data row above the graph has unsightley "#N/A" across the whole row - i need this to show as a blank cell as well.

 

[Aladin Akyurek]

Re: Getting Graphs to ignore Formulas that return "&amp

Mel,

Care to post the formula that returns ""?

 

[Jon Peltier]

Re: Getting Graphs to ignore Formulas that return "&quo

Use conditional formatting to hide the #N/A values. Look up ISNA() in the help files.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
http://www.geocities.com/jonpeltier/Excel/index.html
_______

 

[goblin]

Re: Getting Graphs to ignore Formulas that return "&quo

Mel,

How exactly are you returning #N/A? It should not return "#N/A". You return #N/A with the worksheet function NA()

Goblin

 

[Mel C Roberts]

Re: Getting Graphs to ignore Formulas that return "&quo

Example of the formula being used below.
I have tried using NA() instead of "" but the graph still has lines on it. The only way i can get it to work currently is to delete the formulas and then copy them back in when i need them which is far from ideal.

=IF(I13="","",IF(I13>=0,IF(J13="",SUM($G13:I13),"-")))

 

[Aladin Akyurek]

Re: Getting Graphs to ignore Formulas that return "&amp

Example of the formula being used below.
I have tried using NA() instead of "" but the graph still has lines on it. The only way i can get it to work currently is to delete the formulas and then copy them back in when i need them which is far from ideal.

=IF(I13="","",IF(I13>=0,IF(J13="",SUM($G13:I13),"-")))

Try...

=IF(I13="",#N/A,IF(I13>=0,IF(J13="",SUM($G13:I13),#N/A)))

 

[Mel C Roberts]

Re: Getting Graphs to ignore Formulas that return "&quo

Yep this was what i tried when you first suggested it. However you can see the #N/A in all of the cells where the formula has no specific value.

And the graph does not realise that #N/A measn that it should ignore the value and print nothing as the co-ordinate, it still uses 0,0.

Anyone got any other ideas?

 

[Ekim]

Re: Getting Graphs to ignore Formulas that return "&quo

Mel,

Using Excel XP.

=IF(I13="",N/A(),IF(I13>=0,IF(J13="",SUM($G13:I13),N/A())))

That’s a N/A with back to back open and closed brackets.

Now go to:

1. Tools | Options | Chart
2. In the section headed “Active Chart - Plot empty Cells as:“ ensue that the radio button is clicked for “Not plotted (leave gaps)” and that the you have clicked the check box for “Plot visible cells only”.


HTH

Mike