Complicated (for me anyways) IF Statement

[Kanekutter05]

So I'm trying to do this formula...it's getting pretty hairy. Here's the situation I need to get the formula to work for me on:

For every 20% more that a company spends with us than they did last year, they get a 0.5% rebate up to 4%

I've got a column on my sheet that has the percentage change in $$ from last year to this year. So basically if the percentage is anywhere from 0-20% I want the cell to say 1...from 21-40% to say 2 and so on and so forth. Here's what I came up with from piecing together different example formulas across the internet:

=IF(AND(L4>0,L4<=20),1,IF(AND(L4>20,L4<=40),2,IF(AND(L4>40,L4<=60),3,IF(AND(L4>60,L4<=80),4,
IF(AND(L4>80,L4<=100),5,IF(AND(L4>100,L4<=120),6,IF(AND(L4>120,L4<=140),7,IF(AND(L4>140,L4<160),8,""))))))))

I know...messy messy messy. If there is a cleaner way to do it that would be great, but basically I just want it to have the functionality. Let me know if you can help out. I thank you in advance.

 

[Jonmo1]

Now if I could just cut down on the user error in making the sheet I'll be set lol

Becoming a member of MrExcel.Com was a BIG step toward that goal