Tier Calculations

P

Patrick15

New Member
Joined
May 23, 2018
Messages
4
How can I create a formula for this Calculation in Excel

76% on first $1,250 of Salary + 54% of following $3,750 + 45% of the remaining to a max. Of 8000$ or 85% of Salary whichever is less.

I appreciate any solution for this.
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
Sort by date Sort by votes
Hi,

Sounds like homework...look into IF, AND, MIN, MAX functions combined with < (less than), > (greater than), and/or = (equal).
 
Upvote 0
I got these as the three cases:

Salary Ranges

(S = 0 -> 1250)

=S*(0.76)

(S = 1250 -> 5000)

=900 + (S-1250)*(0.54)

(S = 5000 -> 16278)

=2925 + (S-5000)*(0.45)

(S = 16278 -> Infinity)

=8000


The 85% of Salary doesn't apply to any of those ranges as stated above.

See below

FLRvCLY.png


The excel formula for this would be

=IF(A1>=16278,8000,IF(A1<=1250,A1*0.76,IF(A1<=5000,900+(A1-1250)*0.54,2925+(A1-5000)*0.45)))

Please let me know if I've misunderstood something.

Edit: I picked 16278 cuz it was close/rounded, but the (more accurate) formula would be this:

=IF(A1>=5000+(5075/0.45),8000,IF(A1<=1250,A1*0.76,IF(A1<=5000,900+(A1-1250)*0.54,2925+(A1-5000)*0.45)))
 
Last edited:
Upvote 0
Another way:

=MIN(MIN(A1,1250)*76%+MEDIAN(0,A1-1250,3750)*54%+MAX(A1-5000,0)*45%,8000)
 
Upvote 0
hotabae said:
I got these as the three cases:

Salary Ranges

(S = 0 -> 1250)

=S*(0.76)

(S = 1250 -> 5000)

=900 + (S-1250)*(0.54)

(S = 5000 -> 16278)

=2925 + (S-5000)*(0.45)

(S = 16278 -> Infinity)

=8000


The 85% of Salary doesn't apply to any of those ranges as stated above.

See below

FLRvCLY.png


The excel formula for this would be

=IF(A1>=16278,8000,IF(A1<=1250,A1*0.76,IF(A1<=5000,900+(A1-1250)*0.54,2925+(A1-5000)*0.45)))

Please let me know if I've misunderstood something.

Edit: I picked 16278 cuz it was close/rounded, but the (more accurate) formula would be this:

=IF(A1>=5000+(5075/0.45),8000,IF(A1<=1250,A1*0.76,IF(A1<=5000,900+(A1-1250)*0.54,2925+(A1-5000)*0.45)))
Click to expand...


Thank you for your response. I reviewed the three formulas, yours and Eric's I plotted it with and a number of salaries to see where the differences were, there was a discrepancy and I am now a tad confused as to which is the right calc.

What do you think?
 
Upvote 0
Eric W said:
Another way:

=MIN(MIN(A1,1250)*76%+MEDIAN(0,A1-1250,3750)*54%+MAX(A1-5000,0)*45%,8000)
Click to expand...


Thank you for your response. I reviewed the three formulas, I plotted it with and a number of salaries on a monthly bases since its for a monthly calc, to see where the differences were, there was a discrepancy and I am now a tad confused as to which is the right calc.
 
Upvote 0
You don't know the right answer? Pick a salary, manually calculate the desired result, then see which formula gives you that number. For example:

ABC
Erichotabae
5225

<tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]10000[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"]5225[/TD]
[TD="align: right"]5175[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]1250[/TD]
[TD="align: right"]76%[/TD]
[TD="align: right"]950[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]3750[/TD]
[TD="align: right"]54%[/TD]
[TD="align: right"]2025[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]5000[/TD]
[TD="align: right"]45%[/TD]
[TD="align: right"]2250[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]10000[/TD]
[TD="align: right"][/TD]

</tbody>
Sheet5​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]B2[/TH]
[TD="align: left"]=MIN(MIN(A1,1250)*76%+MEDIAN(0,A1-1250,3750)*54%+MAX(A1-5000,0)*45%,8000)[/TD]
[/TR]
[TR]
[TH]C2[/TH]
[TD="align: left"]=IF(A1>=5000+(5075/0.45),8000,IF(A1<=1250,A1*0.76,IF(A1<=5000,900+(A1-1250)*0.54,2925+(A1-5000)*0.45)))[/TD]
[/TR]
[TR]
[TH]C5[/TH]
[TD="align: left"]=A5*B5[/TD]
[/TR]
[TR]
[TH]C6[/TH]
[TD="align: left"]=A6*B6[/TD]
[/TR]
[TR]
[TH]C7[/TH]
[TD="align: left"]=A7*B7[/TD]
[/TR]
[TR]
[TH]A9[/TH]
[TD="align: left"]=SUM(A5:A7)[/TD]
[/TR]
[TR]
[TH]C9[/TH]
[TD="align: left"]=SUM(C5:C7)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited:
Upvote 0
Eric W said:
You don't know the right answer? Pick a salary, manually calculate the desired result, then see which formula gives you that number. For example:

ABC
Erichotabae
5225

<tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]10000[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"]5225[/TD]
[TD="align: right"]5175[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]1250[/TD]
[TD="align: right"]76%[/TD]
[TD="align: right"]950[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]3750[/TD]
[TD="align: right"]54%[/TD]
[TD="align: right"]2025[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]5000[/TD]
[TD="align: right"]45%[/TD]
[TD="align: right"]2250[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]10000[/TD]
[TD="align: right"][/TD]

</tbody>
Sheet5​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]B2[/TH]
[TD="align: left"]=MIN(MIN(A1,1250)*76%+MEDIAN(0,A1-1250,3750)*54%+MAX(A1-5000,0)*45%,8000)[/TD]
[/TR]
[TR]
[TH]C2[/TH]
[TD="align: left"]=IF(A1>=5000+(5075/0.45),8000,IF(A1<=1250,A1*0.76,IF(A1<=5000,900+(A1-1250)*0.54,2925+(A1-5000)*0.45)))[/TD]
[/TR]
[TR]
[TH]C5[/TH]
[TD="align: left"]=A5*B5[/TD]
[/TR]
[TR]
[TH]C6[/TH]
[TD="align: left"]=A6*B6[/TD]
[/TR]
[TR]
[TH]C7[/TH]
[TD="align: left"]=A7*B7[/TD]
[/TR]
[TR]
[TH]A9[/TH]
[TD="align: left"]=SUM(A5:A7)[/TD]
[/TR]
[TR]
[TH]C9[/TH]
[TD="align: left"]=SUM(C5:C7)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...

Thank you so so much for this explanation. I quite understand now. Kind regards.
 
Upvote 0
Oops, Yeah. Looks like I did some bad math for the 2nd group, cause wouldn't ya know, 1250*0.76 is 950, not 900 :stickouttounge:

Edited formula would be:

=IF(A1>=5000+(5075/0.45),8000,IF(A1<=1250,A1*0.76,IF(A1<=5000,950+(A1-1250)*0.54,2975+(A1-5000)*0.45)))

Changed from the original

=IF(A1>=5000+(5075/0.45),8000,IF(A1<=1250,A1*0.76,IF(A1<=5000,900+(A1-1250)*0.54,2925+(A1-5000)*0.45)))​
Click to expand...
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
Payroll - Salary Step Increases
Replies
2
Views
360
JPav
J
A
  • Solved
Formula to calculate sales price
Replies
2
Views
429
ASBM24
A
J
  • Question Question
Water Bill Calculator w/ROI from Percentage Savings
Replies
1
Views
476
Jared016
J
H
  • Question Question
Calculating Remaining Budget with a fill-down formula
Replies
1
Views
128
DanteAmor
DanteAmor
J
  • Question Question
Manual calculation of the median from a series of grades
2
Replies
14
Views
321
felixstraube
F

Forum statistics

Threads
1,224,820
Messages
6,181,155
Members
453,021
Latest member
Justyna P

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