Make Graph Ignore Formula Output ""

pgab

New Member
Joined
Dec 8, 2009
Messages
2
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.
 

Some videos you may like

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

c_m_s_jr

Well-known Member
Joined
Mar 23, 2009
Messages
1,561
If you want it to ingore the value you need to have it return #N/A
 

pgab

New Member
Joined
Dec 8, 2009
Messages
2
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

Well-known Member
Joined
Mar 23, 2009
Messages
1,561
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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,756
Messages
5,626,675
Members
416,200
Latest member
Pulsar3000

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top