I need to take an average of a set of numbers while excluding the 5 highest and the 3 lowest. Is there an easy way?
I need to take an average of a set of numbers while excluding the 5 highest and the 3 lowest. Is there an easy way?
On Mon, 9 Jan 2006 14:57:22 -0600, n_gineer
<[email protected]> wrote:
>
>I need to take an average of a set of numbers while excluding the 5
>highest and the 3 lowest. Is there an easy way?
The *array* formula:
=AVERAGE(LARGE(rng,ROW(INDIRECT("6:"&COUNT(rng)-3))))
To enter an array formula, after typing or pasting it into the formula bar,
hold down <ctrl><shift> while hitting <enter>. Excel will place braces {...}
around the formula.
This formula assumes that a number is only counted once. For example, given
the numbers:
12,12,12,10,10,10,10,10,10,9,9,9,9,9,8,8,7,7,7,7,6,6,5,4,4,4
the formula would exclude the three 12's, two of the 10's and the three 4's
from the average.
Is this what you want?
--ron
n_gineer wrote...
>I need to take an average of a set of numbers while excluding the 5
>highest and the 3 lowest. Is there an easy way?
....
=(SUM(data)-SUM(LARGE(data,{1,2,3,4,5}),SMALL(data,{1,2,3})))/(COUNT(data)-8)
Harlan, yours worked perfect. thank you!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks