Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Function equations
Is there a function command that will average a group of numbers while
dropping the lowest number automatically? |
#2
|
|||
|
|||
Try something like:
=AVERAGE(IF(A1:A5MIN(A1:A5),A1:A5)) Array-enter the formula, i.e. press CTRL+SHIFT+ENTER instead of just pressing ENTER Adapt the range to suit -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "Judge" wrote: Is there a function command that will average a group of numbers while dropping the lowest number automatically? |
#3
|
|||
|
|||
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) "Judge" wrote in message ... Is there a function command that will average a group of numbers while dropping the lowest number automatically? |
#4
|
|||
|
|||
Try something like:
=AVERAGE(IF(A1:A5MIN(A1:A5),A1:A5)) Array-enter the formula, i.e. press CTRL+SHIFT+ENTER instead of just pressing ENTER 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. -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- |
#5
|
|||
|
|||
"Max" wrote...
Try something like: =AVERAGE(IF(A1:A5MIN(A1:A5),A1:A5)) Array-enter the formula, i.e. press CTRL+SHIFT+ENTER instead of just pressing ENTER .... 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})) |
#6
|
|||
|
|||
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. -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula to list unique values | Excel Worksheet Functions | |||
I cant use englisch function names in a swedich version of excel | Excel Discussion (Misc queries) | |||
Find a Function to use accross different worksheets | Excel Worksheet Functions | |||
change function variable prompts?? | Excel Worksheet Functions | |||
Counting Function Dilemma | Excel Worksheet Functions |