writing a formula

  • Thread starter Thread starter Tim Dolan
  • Start date Start date
T

Tim Dolan

Using Excel 2000, I've been using a basic formula for finding averages for a
sport. Players may not always try their best which can result in low scores
for them and high scores for the opponent.
I would like to be able to drop high scores and low scores and use middle
scores.
If I had 5 game rounds, 7 game rounds 9 game rounds and 11 game rounds,
could I drop the top 2 and bottom 2 scores in each round and apply a basic
average formula to get a fair average?
(except the 5 game round)
If so, how can I do it with Excel?
Tim fm CT
 
Tim

Have a look at TRIMMEAN. From the Help:

Returns the mean of the interior of a data set. TRIMMEAN calculates the mean
taken by excluding a percentage of data points from the top and bottom tails
of a data set. You can use this function when you wish to exclude outlying
data from your analysis.

Syntax

TRIMMEAN(array,percent)


Regards

Trevor
 
Trevor,
Thanks for your reply. I think I used poor wording in my description of what
I'd like to do. Instead of dropping the "two top and two bottom scores", I
should have said that I want to drop the two highest and two lowest scores
in value.
thanks,
Tim fm CT
 
Trevor,
I looked at the Trimmean help. I'm in over my head so I have to
ask. How does the trimmean determine the highest and or lowest values? The
percentage thing is handleing each cell equally right?
Tim fm Ct
 
Tim

let's say you had 10 numbers in A1 to J1 in random order:

4, 6, 3, 2, 1, 5, 6, 9, 10, 6



If you took the average of these numbers, you'd get:



=AVERAGE(A1:J1) = 5.2



If you use TRIMMEAN with 20% as the second parameter, you'd get:



=TRIMMEAN(A1:J1,20%) = 5.125



This is dropping 20% of the numbers, 10% from the top and 10% from the
bottom. In this case, it is taking off 2 numbers, 1 high and 1 low


That would be the same as, say, copying the numbers and sorting them into
ascending order and manually omitting the top and the bottom numbers

A3 to J3 = 1, 2, 3, 4, 5, 6, 6, 6, 9, 10

becomes

B3 to I3 = 2, 3, 4, 5, 6, 6, 6, 9



The average of these numbers is:



=AVERAGE(B3:I3) = 5.125

=TRIMMEAN(A3:J3,20%) = 5.125

Hope this clarifies. You need to determine the percentage of numbers you
need to drop. I'm not sure how easily this relates to your task but that's
the basic principle.

Regards

Trevor
 
Back
Top