Help with NESTED IF formula

[knighcloud]

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!

 

[Michael M]

try

=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]

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]

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

 

[Peter_SSs]

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
	B	C	D	E	F	G
1					D8	C
2					1	C1
3					6	C2
4					16	C1
5					26	C2
6					36	C1
7					46	C2
8			29		51	C3
9					56	C4
10					66	C3
11					76	C4
12					86	C3
13	x	C2			96	C4
14
15	4	C2
16	fgdf	C2				C2
17	x	C2
18
knighcloud