Tier Calculations

[Patrick15]

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.

 

[jtakw]

Hi,

Sounds like homework...look into IF, AND, MIN, MAX functions combined with < (less than), > (greater than), and/or = (equal).

 

[hotabae]

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

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)))

 

[Eric W]

Another way:

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

 

[Patrick15]

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

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)))

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?

 

[Patrick15]

Another way:

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

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.

 

[Eric W]

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:

	A	B	C
1	10000	Eric	hotabae
2		5225	5175
3
4
5	1250	76%	950
6	3750	54%	2025
7	5000	45%	2250
8
9	10000		5225

<tbody>
</tbody>

Sheet5​

Worksheet Formulas Cell Formula B2 =MIN(MIN(A1,1250)*76%+MEDIAN(0,A1-1250,3750)*54%+MAX(A1-5000,0)*45%,8000) C2 =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))) C5 =A5*B5 C6 =A6*B6 C7 =A7*B7 A9 =SUM(A5:A7) C9 =SUM(C5:C7) <tbody> </tbody>

<tbody>
</tbody>

 

[Patrick15]

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:

	A	B	C
1	10000	Eric	hotabae
2		5225	5175
3
4
5	1250	76%	950
6	3750	54%	2025
7	5000	45%	2250
8
9	10000		5225

<tbody>
</tbody>

Sheet5​

Worksheet Formulas Cell Formula B2 =MIN(MIN(A1,1250)*76%+MEDIAN(0,A1-1250,3750)*54%+MAX(A1-5000,0)*45%,8000) C2 =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))) C5 =A5*B5 C6 =A6*B6 C7 =A7*B7 A9 =SUM(A5:A7) C9 =SUM(C5:C7) <tbody> </tbody>

<tbody>
</tbody>

Thank you so so much for this explanation. I quite understand now. Kind regards.

 

[hotabae]

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

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)))​