Nested IF troubles

Jen Lawson

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!

Paul Newcome

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))

Hollie Green

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

Jen Lawson

=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

https://community.smartsheet.com/discussion/comment/377175#Comment_377175

Can anyone help with an updated formula assist? Please and thank you! :)

Paul Newcome

Try this:

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

Jen Lawson

Mostly it works, except now, for blanks I have "7". I assume I need to add an IFERROR??

Paul Newcome

Blanks in which column exactly?

Jen Lawson

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.

Paul Newcome

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))

Jen Lawson

@Paul Newcome you are my Smartsheet hero!

Jen Lawson

@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!

Paul Newcome

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

Jen Lawson

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

Paul Newcome

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.