# Help with NESTED IF formula

#### knighcloud

##### New Member
Hi everyone!

I have a formula for my excel workbook. The formula for one cell contains multiple arguments and goes like this:

If B13="", then "", else

IF(OR(AND(D8>=1,D8<=5),AND(D8>=16,D8<=25),AND(D8>=36,D8<=45)),"C1",
IF(OR(AND(D8>=6,D8<=15),AND(D8>=26,D8<=35),AND(D8>=46,D8<=50)),"C2",
IF(OR(AND(D8>=51,D8<=55),AND(D8>=66,D8<=75),AND(D8>=86,D8<=95)),"C3",
IF(OR(AND(D8>=56,D8<=65),AND(D8>=76,D8<=85),AND(D8>=96,D8<=100)),"C4","")))).

I was able to apply this formula to one cell but what happens is, If I drag the cell with the formula to the next cell, the referenced cells changed. What I'm hoping to achieve is that only the range from B13 downward will change and the rest of the formula stays the same. Can anybody help me on this? Thank you!

### Excel Facts

To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

#### Michael M

##### Well-known Member
try
Code:
``=IF(B13="", "",IF(OR(AND(\$D\$8>=1,\$D\$8<=5),AND(\$D\$8>=16,\$D\$8<=25),AND(\$D\$8>=36,\$D\$8<=45)),"C1",IF(OR(AND(\$D\$8>=6,\$D\$8<=15),AND(\$D\$8>=26,\$D\$8<=35),AND(\$D\$8>=46,\$D\$8<=50)),"C2",IF(OR(AND(\$D\$8>=51,\$D\$8<=55),AND(\$D\$8>=66,\$D\$8<=75),AND(\$D\$8>=86,\$D\$8<=95)),"C3",IF(OR(AND(\$D\$8>=56,\$D\$8<=65),AND(\$D\$8>=76,\$D\$8<=85),AND(\$D\$8>=96,\$D\$8<=100)),"C4","")))))``

#### Hooi

##### Board Regular
Hi everyone!

I have a formula for my excel workbook. The formula for one cell contains multiple arguments and goes like this:

If B13="", then "", else

IF(OR(AND(D8>=1,D8<=5),AND(D8>=16,D8<=25),AND(D8>=36,D8<=45)),"C1",
IF(OR(AND(D8>=6,D8<=15),AND(D8>=26,D8<=35),AND(D8>=46,D8<=50)),"C2",
IF(OR(AND(D8>=51,D8<=55),AND(D8>=66,D8<=75),AND(D8>=86,D8<=95)),"C3",
IF(OR(AND(D8>=56,D8<=65),AND(D8>=76,D8<=85),AND(D8>=96,D8<=100)),"C4","")))).

I was able to apply this formula to one cell but what happens is, If I drag the cell with the formula to the next cell, the referenced cells changed. What I'm hoping to achieve is that only the range from B13 downward will change and the rest of the formula stays the same. Can anybody help me on this? Thank you!
For example if u wish the cell reference do not want to change just put "\$" in front of them
for A1 = \$A\$1 = column and row will retain at everywhere
\$A1= column will retain while the row will change accordingly
A\$1 = row will retain while the column will change accordingly

#### knighcloud

##### New Member
Thank you so much for your help. Your suggestion worked for me.

#### Peter_SSs

##### MrExcel MVP, Moderator
There is a lot of repetitive calculations going on there.

Perhaps I have mis-understood, but what about considering something like this?

a) Use a lookup table instead of so many nested IFs and OR(AND combinations.

b) Use D8 just once to work out the C1/C2/C3/C4 value you are interested in.

Formula in C13 is copied down.

Excel Workbook
BCDEFG
1D8C
21C1
36C2
416C1
526C2
636C1
746C2
82951C3
956C4
1066C3
1176C4
1286C3
13xC296C4
14
154C2
16fgdfC2C2
17xC2
18
knighcloud

Replies
1
Views
118
Replies
3
Views
232
Replies
2
Views
316
Replies
4
Views
473
Replies
12
Views
258

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,163,718
Messages
5,833,289
Members
430,202
Latest member
Faizal5zl

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