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!
 

Excel Facts

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

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
21,276
Office Version
  1. 365
  2. 2007
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
54,275
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
 
Master Excel Bundle

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.

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top