Function equations

J

Judge

Is there a function command that will average a group of numbers while
dropping the lowest number automatically?
 
M

Max

Try something like:

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

Array-enter the formula, i.e. press CTRL+SHIFT+ENTER
instead of just pressing ENTER

Adapt the range to suit
 
P

Peo Sjoblom

A combination of functions can do it, assume the values are in A2:A20 then
you can use this formula

=AVERAGE(LARGE($A$2:$A$20,ROW(INDIRECT("1:"&COUNT($A$2:$A$20)-1))))

entered with ctrl + shift & enter

--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)
 
M

Max

Try something like:
=AVERAGE(IF(A1:A5>MIN(A1:A5),A1:A5))
Array-enter the formula, i.e. press CTRL+SHIFT+ENTER
instead of just pressing ENTER
Click to expand...

Note that the suggested formula will ditch all incidences of the lowest
number in the range, if there should be more than 1 instance of the lowest
number occurring (i.e. duplicate lowest numbers). If you want it to drop
only 1 instance of the lowest number (if there could be duplicate lowest
numbers ocurring), go with Peo's formula.
 
H

Harlan Grove

Max said:
Try something like:

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

Array-enter the formula, i.e. press CTRL+SHIFT+ENTER
instead of just pressing ENTER
Click to expand...
....

If there are multiple instances of the smallest value, this may not produce
the desired result. Indeed, if all cells are equal, this will return #DIV/0!

If the OP only wants to eliminate a single smallest value, then (general)

=SUM(A1:A5,-MIN(A1:A5))/(COUNT(A1:A5)-1)

or if the range has relatively few cells,

=AVERAGE(LARGE(A1:A5,{1;2;3;4}))
 
M

Max

Thanks for the refinements, Harlan.

Yes, the "limitations" of the suggestion was realized,
albeit a little belated <g>, and a follow-up note duly posted ..

Grateful, as always, for your experienced add-ons.
 
Top