Create 'blank' on Graph instead of 0 value?

[Cerestes]

Short Question:

What can I have the IF statement return as a result that will NOT graph at all. Just leave a hole in the graph.

Long Question:
I have a large range of values and dates.
1/1 1/2 1/3 ....etc
10 11 7 .......etc
8 12 6 .....etc

These values are pulled via VLOOKUP() from various places.
When VLOOKUP finds a blank cell, it returns a '0', which doesn't work for me.
I've added an IF(ISBLANK), to return "", which is (I believe) an empty cell.
The problem is, this is still graphing as a zero. So it will be graphing along nicely, and then shoot down to zero and back up again.

Thanks!

 

[Cerestes]

Any ideas?
I would like a result an IF statement can return, that is TRUE for ISBLANK().

I've tried null characters (Alt-255), spaces ("", " ") etc.
All return as 'false' for ISBLANK(), and when graphed, drop my graph to zero for that value.

 

[dantb]

Something like this maby ?

=IF(A1<>"",FALSE,TRUE)

or =IF(A1<>0,FALSE,TRUE)

 

[Cerestes]

I'm not sure how I would make that work.

The cell currently has this:
=IF(ISBLANK(VLOOKUP($A93,ACCESS,45,FALSE))," ",VLOOKUP($A93,ACCESS,45,FALSE))

The idea is that if the VLOOKUP value is not blank, it returns that value.
If it is blank, it needs to return SOMETHING. (Anything!) that shows up in the cell as a blank AND doesn't graph.

" " shows up in the cell as blank, but it graphs as a 'zero', which makes my graph look wrong.

 

[Aladin Akyurek]

...

" " shows up in the cell as blank, but it graphs as a 'zero', which makes my graph look wrong.

That's a space, i.e., text with a length of 1. What kind of graph are you making?

 

[PaddyD]

Also, when you say you don't want to plot the zeros, do you mean you don't want to plot the entire point, or do you just want the graph to include a gap?

 

[Cerestes]

I do want to graph zeros - I don't want to graph 'empty' cells.
Ideally, it would simply be a missing spot in the graph.

It works kind of like this.
I have a huge calendar (2002-2006) with 1 value per week for each of ~30 columns.

Depending on what selection is made on the summary sheet, HLOOKUP is used to pull data from 5 of those 30 columns.

Right now, 2006 is empty. On a continous basis, we add data to those rows.
When HLOOKUP grabs data for 2006, and found those cells blank, it returned a 0. I don't want it to return 0 (some values I do want are 0's).
To combat this, I used an IF statement, that if the cell was blank, return "".
This at least leaves the cleaned up data a bit better.
This table that is created by HLOOKUP is then used to create a simple line graph. The problem is, "" is being graphed as 0. So the line runs along nicely, and then drops to 0 for all of 2006.

Is there any value that my IF statement can return instead of a space ("")that will NOT graph out?

 

[UDcc123]

I love this site

Isn't this site awesome? Here was a post from almost 3 years ago that gave me the answer I was looking for!!!

 

[Jon Peltier]

What?? It's a post from one year ago, and there's no answer in the thread.

The answer for a line or XY chart is to return NA(). This is not plotted as a point on the chart, and if the series has lines connecting the points, it will draw the line from before the missing point to after it. You get an ugly #N/A error in the cell, but conditional formatting can hide this.

 

[Todd R K]

Jon

Can you elaborate? I have a chart set up to capture monthly data and it is plotting a zero for the month of December since there is no data yet.

How do I get the cell to return an #N/A so that it will not plot anything until the data is available?

Thank you

Todd