J
jim3975
Hi All,
How do I remove the highest and lowest values from a series of fields?
How do I remove the highest and lowest values from a series of fields?
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
P
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
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
J
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
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
J
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
extremes (as a primitive statistical approach to 'smoothening' a graph).
Your answer makes it clear.
Jim
J
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]>,
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]>,
P
Pete_UK
Good guess on my part then !! <bg>
Thanks for feeding back.
Pete
Thanks for feeding back.
Pete
B
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
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
J
Jim 3975
JE McGimpsey said: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.