IF formula assistance needed

[dallen]

I have a very long IF formula that was developed to calculate commissions based upon Tier levels.

I've attached a small version of the spreadhseet entitled: Excel Help Needed.
There are Comments in the spreadsheet to explain what I'm needing the forumla to do.

Any assistance would be greatly appreciated.

Thank you,
Diane Allen

[Sam Capricci]

so is your issue that it is too long or that it isn't working?
if too long this is a shortened version of the formula...
=IF(AND(H4="Tier 1",J4<0%),G4*$N$3,IF(AND(H4="Tier 1",J4<=10%),G4*$N$4,IF(AND(H4="Tier 1",J4<=20%),G4*$N$5,IF(AND(H4="Tier 1",J4<=30%),G4*$N$6,IF(AND(H4="Tier 1",J4>30%),G4*0,IF(AND(H4="Tier 2",J4<0%),G4*$Q$3,IF(AND(H4="Tier 2",J4<=10%),G4*$Q$4,IF(AND(H4="Tier 2",J4<=20%),G4*$Q$5,IF(AND(H4="Tier 2",J4>20%),G4*0,IF(AND(H4="Tier 3",J4<0%),G4*$N$9,IF(AND(H4="Tier 3",J4<=10%),G4*$N$10,IF(AND(H4="Tier 3",J4>10%),G4*0,IF(AND(H4="Tier 4",J4<=0%),G4*$Q$9,IF(AND(H4="Tier 4",J4>0%),G4*0))))))))))))))
you don't need the "between" sections because the formula stops when it reaches the answer.

BTW, I'm looking at alternatives too.

[dallen]

The issue is that it is calculating the Above Retail commission at the Above Retail percentage at any amount above retail.
It should only calculate using the Above Retail % if the retail has been raised 10% or higher, not raised from 0.1-9%

[Sam Capricci]

your formula is wrong.
where you have IF(AND(H5="Tier 3",J5<0%),G5*$N$9 you want it to point to N10 instead.
in fact, it appears that as I'm looking at your formula you might have the percentages upside down to your if statement.
so if it is tier 1 and less than 0% you want them to get 10% "comm"?

[CRIMEDOG]

This is what I usually do - Add a helping column to your commissions. So for Example Tier 1 would have a column to the left. Fill that column with the HIGHEST % in you categories. So if you want to pay 10% for anything above 10% that would have the highest possible % (I used 1000%) - the next line in L is 10% for 8% commission - etc...
Then use Match to determine what line commission is from Match(L4,L3:L7,-1) Tier 1 Match(L4,L9:L11,-1) etc... the last row is 0 commission

then you can use if- IF($H4="Tier 1",Index($L$3:$N$7,Match($L4,$L$3:$L$7,-1),3),IF($H4="Tier 2",Index($L$9:$L$11,Match($L4,$L$9:$L$11,-1),3),IF...

[dallen]

Yes, I realize the formula is actually backwards. The owner of the company develoepd this back in 2004.
Under the Disc column, if it shows a negative, meaning they marked it up. If it shows a positive under the Disc column, they've marked it down.

[Sam Capricci]

Your note in the attachment indicates the correct answer is supposed to be $94.44 and not $157.40. The only ways to get that is to either switch the values in N9 with N10 or change where your formula points or change your greater than / equal to sections.

EDIT: I did notice that if you change the formula in the Disc% from =1-(F4/I4) to =F4/I4-1 and no other changes it will calculate the commission at $94.44.
maybe that will help?