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


"Joe User" wrote:

PS....

I wrote:
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),
[....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" wrote:

"sed" wrote:
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....]

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.

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","tr to"},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" wrote:
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!

--
sss