#### Kanekutter05

##### New Member

- Joined
- Oct 25, 2011

- Messages
- 21

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.