Excel chart and 'blanks'

[kibblerok]

Hello all

Say I have some data:

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]

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

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

 

[kibblerok]

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