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

  • Thread starter Thread starter Scott R
  • Start date Start date
S

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?
 
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
 
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
 
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.
 
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.
 
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...
 
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...
 
Back
Top