How to troubleshoot the error in a formula? I am receiving #UNPARSEABLE error

Sonal Dubey

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 step-by-step 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.

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 percent-formatting 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!

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 percent-formatting 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!

Sonal Dubey

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!