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

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
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
320
JPav
J
A
  • Solved
Formula to calculate sales price
Replies
2
Views
384
ASBM24
A
J
  • Question Question
Water Bill Calculator w/ROI from Percentage Savings
Replies
1
Views
427
Jared016
J
J
  • Question Question
Manual calculation of the median from a series of grades
2
Replies
14
Views
271
felixstraube
F
D
  • Question Question
Locate first instance of all dates in a column and hide all remaining rows
Replies
4
Views
82
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,223,893
Messages
6,175,240
Members
452,621
Latest member
Laura_PinksBTHFT

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