Average to Ignore 0 & #N/A

[Shogun]

Ive done searching and found ignoring 0 all over the place and one for ignoring na but the individual made his 0 na. For graphing reasons i need keep the 0 so i need to the avg to ignore both NA and 0. Whats the best way to do this?

 

[T. Valko]

Ive done searching and found ignoring 0 all over the place and one for ignoring na but the individual made his 0 na. For graphing reasons i need keep the 0 so i need to the avg to ignore both NA and 0. Whats the best way to do this?

Try one of these...

Book1
	A
1	98
2	0
3	31
4	28
5	0
6	_
7	#N/A
8	41
9	#N/A
10	38
Sheet1

This array entered** formula will work in any version of Excel:

=AVERAGE(IF(ISNUMBER(A1:A10),IF(A1:A10<>0,A1:A10)))

** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER.

If you're using Excel 2007 or later...

=AVERAGEIFS(A1:A10,A1:A10,"<>0",A1:A10,"<1E100")

 

[Aladin Akyurek]

Ive done searching and found ignoring 0 all over the place and one for ignoring na but the individual made his 0 na. For graphing reasons i need keep the 0 so i need to the avg to ignore both NA and 0. Whats the best way to do this?

Control+shift+enter, not just enter:

=AVERAGE(IF(ISNUMBER(A2:A40),IF(A2:A40>0,A2:A40)))

will average all positive values from A2:A40.

 

[Shogun]

Control+shift+enter, not just enter:

=AVERAGE(IF(ISNUMBER(A2:A40),IF(A2:A40>0,A2:A40)))

will average all positive values from A2:A40.

Thak but i need to include both positve and negative numbers for functionality.

If you're using Excel 2007 or later...

=AVERAGEIFS(A1:A10,A1:A10,"<>0",A1:A10,"<1E100")

This works great but what does "<1E100" do to this formula or what does it mean?

 

[Aladin Akyurek]

Thak but i need to include both positve and negative numbers for functionality....

Matchematicians do not like the idea of excluding 0's while both positive and negatives are included...

By the way...

=AVERAGE(IF(ISNUMBER(A2:A40),IF(A2:A40,A2:A40)))

will include both...

 

[T. Valko]

Originally Posted by T. Valko
If you're using Excel 2007 or later...
=AVERAGEIFS(A1:A10,A1:A10,"<>0",A1:A10,"<1E100")

This works great but what does "<1E100" do to this formula or what does it mean?

1E100 is scientific notation for a very large number. 1 followed by 100 zeros. 1E100 is a really gigantic number!

It means: average all numbers that are less than 1E100.

The zeros are being excluded as a separate criteria: "<>0".

There is a 100% chance that the numbers you're dealing with will be less than 1E100 so we use that as a criteria to ensure that both positive and negative numbers will be included in the average while at the same time, ignoring the #N/A errors.

 

[Shogun]

Matchematicians do not like the idea of excluding 0's while both positive and negatives are included...

Makes sense. But the data im using a 0 would mean no one came to work so i should excluse it from the weekly avg.