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.

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
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"pdberger" wrote in message
...
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.

On Fri, 6 Jan 2006 07:33:02 -0800, "pdberger"
wrote:

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(COU NT(Production)/3,1)))/
COUNTIF(Production,""&SMALL(Production,CEILING(CO UNT(Production)/3,1)))

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


--ron

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,ROUN D(COUNTA($A$2:$A$60000)*2/3,0)))/COUNTIF($B$2:$B$60000,"="&LARGE($B$2:$B$60000,ROU ND(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

"pdberger" wrote:

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.