Nested IF troubles
I'm looking to do a NestedIF formula where I set up a predictive "Projected Ship Date" column to use additional columns to the below (old screenshot, but I figured it indicated appropriately enough). Not sure what part I have incorrect but I am getting an #Incorrect Argument Set error.
Formula I'm using: =IF([LNP Formulation]@row = "Yes", [Capping Estimate]@row + 14, [Capping Actual]@row + 14, IF([LNP Formulation]@row = "No", [Capping Estimate]@row + 7, [Capping Actual]@row + 7))
The goal is IF I have a "Yes" in the column [LNP Formulation], then add 14 days to from date in [Capping Estimate] but if there is a date in [Capping Actual], use that instead. In addition to that is if [LNP Formulation] say "No", do the same as above, but use +7 not 14.
HELP, making me crazy!
Jen Lawson, PMP
Vernal Biosciences
Colchester, VT 05446
Best Answer

So the date columns then?
Give this a try:
=IF(OR([Capping Estimate]@row <> "", [Capping Actual]@row <> ""), IF([Capping Actual]@row <> "", [Capping Actual]@row, [Capping Estimate]@row) + IF([LNP Formulation]@row = "Yes", 14, 7))
Answers

=IF([Capping Actual]@row,<>"",[Capping Actual]@row,IF([LNP Formulation]@row,"yes",[Capping Estimate]@row+14,[Capping Estimate]@row+7))

=IF([Capping Actual]@row, <>"", [Capping Actual]@row, IF([LNP Formulation]@row, "Yes", [Capping Estimate]@row + 14, [Capping Estimate]@row + 7))
Thanks Hollie, I entered as noted above and recvd an #Incorrect Argument error.
Jen Lawson, PMP
Vernal Biosciences
Colchester, VT 05446

Can anyone help with an updated formula assist? Please and thank you! :)
Jen Lawson, PMP
Vernal Biosciences
Colchester, VT 05446

Try this:
=IF([Capping Actual]@row <> "", [Capping Actual]@row, [Capping Estimate]@row) + IF([LNP Formulation]@row = "Yes", 14, 7)

Mostly it works, except now, for blanks I have "7". I assume I need to add an IFERROR??
Jen Lawson, PMP
Vernal Biosciences
Colchester, VT 05446

Blanks in which column exactly?

Blanks in any of the date columns,
If it's not a live line (a discount, not production for example) there are no dates for reference.
Jen Lawson, PMP
Vernal Biosciences
Colchester, VT 05446

So the date columns then?
Give this a try:
=IF(OR([Capping Estimate]@row <> "", [Capping Actual]@row <> ""), IF([Capping Actual]@row <> "", [Capping Actual]@row, [Capping Estimate]@row) + IF([LNP Formulation]@row = "Yes", 14, 7))

@Paul Newcome you are my Smartsheet hero!
Jen Lawson, PMP
Vernal Biosciences
Colchester, VT 05446

@Paul Newcome , you up for one more? I have so many of these if this, do this, if that do that type formulas....
This one is a Projected ship date, based on 2 columns. If there is a date in the LNP Actual column, add 2 days if not, add 2 days to the date in the LNP Estimate/ Release column. I keep getting one part to work, but not the entire formula. I have this:
=IF([LNP Actual]@row = "", IF([LNP Estimate/ Release]@row = "", [LNP Actual]@row + 2, [LNP Estimate/ Release]@row + 2))
I get a date, but it's not adding at all for LNP Actual having a date. Sheet looks like this:
Please and thank you!
Jen Lawson, PMP
Vernal Biosciences
Colchester, VT 05446

You would use similar logic to the previous one. Instead of trying to nest IFs together, use one IF to grab the appropriate date and then add the appropriate number of days.
=IF(THIS <> "", THIS, that) + 2

It sounds likes that's a cleaner version of what I finally got to work. I used this:
=IF([LNP Estimate/ Release]@row = "", "", IF([LNP Actual]@row = "", [LNP Estimate/ Release]@row + 2, [LNP Actual]@row + 2))
Does mine make sense, and how do you think in the Smartsheet lingo so clearly?!
Jen
Jen Lawson, PMP
Vernal Biosciences
Colchester, VT 05446

Yours does make sense, and I don't see any reason why it shouldn't hold up in the long run.
I have been working 40+ hours / week building various Smartsheet solutions for a little over 5 years now. I feel like if I didn't know a thing or two about SS by now then I am probably in the wrong line of work. Haha. It comes with experience and A LOT of failures along the way.
Help Article Resources
Categories
Check out the Formula Handbook template!