Long Long Long Nested If Function

S

sed

Hello,
I need some help on a very long nested if formula. Or at least thats the
type that I have been trying to use to get the calculations on my
spreadsheet.

I have this formula which is working for me but it is only one of the 5
variables I need to include in it.
1) =IF(B5="cdy",IF(P23=20,1625,(IF(P23=40,1875,2200))))

to this formula I need to add

=IF(B5="nwr",IF(P23=20,850,(IF(P23=40,935,1260))))
=IF(B5="lsp",IF(P23=20,1125,(IF(P23=40,1225,1550))))
=IF(B5="mtrl",IF(P23=20,1035,(IF(P23=40,1150,1500))))
=IF(B5="trto",IF(P23=20,1450,(IF(P23=40,1650,1950))))

So I was wondering if there is a way to have all of these as ONE whole
formula. I will appreciate your help very much.

Thanks!
 
J

Joe User

sed said:
I have this formula which is working for me but it is only one
of the 5 variables I need to include in it.
1) =IF(B5="cdy",IF(P23=20,1625,(IF(P23=40,1875,2200))))
to this formula I need to add
=IF(B5="nwr",IF(P23=20,850,(IF(P23=40,935,1260))))
[....etc....]
Click to expand...

One approach.... Construct the following table in some unused range of cells:

X1: cdy Y1: =IF(P23=20,1625, IF(P23=40,1875,2200))
X2: nwr Y2: =IF(P23=20,850, IF(P23=40,935,1260))
X3: lsp Y3: =IF(P23=20,1125, IF(P23=40,1225,1550))
X4: mtrl Y4: =IF(P23=20,1035, IF(P23=40,1150,1500))
X5: trto Y5: =IF(P23=20,1450, IF(P23=40,1650,1950))

Then, in the cell where you would have written the combined IF expression,
enter:

=VLOOKUP(B5, X1:Y5, 2, 0)

If B5 might contain some undefined string, then:

=IF(ISERROR(VLOOKUP(B5,X1:Y5,2, 0)), "", VLOOKUP(B5,X1:Y5,2, 0))

So I was wondering if there is a way to have all of these
as ONE whole formula.
Click to expand...

Arguably, the formulas in Y1:Y5 can be "simplified", albeit error-prone:

Y1: =2200 - 575*(P3=20) - 325*(P3=40)
Y2: =1260 - 410*(P3=20) - 325*(P3=40)
Y3: =1550 - 425*(P3=20) - 325*(P3=40)
Y4: =1500 - 465*(P3=20) - 350*(P3=40)
Y5: =1950 - 500*(P3=20) - 300*(P3=40)

That lends itself to the following formula without the need for a table:

=CHOOSE(MATCH(B5,{"cdy","nwr","lsp","mtrl","trto"},0),
2200 - 575*(P3=20) - 325*(P3=40),
1260 - 410*(P3=20) - 325*(P3=40),
1550 - 425*(P3=20) - 325*(P3=40),
1500 - 465*(P3=20) - 350*(P3=40),
1950 - 500*(P3=20) - 300*(P3=40))

With error-checking:

