Formula showing #invalid operation after changing a simple logic
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?
Answers
-
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!
-
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.
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 438 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 451 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 282 Events
- 32 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!