IF, AND, formula help !! Please !!

[grrrrr]

I will try and explain as best I can.

Commissions are paid at different rates and there is always a minimum commission.

There is one cell in the spreadsheet which shows the % rate at which commissions should be paid.

There is another showing the current commission based on the profit made. If the commission % (say 15% which has a minimum commission of £50) if above £50, then it should stay at the 15% of the profit. But if this is below £50, then it wants to change to £50.00.

After this, I will need to add in a rule for 12.5% - £40.00 etc but the first bit should point me in the right direction.

I had the following attempt and it says #value:

=IF(H10>0,(IF(P71=15%,AND(H10*P71)<=50,"50.00"),H10*P71))

H10 = profit on which the 15% is based.

In this case 15% of H10 is around £40, so it wants to change to £50.00 rather than stay at 15% of H10.

P71 is the cell calculating the commission rate (in this case 15%).

Any ideas?????

I'm a bit new to this!

Thanks!!

 

[grrrrr]

Well P71 can either be 8%, 10% which are both a min of £30

12.5% which is £40

and

15% which is £50

 

[jasonb75]

Try

=MAX(B11*C11,LOOKUP(C11,{0.08,30;0.125,40;0.15,50}))

 

[grrrrr]

I'm not sure on that one. Are B11 and C11 H10 and P71?

Will it recognise 0.08 etc as 8%?

I might have to put them all at minimum commission and just change them if/when needed.

 

[jasonb75]

Sorry, forgot to change it back, I used closer cells to test the formula.

=MAX(H10*P71,LOOKUP(P71,{0.08,30;0.125,40;0.15,50}))

It will recognise 0.8 as anything greater than or equal to 8% but less than 12.5%

If you want EXACT %ages only then use

=MAX(H10*P71,VLOOKUP(P71,{0.08,30;0.10,30;0.125,40;0.15,50},2,0))

That way if P71 contains a value of say 11% then the formula will return #N/A!
while the original suggestion would have still calculated with potentialy incorrect results.

 

[grrrrr]

Hi Jason - thanks again but I am just getting #N/A?

 

[jasonb75]

Which of the 2 formula are you trying?

What values are in H10 and P71?

 

[grrrrr]

I tried both with the same result.

P71 is "15%". Might it be that it isn't an actual figure?

H10 are variable - say £200.

 

[jasonb75]

In another cell try

=isnumber(P71)
=isnumber(H10)

Do either of them return FALSE?

Do you still get N/A from

=MAX(H10*P71,VLOOKUP(--P71,{0.08,30;0.10,30;0.125,40;0.15,50},2,0))

 

[grrrrr]

The final formula works perfectly - thank you so much!!

 

[jasonb75]

That means P71 wasn't a number, it just looked like it should be

15% is a number

"15%" is text

--"15%" is text that looks like a number converted back to a number, which is the method used in the last formula

=MAX(H10*P71,VLOOKUP(--P71,{0.08,30;0.10,30;0.125,40;0.15,50},2,0))