=IF(ISERROR(MATCH(B5,{"cdy","nwr","lsp","mtrl","trto"},0)), "",
CHOOSE(MATCH(B5,{"cdy","nwr","lsp","mtrl","trto"},0),
2200 - 575*(P3=20) - 325*(P3=40),
1260 - 410*(P3=20) - 325*(P3=40),
1550 - 425*(P3=20) - 325*(P3=40),
1500 - 465*(P3=20) - 350*(P3=40),
1950 - 500*(P3=20) - 300*(P3=40))


----- original message -----
 
D

David Biddulph

=IF(B5="cdy",IF(P23=20,1625,(IF(P23=40,1875,2200))),IF(B5="nwr",IF(P23=20,850,(IF(P23=40,935,1260))),IF(B5="lsp",IF(P23=20,1125,(IF(P23=40,1225,1550))),IF(B5="mtrl",IF(P23=20,1035,(IF(P23=40,1150,1500))),IF(B5="trto",IF(P23=20,1450,(IF(P23=40,1650,1950))),"")))))
 
J

Joe User

PS....
Construct the following table in some unused range of cells:
X1: cdy Y1: =IF(P23=20,1625, IF(P23=40,1875,2200)) [....etc....]
Arguably, the formulas in Y1:Y5 can be "simplified", albeit error-prone:
Y1: =2200 - 575*(P3=20) - 325*(P3=40) [....etc....]
That lends itself to the following formula without the need for a table:
=CHOOSE(MATCH(B5,{"cdy","nwr","lsp","mtrl","trto"},0),
2200 - 575*(P3=20) - 325*(P3=40),
Click to expand...
[....etc....]

Of course, you can use the CHOOSE/MATCH combination with the original IF
expressions, to wit:

CHOOSE(MATCH(B5,{"cdy","nwr","lsp","mtrl","trto"},0),
IF(P23=20,1625, IF(P23=40,1875,2200)),
IF(P23=20,850, IF(P23=40,935,1260)),
IF(P23=20,1125, IF(P23=40,1225,1550)),
IF(P23=20,1035, IF(P23=40,1150,1500)),
IF(P23=20,1450, IF(P23=40,1650,1950)))


----- original message -----

Joe User said:
sed said:
I have this formula which is working for me but it is only one
of the 5 variables I need to include in it.
1) =IF(B5="cdy",IF(P23=20,1625,(IF(P23=40,1875,2200))))
to this formula I need to add
=IF(B5="nwr",IF(P23=20,850,(IF(P23=40,935,1260))))
[....etc....]
Click to expand...

One approach.... Construct the following table in some unused range of cells:

X1: cdy Y1: =IF(P23=20,1625, IF(P23=40,1875,2200))
X2: nwr Y2: =IF(P23=20,850, IF(P23=40,935,1260))
X3: lsp Y3: =IF(P23=20,1125, IF(P23=40,1225,1550))
X4: mtrl Y4: =IF(P23=20,1035, IF(P23=40,1150,1500))
X5: trto Y5: =IF(P23=20,1450, IF(P23=40,1650,1950))

Then, in the cell where you would have written the combined IF expression,
enter:

=VLOOKUP(B5, X1:Y5, 2, 0)

If B5 might contain some undefined string, then:

=IF(ISERROR(VLOOKUP(B5,X1:Y5,2, 0)), "", VLOOKUP(B5,X1:Y5,2, 0))

So I was wondering if there is a way to have all of these
as ONE whole formula.
Click to expand...

Arguably, the formulas in Y1:Y5 can be "simplified", albeit error-prone:

Y1: =2200 - 575*(P3=20) - 325*(P3=40)
Y2: =1260 - 410*(P3=20) - 325*(P3=40)
Y3: =1550 - 425*(P3=20) - 325*(P3=40)
Y4: =1500 - 465*(P3=20) - 350*(P3=40)
Y5: =1950 - 500*(P3=20) - 300*(P3=40)

That lends itself to the following formula without the need for a table:

=CHOOSE(MATCH(B5,{"cdy","nwr","lsp","mtrl","trto"},0),
2200 - 575*(P3=20) - 325*(P3=40),
1260 - 410*(P3=20) - 325*(P3=40),
1550 - 425*(P3=20) - 325*(P3=40),
1500 - 465*(P3=20) - 350*(P3=40),
1950 - 500*(P3=20) - 300*(P3=40))

With error-checking:

=IF(ISERROR(MATCH(B5,{"cdy","nwr","lsp","mtrl","trto"},0)), "",
CHOOSE(MATCH(B5,{"cdy","nwr","lsp","mtrl","trto"},0),
2200 - 575*(P3=20) - 325*(P3=40),
1260 - 410*(P3=20) - 325*(P3=40),
1550 - 425*(P3=20) - 325*(P3=40),
1500 - 465*(P3=20) - 350*(P3=40),
1950 - 500*(P3=20) - 300*(P3=40))


----- original message -----

sed said:
Hello,
I need some help on a very long nested if formula. Or at least thats the
type that I have been trying to use to get the calculations on my
spreadsheet.

I have this formula which is working for me but it is only one of the 5
variables I need to include in it.
1) =IF(B5="cdy",IF(P23=20,1625,(IF(P23=40,1875,2200))))

to this formula I need to add

=IF(B5="nwr",IF(P23=20,850,(IF(P23=40,935,1260))))
=IF(B5="lsp",IF(P23=20,1125,(IF(P23=40,1225,1550))))
=IF(B5="mtrl",IF(P23=20,1035,(IF(P23=40,1150,1500))))
=IF(B5="trto",IF(P23=20,1450,(IF(P23=40,1650,1950))))

So I was wondering if there is a way to have all of these as ONE whole
formula. I will appreciate your help very much.

Thanks!
Click to expand...
Click to expand...
 
S

sed

Joe thanks a lot! I appreciate your help!
--
sss


Joe User said:
PS....
Construct the following table in some unused range of cells:
X1: cdy Y1: =IF(P23=20,1625, IF(P23=40,1875,2200)) [....etc....]
Arguably, the formulas in Y1:Y5 can be "simplified", albeit error-prone:
Y1: =2200 - 575*(P3=20) - 325*(P3=40) [....etc....]
That lends itself to the following formula without the need for a table:
=CHOOSE(MATCH(B5,{"cdy","nwr","lsp","mtrl","trto"},0),
2200 - 575*(P3=20) - 325*(P3=40),
Click to expand...
[....etc....]

Of course, you can use the CHOOSE/MATCH combination with the original IF
expressions, to wit:

CHOOSE(MATCH(B5,{"cdy","nwr","lsp","mtrl","trto"},0),
IF(P23=20,1625, IF(P23=40,1875,2200)),
IF(P23=20,850, IF(P23=40,935,1260)),
IF(P23=20,1125, IF(P23=40,1225,1550)),
IF(P23=20,1035, IF(P23=40,1150,1500)),
IF(P23=20,1450, IF(P23=40,1650,1950)))


----- original message -----

Joe User said:
sed said:
I have this formula which is working for me but it is only one
of the 5 variables I need to include in it.
1) =IF(B5="cdy",IF(P23=20,1625,(IF(P23=40,1875,2200))))
to this formula I need to add
=IF(B5="nwr",IF(P23=20,850,(IF(P23=40,935,1260))))
[....etc....]
Click to expand...

One approach.... Construct the following table in some unused range of cells:

X1: cdy Y1: =IF(P23=20,1625, IF(P23=40,1875,2200))
X2: nwr Y2: =IF(P23=20,850, IF(P23=40,935,1260))
X3: lsp Y3: =IF(P23=20,1125, IF(P23=40,1225,1550))
X4: mtrl Y4: =IF(P23=20,1035, IF(P23=40,1150,1500))
X5: trto Y5: =IF(P23=20,1450, IF(P23=40,1650,1950))

Then, in the cell where you would have written the combined IF expression,
enter:

=VLOOKUP(B5, X1:Y5, 2, 0)

If B5 might contain some undefined string, then:

=IF(ISERROR(VLOOKUP(B5,X1:Y5,2, 0)), "", VLOOKUP(B5,X1:Y5,2, 0))

So I was wondering if there is a way to have all of these
as ONE whole formula.
Click to expand...

Arguably, the formulas in Y1:Y5 can be "simplified", albeit error-prone:

Y1: =2200 - 575*(P3=20) - 325*(P3=40)
Y2: =1260 - 410*(P3=20) - 325*(P3=40)
Y3: =1550 - 425*(P3=20) - 325*(P3=40)
Y4: =1500 - 465*(P3=20) - 350*(P3=40)
Y5: =1950 - 500*(P3=20) - 300*(P3=40)

That lends itself to the following formula without the need for a table:

=CHOOSE(MATCH(B5,{"cdy","nwr","lsp","mtrl","trto"},0),
2200 - 575*(P3=20) - 325*(P3=40),
1260 - 410*(P3=20) - 325*(P3=40),
1550 - 425*(P3=20) - 325*(P3=40),
1500 - 465*(P3=20) - 350*(P3=40),
1950 - 500*(P3=20) - 300*(P3=40))

With error-checking:

=IF(ISERROR(MATCH(B5,{"cdy","nwr","lsp","mtrl","trto"},0)), "",
CHOOSE(MATCH(B5,{"cdy","nwr","lsp","mtrl","trto"},0),
2200 - 575*(P3=20) - 325*(P3=40),
1260 - 410*(P3=20) - 325*(P3=40),
1550 - 425*(P3=20) - 325*(P3=40),
1500 - 465*(P3=20) - 350*(P3=40),
1950 - 500*(P3=20) - 300*(P3=40))


----- original message -----

sed said:
Hello,
I need some help on a very long nested if formula. Or at least thats the
type that I have been trying to use to get the calculations on my
spreadsheet.

I have this formula which is working for me but it is only one of the 5
variables I need to include in it.
1) =IF(B5="cdy",IF(P23=20,1625,(IF(P23=40,1875,2200))))

to this formula I need to add

=IF(B5="nwr",IF(P23=20,850,(IF(P23=40,935,1260))))
=IF(B5="lsp",IF(P23=20,1125,(IF(P23=40,1225,1550))))
=IF(B5="mtrl",IF(P23=20,1035,(IF(P23=40,1150,1500))))
=IF(B5="trto",IF(P23=20,1450,(IF(P23=40,1650,1950))))

So I was wondering if there is a way to have all of these as ONE whole
formula. I will appreciate your help very much.

Thanks!
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Nesting If Statements 1
limit of 7 nested functions? 3
excel modular sheets subsheets 0
Switch formula in query results in "Text too long to be edited." 3
Nested IF & MAX Function 4
VBA function to replace nested IFs 8
Nested IF function 4
Importing Data Into Excel From LONG Web Page With URL Ending With .cfm 0

Top