software

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?

=(SUM(range)-MAX(range)-MIN(range))/(COUNT(range)-2)quot;Michael Jquot; wrote:

gt; Is there a spreadsheet function to calculate the average, ignoring a single
gt; minimum and maximum value. This is similar to what they use in the olympics
gt; to calculate a score.
gt;
gt; Example: for this list:
gt;
gt; A1: 10
gt; A2: 10
gt; A3: 8
gt; A4: 7
gt; A5: 6
gt; A6: 6
gt; A7: 6
gt;
gt; I would need to average only cells A2 through A6, ignoring a single max and
gt; min value. The list is normally not sorted and the the number of values is
gt; not constant.
gt; Is there a statistical calulcation to do this?

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 gt;,
Michael J lt;Michael gt; wrote:

gt; Is there a spreadsheet function to calculate the average, ignoring a single
gt; minimum and maximum value. This is similar to what they use in the olympics
gt; to calculate a score.
gt;
gt; Example: for this list:
gt;
gt; A1: 10
gt; A2: 10
gt; A3: 8
gt; A4: 7
gt; A5: 6
gt; A6: 6
gt; A7: 6
gt;
gt; I would need to average only cells A2 through A6, ignoring a single max and
gt; min value. The list is normally not sorted and the the number of values is
gt; not constant.
gt; Is there a statistical calulcation to do this?

Thanks JE. Your general works fine.
---------------------------------------------quot;JE McGimpseyquot; wrote:

gt; One way:
gt;
gt; =TRIMMEAN(A1:A7, 2/7))
gt;
gt; For more or fewer numbers, this can be generalized as
gt;
gt; =TRIMMEAN(A:A,2/COUNT(A:A))
gt;
gt;
gt;
gt; In article gt;,
gt; Michael J lt;Michael gt; wrote:
gt;
gt; gt; Is there a spreadsheet function to calculate the average, ignoring a single
gt; gt; minimum and maximum value. This is similar to what they use in the olympics
gt; gt; to calculate a score.
gt; gt;
gt; gt; Example: for this list:
gt; gt;
gt; gt; A1: 10
gt; gt; A2: 10
gt; gt; A3: 8
gt; gt; A4: 7
gt; gt; A5: 6
gt; gt; A6: 6
gt; gt; A7: 6
gt; gt;
gt; gt; I would need to average only cells A2 through A6, ignoring a single max and
gt; gt; min value. The list is normally not sorted and the the number of values is
gt; gt; not constant.
gt; gt; Is there a statistical calulcation to do this?
gt;

Thanks Duke. That does the trick.
--------------------------------------

quot;Duke Careyquot; wrote:

gt; =(SUM(range)-MAX(range)-MIN(range))/(COUNT(range)-2)
gt;
gt;
gt; quot;Michael Jquot; wrote:
gt;
gt; gt; Is there a spreadsheet function to calculate the average, ignoring a single
gt; gt; minimum and maximum value. This is similar to what they use in the olympics
gt; gt; to calculate a score.
gt; gt;
gt; gt; Example: for this list:
gt; gt;
gt; gt; A1: 10
gt; gt; A2: 10
gt; gt; A3: 8
gt; gt; A4: 7
gt; gt; A5: 6
gt; gt; A6: 6
gt; gt; A7: 6
gt; gt;
gt; gt; I would need to average only cells A2 through A6, ignoring a single max and
gt; gt; min value. The list is normally not sorted and the the number of values is
gt; gt; not constant.
gt; gt; Is there a statistical calulcation to do this?