Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
n_gineer
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Ron Rosenfeld
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Harlan Grove
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
n_gineer
 
Posts: n/a
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On



All times are GMT +1. The time now is 10:27 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"