# 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

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

#### 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
7
Views
67
Replies
3
Views
58
Replies
4
Views
100
Replies
2
Views
61
Replies
3
Views
48