How do I average a group of cells and ignore a #div/0! error?

[Scott R]

Him You Them Us Me
12 10 8 6
12 11 7 4
11 9 6 9
average 11.6 10 7 6.3 #DIV/0!

How can you average the cells in the bottom row and ignore the #div/0! Error
to get the answer 8.76?

 

[Jacob Skaria]

Assuming you have the totals in row5 B5 to F5 try the below formula

Please note that this is an array formula. Within the cell in edit mode (F2)
paste this formula and press Ctrl+Shift+Enter to apply this formula. If
successful in 'Formula Bar' you can notice the curly braces at both ends like
"{=<formula>}"

=SUM(IF(ISERROR(B5:F5),0,B5:F5))/COUNTIF(B5:F5,">0")

If this post helps click Yes

 

[Jacob Skaria]

Assuming you have the totals in row5 B5 to F5 try the below formula

Please note that this is an array formula. Within the cell in edit mode (F2)
paste this formula and press Ctrl+Shift+Enter to apply this formula. If
successful in 'Formula Bar' you can notice the curly braces at both ends like
"{=<formula>}"

=SUM(IF(ISERROR(B5:F5),0,B5:F5))/COUNTIF(B5:F5,">0")

If this post helps click Yes

 

[Jacob Skaria]

If you are using 2007 try =AVERAGEIF()

 

[Jacob Skaria]

If you are using 2007 try =AVERAGEIF()

 

[T. Valko]

Try this array formula** :

=AVERAGE(IF(ISNUMBER(A5:E5),A5:E5))

** 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.

 

[T. Valko]

Try this array formula** :

=AVERAGE(IF(ISNUMBER(A5:E5),A5:E5))

** 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.

 

[Jim Thomlinson]

Average the source data and not the averages. Note that averges of averages
can get a little troublesome depending what you want to do.
=average(B2:F4)

If you have 100 values of 1 it's average is 1.
If you have 1 value of 100 it's average is 100.
Average of Averages is (1 + 100) / 2 = 101 / 2 = 50.5
Total average is (1+1+1+... + 100) / 101 = 200/101 = 1.98

Obvioulsly not the same...

 

[Jim Thomlinson]

Average the source data and not the averages. Note that averges of averages
can get a little troublesome depending what you want to do.
=average(B2:F4)

If you have 100 values of 1 it's average is 1.
If you have 1 value of 100 it's average is 100.
Average of Averages is (1 + 100) / 2 = 101 / 2 = 50.5
Total average is (1+1+1+... + 100) / 101 = 200/101 = 1.98

Obvioulsly not the same...