Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Averaging excluding min and max numbers
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? -- n_gineer ------------------------------------------------------------------------ n_gineer's Profile: http://www.excelforum.com/member.php...o&userid=29159 View this thread: http://www.excelforum.com/showthread...hreadid=499535 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Averaging excluding min and max numbers
On Mon, 9 Jan 2006 14:57:22 -0600, 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? 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Averaging excluding min and max numbers
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) |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Averaging excluding min and max numbers
Harlan, yours worked perfect. thank you! -- n_gineer ------------------------------------------------------------------------ n_gineer's Profile: http://www.excelforum.com/member.php...o&userid=29159 View this thread: http://www.excelforum.com/showthread...hreadid=499535 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|