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 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.
Best Answer
-
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!
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 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!
-
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!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!