Formula showing #invalid operation after changing a simple logic

Michaela Kamenska

Hi all,

it's me again! So I've got this formula that breaks down hours by duration into relevant months and somehow I can't get the syntax right.

What I'm trying to say is that if Global lead at row = "EU" , use lead startup and "Duration (months)", if global lead at row <> "EU" and Regional startup date is not blank, use regional startup date and "Duration (months) - regional", else just use lead startup and "duration (months)"... But I'm getting unparseable on it.

=IF(AND([Global Lead]@row = "EU", ([Jan-21]$1 >= YEAR($[Lead Start-Up]@row) + RIGHT("00" + MONTH($[Lead Start-Up]@row), 2)), ([Jan-21]$1 <= YEAR($[End Date (CSR)]@row) + RIGHT("00" + MONTH($[End Date (CSR)]@row), 2))), $[Total Budgeted incl MDR Oversight]@row / $[Duration (months)]@row), IF(AND(NOT(ISBLANK([Regional Start-Up]@row)), [Global Lead]@row <> "EU", ([Jan-21]$1 >= YEAR($[Regional Start-Up]@row) + RIGHT("00" + MONTH($[Regional Start-Up]@row), 2)), ([Jan-21]$1 <= YEAR($[End Date (CSR)]@row) + RIGHT("00" + MONTH($[End Date (CSR)]@row), 2))), $[Total Budgeted incl MDR Oversight]@row / $[Duration (months) - regional]@row, ([Jan-21]$1 >= YEAR($[Lead Start-Up]@row) + RIGHT("00" + MONTH($[Lead Start-Up]@row), 2)), ([Jan-21]$1 <= YEAR($[End Date (CSR)]@row) + RIGHT("00" + MONTH($[End Date (CSR)]@row), 2))), $[Total Budgeted incl MDR Oversight]@row / $[Duration (months)]@row)))

Anyone can help, please?

Jeff Reisman

@Michaela Kamenska

I think I found your issue. You're ending your IF before the nested IF starts.

The clue is almost always in the error message.

#UNPARSEABLE

Cause

The formula has a problem which prevents it from being parsed and interpreted. This can happen for many reasons, such as misspelling, incomplete operators, using the wrong case for a column name, or using single quotes instead of double quotes.

Resolution

Ensure that all column names are spelled correctly in cell references, operators are being used correctly, and any text strings in the formula are surrounded by double quotes (" ").

Michaela Kamenska

@Jeff Reisman ,

Thank you. I've tried that but it still doesn't want to go. I think I may have another typo or two there and also, I seem to have cut off the formula in my previous post. Here's the new snapshot. I definitely don't have a keen eye for these, especially with so many parentheses. 😂 Everytime I start checking which parentheses are closed and which belong to what, I get confused and rarely can actually find it.

Jeff Reisman

@Michaela Kamenska

Two rules for Smartsheet formulas that are generally true:

The color of the first opening parentheses should match the color of the last end parentheses in a formula.

For IFs with embedded IFs: The number of end parentheses at the end should be equal to the number of times the word "IF" appears in the formula.

Here, your end blue parentheses is not the last end parentheses in the formula, and it should be. Plus, there's only one end parentheses at the very end, and there should be two since you have two IFs. So you need to delete that ending blue parentheses and add one to the very end. Give it a try.

Another handy method for troubleshooting IFs is to break them apart into their logical statements and test each one in a test column to see if they evaluate correctly. Slowly add pieces back until you have one whole working IF, then do the same for the nested IF.

Also, make sure your cell references aren't referencing any cells that are in error, otherwise it can break the whole thing.