A Sales Rep has a full year target of 300, split into 3 levels - 100, 200 and
300, with a payout of 15%, 20% and 25% at the 3 levels. Need to know the
formula that calculates where a YTD performance # is relative to a payout on
the 3 levels. For ex. If YTD # is 300 then Level 1 is 100 with a payout at
15%, Level 2 is 100 with a payout of 20% and Level 3 is also 100 paying at
25%. If YTD = 65 then only Level 1 at 15%.

Is this what you want?

=(MIN(A2,100)*15%)+MIN(MAX(A2-100,0),100)*20%+MIN(MAX(A2-200,0),100)*25%

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Clipper" wrote in message
...
A Sales Rep has a full year target of 300, split into 3 levels - 100, 200
and
300, with a payout of 15%, 20% and 25% at the 3 levels. Need to know the
formula that calculates where a YTD performance # is relative to a payout
on
the 3 levels. For ex. If YTD # is 300 then Level 1 is 100 with a payout
at
15%, Level 2 is 100 with a payout of 20% and Level 3 is also 100 paying at
25%. If YTD = 65 then only Level 1 at 15%.

Let me rephrase. A graphical representation might be better.

YTD Actuals = 210

FY Plan = 300
Pay
Cumm Rate Act Pay $
Level 1 Plan = 100 100 15% 100 15
Level 2 Plan = 100 200 20% 100 20
Level 3 Plan = 100 300 25% 10 2.5

The forumula I used to get the 1st 100 under Act is this =IF(B2C5,C5,B2);
where B2 is my YTD Actuals (210) and C5 is the 100 under Cumm (cummulative).
It is the 2nd and 3rd formulas under the Act column I can't do.


"Clipper" wrote:

A Sales Rep has a full year target of 300, split into 3 levels - 100, 200 and
300, with a payout of 15%, 20% and 25% at the 3 levels. Need to know the
formula that calculates where a YTD performance # is relative to a payout on
the 3 levels. For ex. If YTD # is 300 then Level 1 is 100 with a payout at
15%, Level 2 is 100 with a payout of 20% and Level 3 is also 100 paying at
25%. If YTD = 65 then only Level 1 at 15%.

=(MIN(E5,C5)*D5)
=MIN(MAX(E6,0),C6-C5)*D6
=MIN(MAX(E7,0),C7-C6)*D7

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Clipper" wrote in message
...
Let me rephrase. A graphical representation might be better.

YTD Actuals = 210

FY Plan = 300
Pay
Cumm Rate Act Pay $
Level 1 Plan = 100 100 15% 100 15
Level 2 Plan = 100 200 20% 100 20
Level 3 Plan = 100 300 25% 10 2.5

The forumula I used to get the 1st 100 under Act is this =IF(B2C5,C5,B2);
where B2 is my YTD Actuals (210) and C5 is the 100 under Cumm
(cummulative).
It is the 2nd and 3rd formulas under the Act column I can't do.


"Clipper" wrote:

A Sales Rep has a full year target of 300, split into 3 levels - 100,
200 and
300, with a payout of 15%, 20% and 25% at the 3 levels. Need to know
the
formula that calculates where a YTD performance # is relative to a
payout on
the 3 levels. For ex. If YTD # is 300 then Level 1 is 100 with a
payout at
15%, Level 2 is 100 with a payout of 20% and Level 3 is also 100 paying
at
25%. If YTD = 65 then only Level 1 at 15%.