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!!

 

[energman58]

If I follow what you are trying to do you want to pay 50 if comission is positive or the actual comission whichever is greater you need a formula that looks like this:

=if(H10>0,Max(50,H10*P71),H10*P71)

If H10 is negative this will generate a negative comission but as your example had this I left it in. If you dont want this change H10*P71 at the end of the if to zero

 

[grrrrr]

Thanks energman.

That works! But it can often be a negative figure. If it is the minimum should still be £50.00.

Is that possible?

 

[grrrrr]

And also, if the commission changes to 12.5% ie min £40.00 how would I add that to the formula?

 

[energman58]

Yes just change it to this:

=if(H10>0,Max(50,H10*P71),50)

 

[energman58]

P71 will change automatically so you need to make your formula dependent on this - not sure how you determine the minmum comission

Sorry off home now maybe someone can help you before I am back in the office on Saturda

 

[grrrrr]

You're a star!

Yes, P71 updates automatically to show the percentage rate.

Commission can be £30, £40 or £50 minimum so the formula needs to be able to calculate under any of the circumstances

 

[jasonb75]

Just glancing over the thread quickly it seems is if all you need is

=MAX(50,H10*P71)

Or basic principle for the different minimum commission / %age rates.

=MAX(£commission,profit*%age)

 

[grrrrr]

Thanks for your reply Jason.

The commissions will be changing all the time and for different people on differet sheets so I can't just alter the commission manually when they change every time, which is what I think your formula would require?

Can all 3 commissions be involved in the formula?

 

[jasonb75]

They can, but how do you decide who gets which rate?

The formula I've suggested gives the same results as =if(H10>0,Max(50,H10*P71),50)