# Complicated (for me anyways) IF Statement

#### Kanekutter05

##### New Member
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.

### Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
Welcome to the board.

Try

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

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

This should work better

=CEILING(L4/20,1)

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?

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

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

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

Glad to help, thanks for the feedback.

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...

Change CEILING to FLOOR

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

Replies
1
Views
229
Replies
7
Views
486
Replies
4
Views
220
Replies
6
Views
450
Replies
19
Views
446

1,203,760
Messages
6,057,204
Members
444,914
Latest member
Mamun12345

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back