Excel Formula help

[Lewis255]

Hi there, I need help with a formula to show the following –

in first cell
12.5% commission when the gross total falls between (£30,001 – £50,000)

In second cell
15% commission when the gross total falls between (£50,001 – £75,000)

I tried the IF(AND) function but the “False Statement” means that the cell will change the max amount once the gross total goes above that threshold. E.g. I need cell one to show the max value of £2499.88 (12.5% 50,000-30.001) once the gross value reaches £50,001.

I apologise this explanation isn’t very clear, I hope it makes sense.

Thanks

[#2]

Hi,
Is your IF(AND) function something like this:
=IF(AND(A1>=30001,A1<=50000),12.5%,IF(AND(A1>=50001,A1<=75000),15%))
So if A1 contains 30003 you get a result of 12.5% and if it contains 50003 you get 15%.
Or are you looking to combine the result with a value and text, so that it needs CONCAT also?

[#3]

@DeniseMcL1 Thanks for replying. So the one I used had just a little extra.

E.g. IF(AND(A1>=30001,A1<=50000),(50000-30001)*0.125) 

My issue isn't that the formula doesn't work, it's that the false statement returning the argument as "FALSE" even once the gross total reaches 50,001. Once the total reaches that figure, I need the formula to show the max value of 12.5% commission that can be earned between the values of 30,001-50,000 (19,999).

[#4 · Accepted solution]

@Lewis255 

First cell:

=MAX(MIN(A1, 50000)-30000, 0)*12.5%

Second cell:

=MAX(MIN(A1, 75000)-50000, 0)*15%

[#5]

Legend!!!