Is there a way to get an average, excluding the lowest number?

T

TNTraining

I need to get an average of a set of numbers and either exclude the lowest
number in the set OR make sure that only numbers over 10,000 are in the
average -- does anyone know how to do this?
 
T

TomHinkle

look up the database functions in excel (function wizard-database functions)

DAverage is the most out of the box solution available.
 
B

Bob Phillips

=AVERAGE(IF(A1:A5<>MIN(A1:A5),A1:A5))

which is an array formula, so commit with Ctrl-Shift-Enter
 
T

TNTraining

Thanks Tom... I tried to use DAverage, however I was not successful -- have
you ever used it?
 
N

Niek Otten

If you really mean OR, so if answering the first half of your question is
OK:

=(SUM(YourRange)-MIN(YourRange))/(COUNT(YourRange)-1)

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel
 
J

JE McGimpsey

One way:

Exclude lowest:

=(SUM(rng)-MIN(rng))/(COUNT(rng)-1)

Exclude <=10000 (array-enter:CTRL-SHIFT-ENTER or CMD-RETURN):

=AVERAGE(IF(rng>10000,rng))
 
J

JE McGimpsey

Note that if there are more than one instance of the minimum value, then
all of the minimum values will be excluded.

i.e., if all the values in the range are the same, this formula will
return #DIV/0!
 
Top