Excel chart and 'blanks'

kibblerok

kibblerok

kibblerok

Soldato
Joined
21 Nov 2002
Posts
5,011
Location
Manchester
Hello all

Say I have some data:

Code: 
Date                 Value
01/01/08             29.0%
02/01/08             28.0%
03/01/08             
04/01/08             44.0%
05/01/08             18.0%


The percentage is calculated in a formula.... If(B2=0,"",B2/C2)


how on a line graph would I show all the dates but now allow excel to shoot down to zero on the 3rd when the data is blank (the store was closed)?

I know "" doesnt technically return an empty string so i have tried returning NA() as various sites suggest but that seems to connect the gap between the 2nd and 4th even though show empty as gaps is set.

If i simply delete the formula from the 3rd the gap appears, however thats not a solution.

Any ideas?
 
kibblerok said:
Hello all

Say I have some data:

Code: 
Date                 Value
01/01/08             29.0%
02/01/08             28.0%
03/01/08             
04/01/08             44.0%
05/01/08             18.0%


The percentage is calculated in a formula.... If(B2=0,"",B2/C2)


how on a line graph would I show all the dates but now allow excel to shoot down to zero on the 3rd when the data is blank (the store was closed)?

I know "" doesnt technically return an empty string so i have tried returning NA() as various sites suggest but that seems to connect the gap between the 2nd and 4th even though show empty as gaps is set.

If i simply delete the formula from the 3rd the gap appears, however thats not a solution.

Any ideas?
Click to expand...

Hmm, not an expert with excel by any means but could you perhaps use data --> filter --> autofilter (done on the top cell of the column). Then just hide the blanks :D
 
Eulogy said:
Hmm, not an expert with excel by any means but could you perhaps use data --> filter --> autofilter (done on the top cell of the column). Then just hide the blanks :D
Click to expand...

Only problem is I want to plot the blanks and leave a gap where data is... plus I want to just paste the data into a data tab and the individual sheets do the rest automatically with lookups etc

The only thing left remaining is this graph gap issue :(
 
You could change the code to the below

=if(ISERROR(B2/C2), 0, B2/C2)

Which would give you 0% in the cells where otherwise you'd have a div/0 error, and therefore when you graph it, it would give the desired effect.

The only problem with that is if you need to distinguish between a 0% caused by no data and a 0% driven by data in the spreadsheet.
 
cheers for the replies.

I want the gaps to be there... in this file ... the bottom graph is what im after but im only able to achieve this when the cell is completely empty with no formula

The first one interpolates with #n/a... this wouldn't be so bad if the interpolated lines could be changed colour or changed to a dash automatically between those points
 
I don't know how to achieve what you want, but the real question I've got to ask is why would you want to do it? If you're looking for a trend over time, then you want it interpolated, if you're looking for the figures for each week as a visual thing but aren't concerned about the trends so much (or can read other types of graphs) a bar graph would be a better solution than a line one.
 
Impossible apparently with line graphs

This link talks about it, you can't get truly blank values in cells with formulas, you could delete all values that contain certain results using the instructions at the end of the document though, but it's manual work

http://excel.tips.net/Pages/T002814_Returning_a_Blank_Value.html

Possibly some kind of macro could empty the cells but that's above my knowledge with Excel :)
 
Last edited:
You must log in or register to reply here.
Back
Top Bottom