0

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.

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

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.

8
  • 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...?
    – Chilli
    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.
    – Chilli
    Sep 1 '19 at 10:25
  • e.g: $250 would be $99.9 @ 15% + $99.9 @ 14% + $50.2 @ 13%
    – Chilli
    Sep 1 '19 at 10:28
0

You could try:

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

If your column is formatted as percentage:

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.