Ignoring items in graph data

ajcolyer

New Member
Joined
Jan 23, 2005
Messages
19
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.
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
ajcolyer said:
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

Well-known Member
Joined
Jul 17, 2004
Messages
2,838
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

New Member
Joined
Jan 23, 2005
Messages
19
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
ajcolyer said:
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
ABCD
131.83333
2YXxX
36531.833330
4672020
5682525
673#N/A0
7683030
8692929
962#N/A0
10744747
11604040
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.
 

Forum statistics

Threads
1,147,666
Messages
5,742,510
Members
423,734
Latest member
123hmMission

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top