SOLVED

# Excel Formula help

Copper Contributor

# Excel Formula help

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

4 Replies

# Re: Excel Formula help

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?

# Re: Excel Formula help

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

best response confirmed by Lewis255 (Copper Contributor)
Solution

# Re: Excel Formula help

First cell:

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

Second cell:

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

# Re: Excel Formula help

Legend!!!
1 best response

Accepted Solutions
best response confirmed by Lewis255 (Copper Contributor)
Solution

# Re: Excel Formula help

First cell:

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

Second cell:

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