graphing over blank empty cells ... i.e., skipping blank cells

gary747

New Member
Joined
Jul 10, 2015
Messages
4
I have created a graph to show readings over time and I use the option to skip over empty or blank cells.
Problem is that my formula to create empty cells is not creating truly empty or blank cells.
The example formula is =IF(A1=3,A1,"") ... The =IF statement checks cell A1 and if it finds anything other than 3 then I want the formula to return an empty cell.
My graph presently sees the "" as a "0" and causes the graph to use "0" for the data point. If I go back and manually clear the contents the graph works great. So what can I use for the if false code side to return a truly blank or empty result so the graph skips over this cell if it is blank?
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,722
Office Version
2010
Platform
Windows
Welcome to the board.

Try =IF(A1=3, A1, NA())
 

gary747

New Member
Joined
Jul 10, 2015
Messages
4
That works great for the graph.... It leaves #N/A in the cell... it there a way to leave the cell blank? if not I can live with the #N/A because my graph is now working. Thanks.
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,722
Office Version
2010
Platform
Windows
No way I know of.

You're welcome.
 

jorismoerings

Well-known Member
Joined
Jul 4, 2014
Messages
1,263
hi,

Are you asking to have it blank because of the showing of the na() in your data range. As that's the case you might solve that by conditional formatting your data range with a formula which formats the cell-font to white (or any other color depending the background color)
 

gary747

New Member
Joined
Jul 10, 2015
Messages
4
Yes, the graph is working as intended now but the data range is now showing #N/A as a result of the formula. So now the column results either show a number (which is what I want) or if it is a blank it is now showing #N/A, not sure how to use conditional formatting that would color the cell white if it sees the #N/A but that would solve this issue.
 

gary747

New Member
Joined
Jul 10, 2015
Messages
4
OK I figured it out.... First make the cells all white so nothing shows up... then use conditional formatting that sets everything above 1 to black text and it now only shows the values in the results.....
THANKS to everyone who contributed :) YOU MADE MY DAY...
 

Forum statistics

Threads
1,085,289
Messages
5,382,740
Members
401,802
Latest member
JodieInCanada

Some videos you may like

This Week's Hot Topics

Top