Sales Bonus Formula

[rway32]

Hi

I am trying to figure out how to make a formula where a salesperson needs to sell 100 units per Quarter to qualify for a bonus. They will also get $25.00 per unit sold over 100 PLUS an additional 10% of this amount will be added for each year of service. Say they have worked here 10 years.


Thanks...............

 

[Aladin Akyurek]

In Cell Z1:

=IF(QuarterSalesCell>=100,Bonus+(QuarterSalesCell-100)*25,"")

In Cell Z2:

=IF(N(Z1),Z1+Z1*10%*(YEAR(TODAY())-YEAR(HireDate)),"")

 

[Scott R]

rway32 would have to confirm, but I'm guessing the 10% experience adder is applied to the $25 component only, not the fixed Bonus. So if they had 10 yrs exp, they would earn Bonus + $50 per unit over.

 

[Aladin Akyurek]

rway32 would have to confirm, but I'm guessing the 10% experience adder is applied to the $25 component only, not the fixed Bonus. So if they had 10 yrs exp, they would earn Bonus + $50 per unit over.

Scott,

Please pick up that formula and amend it the way you suggest.

 

[rway32]

It would be units over 100 x 25 . In other words 10% of this amount

Thanks

 

[Scott R]

=(BonusUnits>0)*Bonus+BonusUnits*25*(10%*YrsSvc+1)

where BonusUnits = MAX(Sales-100,0)

 

[rway32]

Scott R ...........I thought it had to be IF formula

Thanks James

 

[rway32]

 

[Scott R]

You don't have to use an IF formula; really just a matter of style.

IF(Sales-Target>0,bonus calc,0) is another approach.