How to troubleshoot the error in a formula? I am receiving #UNPARSEABLE error
I am using a simple if statement but receiving a #UNPARSEABLE error for the formula: =IF(([Proposed 2020 Base Salary]@row[2019 Base Salary]@row)/[2019 Base Salary]@row=100%,"",([Proposed 2020 Base Salary]@row[2019 Base Salary]@row)/[2019 Base Salary]@row)
How can I troubleshoot this error, there is no option to do a stepbystep diagnosis of the formula. The only doubt I have is, is 100% allowed in smartsheets, since you can only make a column type as Number but not really a percentage type.
Best Answer

Cody Holmes ✭✭✭✭
Hi Sonal,
It appears that your suspicion of the 100% is probably correct. You cannot do a mathematical formula where the end result will equal 100% because the percent sign makes the result a string, not a number. Additionally, depending on how the sheet is set up, the number you probably want to be evaluating to is 1 and not 100.
When you format a number in Smartsheet (or really any spreadsheet software), the percent formatting takes the decimal number and displays it in the percentformatting convention. Example:
0.23 is the same as 23%.
I hope that makes sense and helps you resolve your formula. Let me know if I need to expand on anything. If I answered your question, make sure to accept it as a solution so others can find the answer. Give me a vote up too if I was able to help you!
1
Answers
Hi Sonal,
It appears that your suspicion of the 100% is probably correct. You cannot do a mathematical formula where the end result will equal 100% because the percent sign makes the result a string, not a number. Additionally, depending on how the sheet is set up, the number you probably want to be evaluating to is 1 and not 100.
When you format a number in Smartsheet (or really any spreadsheet software), the percent formatting takes the decimal number and displays it in the percentformatting convention. Example:
0.23 is the same as 23%.
I hope that makes sense and helps you resolve your formula. Let me know if I need to expand on anything. If I answered your question, make sure to accept it as a solution so others can find the answer. Give me a vote up too if I was able to help you!
Hi Cody,
Thank you so much for providing the resolution. It worked perfectly for me after i removed the % sign and changed the column to %. I am now comparing the IF logic with 1 and it works!