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

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
19,134
Office Version
2013
Platform
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
45,918
Office Version
365
Platform
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
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,663
Messages
5,512,717
Members
408,910
Latest member
fchri31

This Week's Hot Topics

  • Sort code advice please
    Hi, I have the code below which im trying to edit but getting a little stuck. This was the original code which worked fine,columns A-F would sort...
  • SUMPRODUCT with nested If statement
    Hi everyone, Hope you're all well. I'm hoping someone will be able to point me in the right direction with a problem I'm having with a SUMPRODUCT...
  • VBA - simple sort is killing me!
    Hello all! This should be so easy, but not for me, apparently! I have a table of data that can be of varying lengths and widths. My current macro...
  • Compare Two Lists
    I have two Lists and I need to be able to Identify differences between them. List 100 comes from a workbook - the other is downloaded form the...
  • Formula that deducts points for each code I input.
    I am trying to create a formula that will have each student in my class start at 100 points and then for each code that I enter (PP for Poor...
  • Conditional formatting formula required for day of week and a value
    Hi, I have a really simple spreadsheet where column A is the date, column B is the activity total shown as a number and column C states the day of...
Top