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

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?
 
J

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
 
T

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

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top