"If" Function - #unparseable and #incorrectargument
I am trying to write a formula to translate a % in one column into a phrase in the next column using the If Function
I have tried multiple ways to get this argument to work -
with this one I get the #unparseable error
=IF([IDD/DMD Process Flow]@row = "0%", "New", IF([IDD/DMD Process Flow]@row = "10%", "IDD Started", IF([IDD/DMD Process Flow]@row = "20%", "As - Is Documentation completed"), IF([IDD/DMD Process Flow]@row = "30%", "IDD Ready for Joint Sessions", IF([IDD/DMD Process Flow]@row = "40%", "Joint Sessions in Progress", IF([IDD/DMD Process Flow]@row = "50%", "IDD Signed Off", IF([IDD/DMD Process Flow]@row = "70%", "Design/Build Started", IF([IDD/DMD Process Flow]@row = "80%", "DBU Complete - Ready to Hand off to QS", IF([IDD/DMD Process Flow]@row = "90%", "QS Hand off in progress", IF([IDD/DMD Process Flow]@row = "100%", "Hand off to QS Complete"))))))))))
with this one I get the #Incorrect Argument Set
=IF([IDD/DMD Process Flow]@row, "0%", "New", IF([IDD/DMD Process Flow]@row, "10%", "IDD Started", IF([IDD/DMD Process Flow]@row, "20%", "As - Is Documentation completed", IF([IDD/DMD Process Flow]@row, "30%", "IDD Ready for Joint Sessions", IF([IDD/DMD Process Flow]@row, "40%", "Joint Sessions in Progress", IF([IDD/DMD Process Flow]@row, "50%", "IDD Signed Off", IF([IDD/DMD Process Flow]@row, "70%", "Design/Build Started", IF([IDD/DMD Process Flow]@row, "80%", "DBU Complete - Ready to Hand off to QS", IF([IDD/DMD Process Flow]@row, "90%", "QS Hand off in progress", IF([IDD/DMD Process Flow]@row, "100%", "Hand off to QS Complete"))))))))))
I'm not sure what I am doing wrong
Answers
-
Hey @NicoleB
Your %Complete column is formatted to show percent, which means the actual values are decimals. Note since the value is a number, and not text, the value is not enclosed with quotes within the formula.
=IF([IDD/DMD Process Flow]@row = 0, "New", IF([IDD/DMD Process Flow]@row = 0.1, "IDD Started", IF([IDD/DMD Process Flow]@row = 0.2, "As - Is Documentation completed"), IF([IDD/DMD Process Flow]@row = 0.3, "IDD Ready for Joint Sessions", IF([IDD/DMD Process Flow]@row = 0.4, "Joint Sessions in Progress", IF([IDD/DMD Process Flow]@row = 0.5, "IDD Signed Off", IF([IDD/DMD Process Flow]@row = 0.7, "Design/Build Started", IF([IDD/DMD Process Flow]@row = 0.8, "DBU Complete - Ready to Hand off to QS", IF([IDD/DMD Process Flow]@row = 0.9, "QS Hand off in progress", IF([IDD/DMD Process Flow]@row = 1, "Hand off to QS Complete")))))))))
Did you notice you are missing a reference to 60% (0.6)? It skips from 50% to 70%. Also, are your %Completes always going to equal values in multiples of tens (eg, nothing like 57%)? If values other than tens are possible then the formula needs to be adjusted for that. Let me know if you need further help.
Kelly
-
@Kelly Moore - the % is a Jira drop down field single select that is being pushed into smartsheet- I tried to see if I could change the properties on the column, but doesn't seem to allow me to change it, probably bc it is published from Jira - I tried the formula with your changes and got the Incorrect Argument Set error
Thinking I could try to pull the % into a new column as a real number and try your formula then?
-
OK - so I created a new column and pulled in the value from the other column and converted it to a number - then I updated the formula to point to the new column and still got the same error.
-
grrr, sorry. I found it. You had an extra closing parenthesis before your 4th IF. Your first formula will probably work since the data is text .
Kelly
-
@Kelly Moore - no worries - it still didn't like that jira published field, so I used the new column I created/converted and fixed my formula (thank you) and it worked! :)
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!