NESTING FORMULAS

[CHADA13]

I am using MSOFFICE 97 and i need to nest 8 formulas in one cell, but I'm only able to nest 7. Maybe there is an easier (shorter) formula I can use? The sheet is set up with 4 different charts (charts 1-4). Each chart has a about 20 different model#'s. Once the user picks the chart and model#, the sheet calculates the base price. I'm OK up to this point. Now I need the cell to reference the correct cell in the corresponding chart based on the number of pumps (2 or 4 pumps). These are two separate columns in the charts. This is where I run into trouble.

Is there an easier formula? Would there be a better way, such as INDEX and MATCH?

Here is how the sheets are set up:

CHART# (entered by user)
MODEL# (entered by user)
# OF PUMPS (entered by user)

CHART 1
2 PUMPS 4 PUMPS
MODEL 1 xxx xxx
MODEL 2 xxx xxx
MODEL 3 xxx xxx
MODEL 4 xxx xxx

CHART 2
2 PUMPS 4 PUMPS
MODEL 1 xxx xxx
MODEL 2 xxx xxx
MODEL 3 xxx xxx
MODEL 4 xxx xxx


CHART 3
2 PUMPS 4 PUMPS
MODEL 1 xxx xxx
MODEL 2 xxx xxx
MODEL 3 xxx xxx
MODEL 4 xxx xxx

CHART 4
2 PUMPS 4 PUMPS
MODEL 1 xxx xxx
MODEL 2 xxx xxx
MODEL 3 xxx xxx
MODEL 4 xxx xxx

[Yogi Anand]

How about posting the formula that you are using now ... and then let us see how to accomplish the desired result without running into the nest_limit problem!

Regards

[CHADA13]

Here is the forumula I currently have:
=IF(D5=1,0,IF(AND(D5=2,B3="AIR-CONTAINED"),VLOOKUP(B4,A89:H110,3,FALSE),IF(AND(D5=4,B3="AIR-CONTAINED"),VLOOKUP(B4,A89:H110,4,FALSE),IF(AND(D5=2,B3="AIR-SPLIT"),VLOOKUP(B4,A58:I79,3,FALSE),IF(AND(D5=4,B3="AIR-SPLIT"),VLOOKUP(B4,A58:I79,4,FALSE),IF(AND(D5=2,B3="EVAP-SPLIT"),VLOOKUP(B4,A26:K48,3,FALSE),IF(AND(D5=4,B3="EVAP-SPLIT"),VLOOKUP(B4,A26:K48,4,FALSE),0)))))))

[Asala42]

Greetings,

Here is the forumula I currently have:
=IF(D5=1,0,IF(AND(D5=2,B3="AIR-CONTAINED"),VLOOKUP(B4,A89:H110,3,FALSE),IF(AND(D5=4,B3="AIR-CONTAINED"),VLOOKUP(B4,A89:H110,4,FALSE),IF(AND(D5=2,B3="AIR-SPLIT"),VLOOKUP(B4,A58:I79,3,FALSE),IF(AND(D5=4,B3="AIR-SPLIT"),VLOOKUP(B4,A58:I79,4,FALSE),IF(AND(D5=2,B3="EVAP-SPLIT"),VLOOKUP(B4,A26:K48,3,FALSE),IF(AND(D5=4,B3="EVAP-SPLIT"),VLOOKUP(B4,A26:K48,4,FALSE),0)))))))

To start, you might want to use a few named ranges:
A89:H110 = "AIR-CONTAINED"
A26:K48 = "EVAP-SPLIT"
A58:I79 = "AIR-SPLIT"
A(whatever) = "The other one" (you had 4 ranges I think- you want these to match entries in B3)

How about:

=IF(OR(D5=1,AND(D5<>2,D5<>4)),0,VLOOKUP(B4,INDIRECT(B3),IF(D5=2,3,4),0))

Hope that helps
Adam

[croweld89]

if you do a netsearch for nesting if statments you can find a web sight that has a formula for nesting up to 25+ if statements.

It goes a little something like this...

=IF(OR(AND(B3=1,C3="a"),AND(B3=2,C3="b"),AND(B3=3,C3="c")),2,0)

The first section defines the variables
and(b3=1,c3="a"),
and(b3=2,c3="a"),

etc... place the first six here. Then put
your vlookup formulas where the 2 is.
if(and(b3=2,c3="b"),vlookup(),
if(and(b3=2,c3="b"),vlookup(),
if(and(b3=3,c3="c"),vlookup(),

Don't put...
if(and(b3=6,c3="f"),vlookup() in the sixth area as this will be the if "false". So we will have...

=IF(OR(AND(B3=1,C3="a"),AND(B3=2,C3="b"),AND(B3=3,C3="c"),and(b3=4,c3="d"),and(b3=5,c3="e"),and(b3=6,c3="f"),if(and(b3=2,c3="b"),vlookup(),if(and(b3=2,c3="b"),vlookup(),if(and(b3=3,c3="c"),vlookup(),if(and(b3=4,c3="d"),vlookup(),if(and(b3=5,c3="e"),vlookup(),vlookup())if(or(and(b3=7,c3="g"),and(b3=8,c3="h")),if(and(b3=7,c3="g"),vlookup(),vlookup()))

This formula won't fit unless you follow the above advice and name areas of your spreadsheet. You will then have to modify this formula for your needs of course.

Have fun!
-Dave

[Aladin Akyurek]

On 2002-05-14 12:30, croweld89 wrote:
if you do a netsearch for nesting if statments you can find a web sight that has a formula for nesting up to 25+ if statements.


Dave,

It's always better to switch to something taht is shorter instead of using a long chain of N nested IFs. Such a formula is hard to understand and debug. Often a juicious VLOOKUP can replace such an IF.

Aladin