If(and( question

courtj

hi,

i'm trying to get a formula to work but there's a few different factors that determine which value should be returned.

4- development tiers

5- stage gates

an auto date based on when stage gate changes

right now i have this that is returning back "unparseable"

=IF(AND([Development Tier]@row = "Version Change/Iterative Product Update", [Stage Gate]@row = "stage gate 2"), [Stage Date Date]@row + 110, IF(AND([Development Tier]@row = "New Product Category"), [Stage Date Date]@row + 110, IF(AND([Development Tier]@row = "Packaging Extension"), [Stage Date Date]@row + 80, IF(AND([Development Tier]@row = "Flavor Extension"), [Stage Date Date]@row + 85, “”)))))))

so basically if it's one of the 4 development tiers and in "stage gate 2" it'll return a date that's based on "stage date date" + x amount of days.

I was able to get this formula to work but i need to get it to work with all the factors because ideally i can do each dev tier in each stage gate to return a date

=IF(AND([Development Tier]@row = "flavor extension", [Stage Gate]@row = "stage gate 2"), [Stage Date Date]@row + 75, IF(AND([Development Tier]@row = "packaging extension", [Stage Date Date]@row + 65)))

Help 😊

Nic Larsen

You have AND statements after the 1st one that are not 2 items to test before the result. Try:

=IF(AND([Development Tier]@row = "Version Change/Iterative Product Update", [Stage Gate]@row = "stage gate 2"), [Stage Date Date]@row + 110, IF(AND([Development Tier]@row = "New Product Category", [Stage Gate]@row = "stage gate 2"), [Stage Date Date]@row + 110, IF(AND([Development Tier]@row = "Packaging Extension", [Stage Gate]@row = "stage gate 2"), [Stage Date Date]@row + 80, IF(AND([Development Tier]@row = "Flavor Extension",[Stage Gate]@row = "stage gate 2"), [Stage Date Date]@row + 85, “”)

Dan Beres

Hello @courtj,

My guess is the unparseable error is coming from one or both of these things:
- You are adding within the IF statements
- The column type you are in does not match the date column (the IF statement must also be a date column for the addition to calculate for another date column. Try =[Stage Date Date]@row + 10 and see if you get INVALID COLUMN VALUE where you want the formula).

A way around this would be to do the following:

=[Stage Date Date]@row +
IF([Stage Gate]@row = "stage gate 1",
IF([Development Tier]@row = "Version Change/Iterative Product Update", <Days>,
IF([Development Tier]@row = "New Product Category", <Days>,
IF([Development Tier]@row = "Packaging Extension", <Days>,
IF([Development Tier]@row = "Flavor Extension", <Days>, "")))),

IF([Stage Gate]@row = "stage gate 2",
IF([Development Tier]@row = "Version Change/Iterative Product Update", <Days>,
IF([Development Tier]@row = "New Product Category", <Days>,
IF([Development Tier]@row = "Packaging Extension", <Days>,
IF([Development Tier]@row = "Flavor Extension", <Days>, ""))))))
…
and continue for as many stage gates you want to track. You can paste as is and it will auto-format so just replace the number of days you want for each and adjust the ending parentheses as needed.

Please also check spelling since anything in quotations is case-sensitive, as well as the "Stage Date Date" column having 2 "Dates" in it. The two formulas you posted have different spellings of packaging extension and flavor extension due to the capitalization. This usually wouldn't cause an unparseable error and would usually just send it to the last part of the nested IFs.

courtj

I ended up doing a matrix for the different times on a reference sheet and then index matched, ended up with a much shorter formula and can adjust the matrix easier than going back into the formula.

Thank you for the recommendations!