highest and lowest values

[jim3975]

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

 

[Pete_UK]

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

 

[JE McGimpsey]

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

 

[Jim 3975]

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

 

[JE McGimpsey]

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 <[email protected]>,

 

[Pete_UK]

Good guess on my part then !! <bg>

Thanks for feeding back.

Pete

 

[Bernd]

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

 

[Jim 3975]

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))

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.