Compensation Formula

datastudent

datastudent

Board Regular
Joined
Sep 7, 2021
Messages
92
Office Version
  1. 365
Platform
  1. Windows
Hi,

Please help me figure out the computation of the sales compensation based on the below formula and help me simplified it.

=IF(C145<=50%,0,IF(C145<=100%,(((C28*50%)*($D$65))+((C126-(C28*50%))*$E$65)),IF(C145<=200%,(((C28*50%))*($D$65))+((C28*50%)*$E$65))+((C126)-(C28))*$F$65))


C145sales performance %77.78%
C126actual sales performance70
C164sales compensation payment
C28published sales budget90
D65qouta performance 0-50%0.1
E65qouta performance 51-100%0.2
F65qouta performance 101%+0.3
 
Sort by date Sort by votes
You may be trying to calculate with a tiered formula.is
There are many ways to structure such a formula. There are many examples in this forum.

N.B. To secure responses it is best to post a clear concise question with several examples and expected results.
You can post an example with a few rows showing your spreadsheet.
The forum has a tool named XL2BB that you can use to post an extract.

T202501a.xlsm
CDEFG
14577.78%9.509.5
4e
Cell Formulas
RangeFormula
D145D145=SUM((C145>{0;0.5;1})*(C145-{0;0.5;1})*({0.1;0.1;0.1}))*C28
G145G145=IF(C145<=50%,0,IF(C145<=100%,(((C28*50%)*($D$65))+((C126-(C28*50%))*$E$65)),IF(C145<=200%,(((C28*50%))*($D$65))+((C28*50%)*$E$65))+((C126)-(C28))*$F$65))
 
Upvote 0
This is all I could think of:
=LET(PSB50p,C28*50%,IFS(C145<=50%,0,C145<=100%,(PSB50p*$D$65)+((C126-PSB50p)*$E$65),C145<=200%,(PSB50p*$D$65)+(PSB50p*$E$65)+((C126-C28)*$F$65)))
 
Upvote 0
Solution
Dave Patton said:
You may be trying to calculate with a tiered formula.is
There are many ways to structure such a formula. There are many examples in this forum.

N.B. To secure responses it is best to post a clear concise question with several examples and expected results.
You can post an example with a few rows showing your spreadsheet.
The forum has a tool named XL2BB that you can use to post an extract.

T202501a.xlsm
CDEFG
14577.78%9.509.5
4e
Cell Formulas
RangeFormula
D145D145=SUM((C145>{0;0.5;1})*(C145-{0;0.5;1})*({0.1;0.1;0.1}))*C28
G145G145=IF(C145<=50%,0,IF(C145<=100%,(((C28*50%)*($D$65))+((C126-(C28*50%))*$E$65)),IF(C145<=200%,(((C28*50%))*($D$65))+((C28*50%)*$E$65))+((C126)-(C28))*$F$65))
Click to expand...
Hi, Thank you for your response. I have here a more details. If you could help me please.

Please help me figure out the formula to compute the sales compensation with the below principle.

If the sales performance is 0-50%, compensation is 0.
If sales performance is 51-100%, compensation is 50% will be paid 0.1, 51% up to 100% will be paid 0.2.
If sales performance is 101% or more, compensation is 50% will be paid 0.1, 51% up to 100% will be paid 0.2, above 100% will be paid 0.3.

For example:
sales performance %111.00%
actual sales performance100
sales compensation payment16.5
published sales budget90
qouta performance 0-50%0.1
qouta performance 51-100%0.2
qouta performance 101%+0.3

In manual computation:
90 / 2 = 45 (this is the 50%)
45 * .1 = 4.5
45 * .2 = 9
100 - 90 = 10 (this is the above 100%)
10 * .3 = 3
then 4.5 + 9 +3 = 16.5
 
Upvote 0
Did you try the suggested formula?
Please try either version of the formula.
If you have questions, please advise.

N.B. You can copy a copy of the example to a clean sheet.
Click on the icon below the f(x) in the heading, move to your sheets cell A1, and paste.


