Averaging the top 2/3 highest numbers

[Guest]

Hello to all --

I have a table of monthly production figures for various people, who range
from full-time to very part-time. To track performance, I'd like to average
the top 2/3 highest producers. Here's the kicker -- I anticipate that, over
time, the actual number of people that comprise the top 2/3 will vary, as the
department grows and shrinks.

I guess I have two options:
1 -- manually use the LARGE function repetitively, adjusting the number as
things change over time.
2 -- figuring out how to do this in VBA.

Is there another formula that will count the number of items, compute 2/3rds
of them, and then pick that number of the largest numbers from the group?

Thanks in advance.

 

[Bernard Liengme]

This seems to work with the limited test I did

Function Topavg(rng As Range)
mycount = rng.Count
mycrit = mycount * 2 / 3
For Each mycell In rng
If Application.WorksheetFunction.Rank(mycell, rng) <= mycrit Then
myacc = myacc + mycell
End If
Next

Topavg = myacc / mycrit

End Function

Call it is worksheet with something like =TOPAVG(A1:A90)
best wishes

 

[Ron Rosenfeld]

Hello to all --

I have a table of monthly production figures for various people, who range
from full-time to very part-time. To track performance, I'd like to average
the top 2/3 highest producers. Here's the kicker -- I anticipate that, over
time, the actual number of people that comprise the top 2/3 will vary, as the
department grows and shrinks.

I guess I have two options:
1 -- manually use the LARGE function repetitively, adjusting the number as
things change over time.
2 -- figuring out how to do this in VBA.

Is there another formula that will count the number of items, compute 2/3rds
of them, and then pick that number of the largest numbers from the group?

Thanks in advance.

How do you figure the highest 2/3 producers?

If you define it as the 2/3 of the producers who have the highest production,
then the following might work:

=SUMIF(Production,">"&SMALL(Production,CEILING(COUNT(Production)/3,1)))/
COUNTIF(Production,">"&SMALL(Production,CEILING(COUNT(Production)/3,1)))

where Production is the named range containing the quantity of production (e.g.
A2:A500).


--ron

 

[Guest]

Hi,

Supposing that the names of people are in A2, A3, ...., An, and their
performance are in B2, B3, ..., Bn, try the following formula in some other
cell.

=SUMIF($B$2:$B$60000,">="&LARGE($B$2:$B$60000,ROUND(COUNTA($A$2:$A$60000)*2/3,0)))/COUNTIF($B$2:$B$60000,">="&LARGE($B$2:$B$60000,ROUND(COUNTA($A$2:$A$60000)*2/3,0)))

P.S: If there are ties near the bottom 2/3 of the production such that more
than 2/3 of people are above the top-2/3 production cut-off level, the
formula will, to be fair, include all those people and their production
figures for calculating the average; for example, if there are 18 people and
their production levels are such that 3 people (i.e., 12, 13, and 14) tie
near the 2/3rd cutoff, then the top 14 (and not just 12) people and their
production figures are taken into account.

Regards,
B. R. Ramachandan