The formula to determine if with 3 separate if conditions.

[Guest]

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%.

 

[Bob Phillips]

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)

 

[Guest]

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(B2>C5,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.

 

[Bob Phillips]

=(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)