T202501a.xlsm
ABCDEFGH
1sales performance %111.11%BracketsRateBrackets
2actual sales performance100010%0%10.0%
3sales compensation payment16.54520%50%20.0%
4published sales budget909030%100%30.0%
5qouta performance 0-50%0.1
6qouta performance 51-100%0.2
7qouta performance 101%+0.3
8
916.516.5
4e
Cell Formulas
RangeFormula
B1B1=100/B4
D3D3=B4*G3
D4D4=B4*G4
B9B9=SUM((B2>{0;45;90})*(B2-{0;45;90})*({0.1;0.1;0.1}))
C9C9=SUM((B1>G2:G4)*(B1-G2:G4)*(H2:H4-H1:H3))*B4
 
Upvote 0
Dave Patton said:
Did you try the suggested formula?
Please try either version of the formula.
If you have questions, please advise.

N.B. You can copy a copy of the example to a clean sheet.
Click on the icon below the f(x) in the heading, move to your sheets cell A1, and paste.


T202501a.xlsm
ABCDEFGH
1sales performance %111.11%BracketsRateBrackets
2actual sales performance100010%0%10.0%
3sales compensation payment16.54520%50%20.0%
4published sales budget909030%100%30.0%
5qouta performance 0-50%0.1
6qouta performance 51-100%0.2
7qouta performance 101%+0.3
8
916.516.5
4e
Cell Formulas
RangeFormula
B1B1=100/B4
D3D3=B4*G3
D4D4=B4*G4
B9B9=SUM((B2>{0;45;90})*(B2-{0;45;90})*({0.1;0.1;0.1}))
C9C9=SUM((B1>G2:G4)*(B1-G2:G4)*(H2:H4-H1:H3))*B4
Click to expand...
yes I tried the first formula you sent. It does work but since I didn't give much details the first time I thought it might change based on the principle I just sent.
I'm also having issues in getting my 2xlbb work on my excel. Still trying to fix it.

Anyways, In this formula, can we add the D3 and D4 formula inside the actual formula? just so I would not need to create a separate table for the rate brackets.
or is the first formula you sent before also works considering the principles?

I really appreciate your help!
 
Upvote 0
T202501a.xlsm
AB
1sales performance %111.11%
2actual sales performance100
3sales compensation payment16.50
4published sales budget90
5qouta performance 0-50%10%
6qouta performance 51-100%20%
7qouta performance 101%+30%
8
4e
Cell Formulas
RangeFormula
B1B1=100/B4
B3B3=SUM((B2>{0;0.5;1}*B4)*(B2-{0;45;90})*({0.1;0.1;0.1}))
 
Upvote 0
minor edits of input

T202501a.xlsm
AB
1sales performance %111.11%
2actual sales performance100
3sales compensation payment16.50
4published sales budget90
5quota performance 0-50%10%
6quota performance 51-100%20%
7quota performance 101%+30%
8
9
4e
Cell Formulas
RangeFormula
B1B1=B2/B4
B3B3=SUM((B2>{0;0.5;1}*B4)*(B2-{0;45;90})*({0.1;0.1;0.1}))
 
Upvote 0
T202501a.xlsm
AB
1sales performance %111.11%
2actual sales performance100
3sales compensation payment16.50
4published sales budget90
5quota performance 0-50%10%
6quota performance 51-100%20%
7quota performance 101%+30%
8
4e
Cell Formulas
RangeFormula
B1B1=B2/B4
B3B3=LET(b,B4*{0;0.5;1},r,{0.1;0.1;0.1},SUM((B2>b)*(B2-b)*r))
 
Upvote 0
You must log in or register to reply here.

Similar threads

datastudent
  • Locked
  • Question Question
Sales Compensation Formula
Replies
1
Views
63
Fluff
Fluff
N
  • Question Question
Data Transformation using VBA like subtotal
Replies
1
Views
164
Domenic
D
T
  • Question Question
RAINFALL CESSATION DATE USING INDEX
Replies
1
Views
272
Thlama
T
A
  • Solved
transpose data int listbox on userform based on two dates or without dates
Replies
2
Views
374
Alaa mg
A
D
  • Solved
finding changes and looking back
2
Replies
10
Views
460
felixstraube
F

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top