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,716
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,716
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,082,246
Messages
5,363,998
Members
400,773
Latest member
jwolfe

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top