Formula showing #invalid operation after changing a simple logic

Michaela Kamenska
Michaela Kamenska ✭✭✭✭✭✭
edited 03/24/22 in Formulas and Functions

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?


Tags:

Answers

  • Jeff Reisman
    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 (" ").

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • Michaela Kamenska
    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
    Jeff Reisman ✭✭✭✭✭✭
    edited 03/25/22

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

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!