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]

I thought it might have something to do with that when I saw the ""

I wouldn't have had a clue how to go about the formula though - thanks again - this will save me a lot of time and grief!

 

[jasonb75]

If you're used to VLOOKUP in the conventional way, maybe this would be easier to understand.

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD>%age</TD><TD>Min</TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="TEXT-ALIGN: right">8%</TD><TD style="TEXT-ALIGN: right">£ 30.00 </TD><TD> </TD><TD style="TEXT-ALIGN: right">12.5%</TD><TD style="TEXT-ALIGN: right">£ 625.00 </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="TEXT-ALIGN: right">10%</TD><TD style="TEXT-ALIGN: right">£ 30.00 </TD><TD> </TD><TD style="TEXT-ALIGN: right">5000</TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="TEXT-ALIGN: right">12.5%</TD><TD style="TEXT-ALIGN: right">£ 40.00 </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="TEXT-ALIGN: right">15%</TD><TD style="TEXT-ALIGN: right">£ 50.00 </TD><TD> </TD><TD> </TD><TD> </TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>E2</TD><TD>=MAX(D3*D2,VLOOKUP(D2,$A$2:$B$5,2,0))</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4


It would also be easier to edit if any of the commission rates are changed.

 

[grrrrr]

So close - just one more thing.

I have copied the formula in (adding $ to the cell that doesn't want to change) but the cells with no figures in yet say #VALUE!

Is there an easy way to make these 0 ??

Thanks

 

[grrrrr]

And false in some - not sure why they are different...

 

[jasonb75]

I'm assuming P71 is the cell to check, does that cell hold a formula?

 

[grrrrr]

Oh, and some have £30 in with nothing in the H column??

Probably an easy fix - so close now

 

[grrrrr]

Right just checked and with that formula, the only thing not quite right is the ones that say #VALUE!

These are spaces for sales basically that have a formula in ready to pull from another page but there is nothing to pull through so they are blank. (H column). P71 is the "15%" etc one.

If possibly I would like the above formula to come out as zero (0) for these instead of #VALUE!

 

[jasonb75]

I don't know where FALSE has come from, that can only come from a logical test formula, which we don't have here.

Try

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

 

[grrrrr]

I think that has cracked it.

Nice one Jason. I'll stop pestering you now. At least for a day anyway!

Thank you !!!