Need to do a weighted average, but only excluding the bottom

[Todd]

Wondering if there's a way to get the average of the top
95% of a set of data (other than manually walking through
it).

The example is I have a list of 25 names, each has about
10-60 numbers. I can easily find the average of these,
but I want only the average of the highest 95% for each
name. It's like TRIMMEAN, except TRIMMEAN excludes both
the top and bottom nth percent, where I only want to
exclude the bottom.

Any help with finding a way to do this automatically via
formulas would be most appreciated.

Thanks,

Todd

 

[Bernard V Liengme]

Hi Todd,
You need to make some subjective decisions: 95% of a number 10 to 60 means
missing somewhere between 0 and 3 values. I elected to miss 3 when there
were more than 50 values, 2 when more than 35 and 1 when more than 15. The
following seems to work - modify to taste.
=(SUM(data)-(COUNT(data)>15)*SMALL(data,1)-(COUNT(data)>35)*SMALL(data,2)-(C
OUNT(data)>3)*SMALL(data,3))/COUNT(data)

best wishes
Bernard
www.stfx.ca/people/bliengme

 

[Todd]

Thanks Bernard. This helps a lot. Still have one
problem: the data I need to do this for has 25 people. I
understand how to change the formula you listed to

=(SUMIF(data,"name1")-(COUNTIF(data,"name1")>15)(etc...)

but there's no formula "SMALLIF." How would I tell it to
only take the smallest Nth where the name equals "name1",
for example?

Thanks again for your help.

Todd

 

[Bernard V Liengme]

Hi Todd,
A small correction - I was rushing toward the end last night (being called
by "she who must be obeyed"). We have correctly summed the largest 95% but
the count is in error in the denominator.

=(SUM(data)-(COUNT(data)>15)*SMALL(data,1)-(COUNT(data)35)*SMALL(data,2)-(C
OUNT(data)>50)*SMALL(data,3))/(COUNT(data) -(COUNT(data)>15)*1-(COUNT(data)>
35)*1-(C
OUNT(data)>50)*1)

Your second question: I used 'data' here to avoid having to type somethign
like B2:KB2.
Suppose your names are in rows 2 to 55, with the values to average in B2:KB2
for the first person, B3:KB3 for the second. Place the formula above in some
cell in row 2 (say KD2) put replacing 'data' by B2:KB2. Now copy the formula
down to last row and it will compute everyone's average. Hope I understood
question.

Bernard