Thread: Graphs returning to zero when no value present

Hi there. I'm creating line graphs in Excel, where each record does not necessarily have a value for each point on the X-axis. The X-axis is date, the Y-axis the value that appears on that date. Measurements for each record were taken on different dates - hence every point on the Y only has some values.

Date		A	B	C	D	E	F
03/02/10	0	0	0		0	0
10/02/10				0		
17/02/10		-1				-1
19/02/10			-5		2	
03/03/10	0	-1	-5			
08/03/10			-5			
17/03/10	0	-1		0	4	-4
30/03/10	0	-2	-12	-1		-4
13/04/10	0		-15		6	-6

When I create a line graph from this, on the point for which there are null values, the line returns to zero. This gives a totally misleading picture.

What I want is for that line to simply skip a value for that date, and continue straight to the next actual value. Can anyone suggest a solution?

I should add, I've already tried changing the Hidden and Empty cell settings to "Connect data points with line". It doesn't seem to change anything.

What I have to do is manually go thourgh each empty cell and delete the "contents". But there is no content in any of these blank cells. I've copied and pasted between applications just to make sure. The cells are empty...but the graph reads them as zero.

I'm confused. Is this a bug?

Originally Posted by shg

Put =NA() in the blank cells.

Thanks for your reply shg...unfortunately I don't think it's worked!

I'm calculating the figures by formula. So now I have a formula that puts "=NA()" in a cell where appropriate. The graph I produce from this is the same - =NA() values always return to the zero line.

When I overwrite the formula for those cells with "=NA()" the graph works as it should. But the formula calculated value doesn't do this. I still have to manually change each of these cells. For the small example above this isn't a problem...but I have larger examples with thousands of cells...

I've also tried copying the whole set and pasting Values only, to get rid of the formula. The same thing happens. Only manually changing each cell works.

This is getting a bit silly. What's going on?!

Date		Average	A	B	C	D	E	F
03/02/10	0	0	0	0	0	0	0
10/02/10	0	=NA()	=NA()	=NA()	0	=NA()	=NA()
17/02/10	-1	=NA()	-1	=NA()	=NA()	=NA()	-1
19/02/10	-1.5	=NA()	=NA()	-5	=NA()	2	=NA()
03/03/10	-2	0	-1	-5	=NA()	=NA()	=NA()
08/03/10	-5	=NA()	=NA()	-5	=NA()	=NA()	=NA()
17/03/10	-0.2	0	-1	=NA()	0	4	-4
30/03/10	-3.8	0	-2	-12	-1	=NA()	-4
13/04/10	-3.75	0	=NA()	-15	=NA()	6	-6