#DIV/0! error using Avgerage

N

Naomi

I have a formula that takes a array that includes number grades, drops the
lowest grade and averages them. If there is no data in cell yet i get a
#DIV/0! error is there a way to copy this formula to following cells without
data in them yet and not get this error. The formula Im using is:
=AVERAGE(IF(C13:E13<>MIN(C13:E13),C13:E13))
 
J

JE McGimpsey

One way:

=IF(COUNT(C13:E13)<3,"",AVERAGE(LARGE(C13:E13,{1,2})))


Note, with the formula you're using now, someone who got two or more of
the same low grades would have them BOTH/ALL dropped.
 
R

Roger Govier

Hi Naomi

the formula is an array formula, so needs to be entered or amended using
Control+Shift+Enter (CSE)
When you use CSE, Excel encloses the formula within curly braces { }. Do
not type them yourself
{=AVERAGE(IF(C13:E13<>MIN(C13:E13),C13:E13))}
 
R

Roger Govier

Note, with the formula you're using now, someone who got two or more of
the same low grades would have them BOTH/ALL dropped.

Very good point, John.
 
T

T. Valko

What if there is more than 1 instance of the minimum grade? Should each
minimum grade be excluded?
 
N

Naomi

No even if there is a duplicate minimum number grade only one needs to be
dropped
 
N

Naomi

Works Perfect...Thanks So Much!!! :)


JE McGimpsey said:
One way:

=IF(COUNT(C13:E13)<3,"",AVERAGE(LARGE(C13:E13,{1,2})))


Note, with the formula you're using now, someone who got two or more of
the same low grades would have them BOTH/ALL dropped.
 
T

T. Valko

Try this:

=SUM(C13:E13,-MIN(C13:E13))/MAX(COUNT(C13:E13)-1,1)

If all the cells are empty it will return 0.
 
N

Naomi

I have one other question. The person im making this for has added columns
that might have zeros in them that will need to be included in this formula
as well. How would i modify this formula to not only include the lowest
number and zeros as well

Thanks in advance for the help :)
 
J

JE McGimpsey

One way:

=IF(COUNTIF(C13:G13,">0")<5,"",AVERAGE(LARGE(C13:G13,{1,2,3,4})))

Adjust the '5' and '{1,2,3,4}' to suit the number of added columns.
 
N

Naomi

I adjusted the formula..and for reference there is 11 columns to average
therefore the formula i used was

=IF(COUNTIF(C11:M11,">0")<11,"",AVERAGE(LARGE(C11:M11,{1,2,3,4,5,6,7,8,9,10})))

The formula generated by the cell that i placed the formula in is blank no
zero or error message!.

Any ideas??

Thanks Again!!!
 
J

JE McGimpsey

Your formula works fine for me, and you adjusted it appropriately.

CHeck to see that the values are actually entered as values, not Text.
 
N

Naomi

OMG...what am i doing wrong all the cells are formatted correctly for numbers
and two decimal places. I know its something simple that i am missing

cell c11 = 89.00
d11 = 91.00
e11 = 82.00
f11 = 92.00
g11 = 78.50
h11 = 82.00
i11 = 92.00
j11 = 0.00
k11 = 0.00
l11 = 85.50
m11 = 82.30
n11 = Should be the average of all numbers dropping the lowest
number and all zeros..... Cell is blank and it is formatted for numbers two
decimal places as well.
 
J

JE McGimpsey

Ah - Your first problem statement didn't want a calculation until you
had all 'valid' values, dropping the lowest. The added criterion was
that 0's needed to be ignored - my assumption was that this made them
non-valid values, and therefore there shouldn't be a calculation.

Given that you want the zero's considered valid values, but ignored, and
the calculation to take place when all the cells have numeric values
(including zero) then one way (array-entered: CTRL-SHIFT-ENTER or
CMD-RETURN):

=AVERAGE(IF(COUNT(C11:M11)<11,"",LARGE(C11:M11,
ROW(INDIRECT("1:"&COUNTIF(C11:M11,">0")-1)))))
 

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