Need help with a function

  • Thread starter Thread starter Windowgirl
  • Start date Start date
W

Windowgirl

Hello. I have searched high and low and am at a loss (I know the
answer is easy but I'm lost)

I am trying to calculate commissions. The commission is a formula
that is dependent on three things. If a key number (total sales)
doesn't exceed an forecasted number the commission is $0. If the
total sales exceeds the forecast but doesn't exceed another goal
(incremental goal) the commission is the difference between the total
sales and the forecasted multiplied by 2%. But if the total sales
exceed the incremental goal as well the commission is the difference
between the incremental and forecasted times 2%

I.E.
Scenario 1

Sales are $400
Forecast is $500
Incremental is $800 No commission

Scenario 2

Sales are $1000
Forecast is $500
Incremental $800 Commission would be 2% of 300
(Incremental-Forecast)

Scenario 3

Sales are $700
Forecast is $500
Incremental is $800 Commission would be 2% of 200
(Actual-Forecast)

How do I put that into a formula???

THanks
 
Assuming sales are in cell A1, forecast is in cell A2 and
incremental goal is in cell A3:

=IF(A1<A2,0,IF(A1<A3,(A2-A1)*.02,(A3-A2)*.02))

Regards,
Felipe
 
Back
Top