Ignoring items in graph data

[ajcolyer]

How can I make Excel ignore zero values in graph data for a line graph. Note that this is not the same as ignoring blank spaces, which can be done through the options menu. I want to use a logic function that will leave a zero, or something else (e.g., ""), and thus won't cause the line to drop to zero at that point. Thanks.

 

[Aladin Akyurek]

How can I make Excel ignore zero values in graph data for a line graph. Note that this is not the same as ignoring blank spaces, which can be done through the options menu. I want to use a logic function that will leave a zero, or something else (e.g., ""), and thus won't cause the line to drop to zero at that point. Thanks.

Create an additional column with...

=IF(A2,A2,#N/A)

and use this columns as part of the source data.

 

[PA HS Teacher]

AJ,
I believe using the autofilter to filter the zero's out of your data will also accomplish this. You could similarly use filtering to quickly remove outliers above a certain threshold.

 

[ajcolyer]

Thank you both for your quick replies. I have tried to use "#N/A" instead of "", and this seems to work if the item is in the middle of a data set; however, it does not seem to work if it is the first item in the data set. Any thoughts? Thanks again.

Andy

 

[Aladin Akyurek]

Thank you both for your quick replies. I have tried to use "#N/A" instead of "", and this seems to work if the item is in the middle of a data set; however, it does not seem to work if it is the first item in the data set. Any thoughts? Thanks again.

Andy

Maybe you could substitute the mean for the first 0 value instead of #N/A...

Book6
	A	B	C	D
1			31.83333
2	Y	Xx	X
3	65	31.83333	0
4	67	20	20
5	68	25	25
6	73	#N/A	0
7	68	30	30
8	69	29	29
9	62	#N/A	0
10	74	47	47
11	60	40	40
12
Sheet1

B3:

=IF((C3=0)*(COUNT($C$3:C3)=1),$C$1,IF(C3=0,#N/A,C3))

C1:

=AVERAGE(IF(C3:C11,C3:C11))

which must be confirmed with control+shift+enter instead of just with enter.

Not sure whether all this could be taken to be a sound procedure.