Make Graph Ignore Formula Output ""

[pgab]

I am building a graphing template for shared use that will pull the graph's data from a table set up with formulas. The users will enter their data in seperate area, and this data will be used in the graphing formulas.

My formuals are IF statements that indicate that if the users' entries are blank, then the cell used in the graphing will have a value of "" (blank). My intent was that the graph would read the blank and leave a gap in the graph. However, it seems that because the cell actually does contains an IF formula, Excel does not treat the cell as blank even though the visible output of the formula is a blank cell. So the graph indicates zero rather than the preferred gap.

Anyway to change the graph's behavior to show a gap. My Hidden and Empty Cell Settings are set to show empty cells as Gaps.

 

[c_m_s_jr]

If you want it to ingore the value you need to have it return #N/A

 

[pgab]

Wow. Thanks for the quick reply. My quest to make things look real clean is working against me in this case...

I see with experimentation what you mean about #N/A. I'll have to spend some time making it work in the scenario of my worksheet, but I do onw see the "trick" so to speak.

Thanks again!!

 

[c_m_s_jr]

Wow. Thanks for the quick reply. My quest to make things look real clean is working against me in this case...

I see with experimentation what you mean about #N/A. I'll have to spend some time making it work in the scenario of my worksheet, but I do onw see the "trick" so to speak.

Thanks again!!

If you don't want to show the #N/A, use conditional formatting to turn the font white when the cell values is #N/A