Excel - elegant formula or IF function

I'm trying to use Excel to calculate fees, and the fees in question are as follows:

Monthly Gross Income, Fee

• \$0-\$99.9 = 15%
• \$100-\$199.9 = 14%
• \$200-\$299.9 = 13%
• etc...
• \$1400-\$1499.9 = 1%
• \$1500 or above = 0%

I can't work out a mathematical formula for taking x (Monthly Gross Income) and outputting y (Fee). Do I need to rely on the IF function here in Excel, and if so, is there a more elegant way to do it than just stacking 15 IF functions inside each other?

Many thanks.

• You may use CHOOSE but the most elegant is been suggested by @Jeeped,, just below. Sep 1 '19 at 9:44

With the amount in A1 try,

=MAX(0.15-FLOOR(A1/POWER(10, 4), 0.01), 0)

That produces the associated percentage; multiply by the original value in A1 for the fee.

• Truly it's good one +1 ☺ Sep 1 '19 at 9:44
• Thanks so much! Additional question (if you have time): What is the formula for the total fee amount, if the 15% is applied to the first \$99.9 only, 14% applied to the next \$99.9 only (\$100-\$199.9) etc...? Sep 1 '19 at 10:01
• @Chilli,, multiply formula with Fee (A1,,) Sep 1 '19 at 10:14
• @Rajesh, but that would only give me the one percentage applied to the whole amount, not the total of discrete amounts with different fees applied. Sep 1 '19 at 10:25
• e.g: \$250 would be \$99.9 @ 15% + \$99.9 @ 14% + \$50.2 @ 13% Sep 1 '19 at 10:28

You could try:

=MAX(0,15-INT(A1/100))

If your column is formatted as percentage:

=MAX(0,15-INT(A1/100))/100