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)))
Another way:
=MIN(MIN(A1,1250)*76%+MEDIAN(0,A1-1250,3750)*54%+MAX(A1-5000,0)*45%,8000)
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 |
Worksheet Formulas
<tbody> </tbody> |
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>
=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)))