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]

Welcome to the board.

Try

=MIN(8,INT(L4/20)+1)

 

[Jonmo1]

Wait, that will give 1 for a 0, and 2 for 20, 3 for 40 etc...

This should work better

=CEILING(L4/20,1)

 

[Kanekutter05]

Well the function works fantastic it's getting me exactly what I need. Is there any way that I can cap it so no matter how high the number may get the highest value for the cell will be 8?

 

[Jonmo1]

Yep, forgot to wrap the 2nd formula in the MIN function..

=MIN(8,CEILING(L4/20,1))

 

[Kanekutter05]

Holy crap thank you very much I was working on that for forever...what a great site! Thanks again!

 

[Jonmo1]

Glad to help, thanks for the feedback.

 

[Kanekutter05]

So...I'm an idiot. I actually need my cell to say 0 for 0-19% and then 1 for 20-39%...I didn't explain it right and I just realized. What should I do to change the formula to reflect this? Sorry man...

 

[Jonmo1]

Change CEILING to FLOOR

 

[Kanekutter05]

Ah I knew it was something simple...thanks again! You just made my boss and the brass here very happy that this thing will work the way they want it to. Now if I could just cut down on the user error in making the sheet I'll be set lol