Forum Discussion
Solved
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
First cell:
=MAX(MIN(A1, 50000)-30000, 0)*12.5%
Second cell:
=MAX(MIN(A1, 75000)-50000, 0)*15%
- 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?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).
First cell:
=MAX(MIN(A1, 50000)-30000, 0)*12.5%
Second cell:
=MAX(MIN(A1, 75000)-50000, 0)*15%
