ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)
-   -   Function: Ignore Mimimum and Maximum Values (https://www.excelbanter.com/excel-worksheet-functions/75520-function-ignore-mimimum-maximum-values.html)

Michael J

Function: Ignore Mimimum and Maximum Values
 
Is there a spreadsheet function to calculate the average, ignoring a single
minimum and maximum value. This is similar to what they use in the olympics
to calculate a score.

Example: for this list:

A1: 10
A2: 10
A3: 8
A4: 7
A5: 6
A6: 6
A7: 6

I would need to average only cells A2 through A6, ignoring a single max and
min value. The list is normally not sorted and the the number of values is
not constant.
Is there a statistical calulcation to do this?

Duke Carey

Function: Ignore Mimimum and Maximum Values
 
=(SUM(range)-MAX(range)-MIN(range))/(COUNT(range)-2)


"Michael J" wrote:

Is there a spreadsheet function to calculate the average, ignoring a single
minimum and maximum value. This is similar to what they use in the olympics
to calculate a score.

Example: for this list:

A1: 10
A2: 10
A3: 8
A4: 7
A5: 6
A6: 6
A7: 6

I would need to average only cells A2 through A6, ignoring a single max and
min value. The list is normally not sorted and the the number of values is
not constant.
Is there a statistical calulcation to do this?


JE McGimpsey

Function: Ignore Mimimum and Maximum Values
 
One way:

=TRIMMEAN(A1:A7, 2/7))

For more or fewer numbers, this can be generalized as

=TRIMMEAN(A:A,2/COUNT(A:A))



In article ,
Michael J <Michael wrote:

Is there a spreadsheet function to calculate the average, ignoring a single
minimum and maximum value. This is similar to what they use in the olympics
to calculate a score.

Example: for this list:

A1: 10
A2: 10
A3: 8
A4: 7
A5: 6
A6: 6
A7: 6

I would need to average only cells A2 through A6, ignoring a single max and
min value. The list is normally not sorted and the the number of values is
not constant.
Is there a statistical calulcation to do this?


Michael J

Function: Ignore Mimimum and Maximum Values
 
Thanks JE. Your general works fine.
---------------------------------------------


"JE McGimpsey" wrote:

One way:

=TRIMMEAN(A1:A7, 2/7))

For more or fewer numbers, this can be generalized as

=TRIMMEAN(A:A,2/COUNT(A:A))



In article ,
Michael J <Michael wrote:

Is there a spreadsheet function to calculate the average, ignoring a single
minimum and maximum value. This is similar to what they use in the olympics
to calculate a score.

Example: for this list:

A1: 10
A2: 10
A3: 8
A4: 7
A5: 6
A6: 6
A7: 6

I would need to average only cells A2 through A6, ignoring a single max and
min value. The list is normally not sorted and the the number of values is
not constant.
Is there a statistical calulcation to do this?



Michael J

Function: Ignore Mimimum and Maximum Values
 
Thanks Duke. That does the trick.
--------------------------------------

"Duke Carey" wrote:

=(SUM(range)-MAX(range)-MIN(range))/(COUNT(range)-2)


"Michael J" wrote:

Is there a spreadsheet function to calculate the average, ignoring a single
minimum and maximum value. This is similar to what they use in the olympics
to calculate a score.

Example: for this list:

A1: 10
A2: 10
A3: 8
A4: 7
A5: 6
A6: 6
A7: 6

I would need to average only cells A2 through A6, ignoring a single max and
min value. The list is normally not sorted and the the number of values is
not constant.
Is there a statistical calulcation to do this?



All times are GMT +1. The time now is 12:45 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com