"If" Function - #unparseable and #incorrectargument

NicoleB

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

Kelly Moore

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

NicoleB

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

NicoleB

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.

Kelly Moore

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

NicoleB

@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! :)