Help with if statements based on percentages

OK, so I need to get a formula where my commission percentage is based upon the amount of the sale. For instance, if I make a sale less than $50, my commission is 60%. If I make a sale between 50.01 and 100.00, my commission is 55%. 100.01-200.00 is 50%. 200.01-300.00 is 45%. 300.01 to 400.00 is 40%. 400.01-500.00 is 35%. Over 500 is 30%

I tried to do this on my own and came up with this (h7 of course being the sale amount):
=IF(H7<50.01, H7*0.6,0), IF(AND(H7>50,H7<100.01), H7*0.55,0), IF(AND(H7>100,H7<200.01), H7*0.5,0), IF(AND(H7>200,H7<300.01), H7*0.45,0), IF(AND(H7>300,H7<400.01), H7*0.4,0), IF(AND(H7>400,H7<500.01), H7*0.35,0), IF(H7>500, H7*0.3,0)

But it keeps giving me the #VALUE! error. Help?

Try

=Lookup(H7,{0,50.01,100.01,200.01,300.01,500.01},{0.6,0.55,0.5,0.45,0.4,.3})

Ok, I tried that and for a sale of $125.18 it's giving me a commission of $.50 (50 cents) instead of $62.59...it looks as if it is simply giving the percentage value based upon the amount as the answer...did you mean to simply put that formula in place of the one I tried or in conjunction with it somehow?

p.s. I also added some values

=LOOKUP(H7,{0,50.01,100.01,200.01,300.01,400.01,500.01},{0.6,0.55,0.5,0.45,0.4,0.35,0.3})

Sorry:

=H7*Lookup(H7,{0,50.01,100.01,200.01,300.01,500.01},{0.6,0.55,0.5,0.45,0.4,.3})

Originally Posted by mfrey0118

Ok nevermind...I get it. I just use that formula to ascribe the proper percentage value. So instead of having a very long string in one cell I just add another column for the percentage for that sale and then I can just use (assuming the formula is in g7):

h7*g7

Thanks for your help...

You can put it all in one cell as I showed in my last post... with your added items in the arrays...

Please remember to mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save