Sales Bonus Formula

Aladin Akyurek

MrExcel MVP

Joined

Feb 14, 2002

Messages

85,210

• Sep 3, 2003

• #2

In Cell Z1:

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

In Cell Z2:

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

 

Upvote 0

S

Scott R

Active Member

Joined

Feb 20, 2002

Messages

484

Office Version

1. 365

Platform

1. Windows

• Sep 3, 2003

• #3

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.

 

Upvote 0

Aladin Akyurek

MrExcel MVP

Joined

Feb 14, 2002

Messages

85,210

• Sep 3, 2003

• #4

Scott R said:

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.

Click to expand...

Scott,

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

 

Upvote 0

R

rway32

New Member

Joined

Sep 3, 2003

Messages

5

• Sep 3, 2003

• #5

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

Thanks

 

Upvote 0

S

Scott R

Active Member

Joined

Feb 20, 2002

Messages

484

Office Version

1. 365

Platform

1. Windows

• Sep 3, 2003

• #6

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

where BonusUnits = MAX(Sales-100,0)

 

Upvote 0

R

rway32

New Member

Joined

Sep 3, 2003

Messages

5

• Sep 3, 2003

• #7

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

Thanks James

 

Upvote 0

R

rway32

New Member

Joined

Sep 3, 2003

Messages

5

• Sep 3, 2003

• #8

 

Upvote 0

S

Scott R

Active Member

Joined

Feb 20, 2002

Messages

484

Office Version

1. 365

Platform

1. Windows

• Sep 3, 2003

• #9

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.

 

Upvote 0