Help with NESTED IF formula

knighcloud

New Member
Joined
Jul 20, 2010
Messages
13
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!
 

Some videos you may like

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
Joined
Oct 27, 2005
Messages
19,243
Office Version
  1. 2013
Platform
  1. Windows
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
Joined
Jun 14, 2010
Messages
203
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
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,210
Office Version
  1. 365
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,108,918
Messages
5,525,624
Members
409,657
Latest member
19JimRon72

This Week's Hot Topics

Top