highest and lowest values

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

jim3975 - 20 Apr 2007 16:33 GMT

Hi All,
How do I remove the highest and lowest values from a series of fields?

Reply to this Message

Pete_UK - 20 Apr 2007 17:03 GMT

In what way do you want to remove them? If you want to ignore their
values in a sum, for example, then you could have something like:

=SUM(A1:A20)-MAX(A1:A20)-MIN(A1:A20)

and

=COUNT(A1:A20)-2

to get a count of how many values - the first formula divided by the
second will give you the average, for example.

Hope this helps.

Pete

> Hi All,
> How do I remove the highest and lowest values from a series of fields?

Reply to this Message

Jim 3975 - 20 Apr 2007 21:50 GMT

Thanks a lot; I actually did want calculate an average and ignore the
extremes (as a primitive statistical approach to 'smoothening' a graph).
Your answer makes it clear.

Jim

> In what way do you want to remove them? If you want to ignore their
> values in a sum, for example, then you could have something like:
[quoted text clipped - 14 lines]
>> Hi All,
>> How do I remove the highest and lowest values from a series of fields?

Reply to this Message

JE McGimpsey - 21 Apr 2007 00:07 GMT

There's an XL function that will do that for you:

Say you had 10 values and you wanted to eliminate the max and min:

=TRIMMEAN(A1:A10,2/10)

If you don't know how many values are in the data set, you can eliminate
the top and bottom using

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

If youIn article <46292783$0$2017$9a622dc7@news.kpnplanet.nl>,

> Thanks a lot; I actually did want calculate an average and ignore the
> extremes (as a primitive statistical approach to 'smoothening' a graph).
> Your answer makes it clear.

Reply to this Message

Bernd - 21 Apr 2007 06:39 GMT

Hello,

If the intention is to eliminate all max and all min values (they
might appear more than once):
=TRIMMEAN(A:A,(COUNTIF(A:A,MAX(A:A))+COUNTIF(A:A,MIN(A:A)))/
COUNT(A:A))

Regards,
Bernd

Reply to this Message

Jim 3975 - 21 Apr 2007 06:47 GMT

> There's an XL function that will do that for you:
>
[quoted text clipped - 6 lines]
>
> =TRIMMEAN(A:A, 2/COUNT(A:A))

Thanks! This is even better.
Amazing to see that this is apparently a standard operation. In my
statistical ignorance I seem to have reinvented it.

Reply to this Message

Pete_UK - 21 Apr 2007 01:43 GMT

Good guess on my part then !! <bg>

Thanks for feeding back.

Pete

> Thanks a lot; I actually did want calculate an average and ignore the
> extremes (as a primitive statistical approach to 'smoothening' a graph).
[quoted text clipped - 22 lines]
>
> - Show quoted text -

Reply to this Message

JE McGimpsey - 20 Apr 2007 17:04 GMT

You mean delete the values from the cells? What if there are duplicates,
should duplicate Mins or Maxes be removed as well?

One way, using a macro:

Public Sub RemoveMaxMin()
Dim rCell As Range
Dim dMax As Double
Dim dMin As Double

With Selection
dMax = Application.Max(.Cells)
dMin = Application.Min(.Cells)
For Each rCell In .Cells
With rCell
If IsNumeric(.Value) Then _
If .Value = dMax Or .Value = dMin Then .ClearContents
End With
Next rCell
End With
End Sub

> Hi All,
> How do I remove the highest and lowest values from a series of fields?

Reply to this Message

Rate this thread: