IF statement returns #UNPARSEABLE
Having trouble finding the error in my below formula. It is returning #UNPARSEABLE
=IF(([2021 Target]@row- [Start Date]@row)/365*12>=12, IF(SUM([May-21]@row:[Jun-20]@row)/12<0,0, SUM([May-21]@row:[Jun-20]@row)/12*12),IF(SUM([May-21]@row:[Jun-20]@row)/(([2021 Target]@row- [Start Date]@row)/365*12)<0,0,SUM([May-21]@row:[Jun-20]@row)/([2021 Target]@row- [Start Date]@row)/365*12)*12))
Answers
-
It's a little hard not knowing the context of what you're after, but I'll give it a shot.
If you could post a screenshot that'd be helpful. If you're on a Windows machine, Windows Key+Shift+S will allow you to drag over your screen and take a screenshot with your mouse. Then you can paste (CTRL+V) in a comment here.
Questions:
- Is the formula "divide by 365 and multiply by 12" there to get months between dates?
- Example in bold here: IF(([2021 Target]@row- [Start Date]@row)/365*12>=12
- Is Target a Date type column?
I think your issue is that the first "argument" for your very first IF statement is set up like this
=IF(([2021 Target]@row- [Start Date]@row)/365*12>=12, __________________ IF(SUM([May-21]@row:[Jun-20]@row)/12<0,0,SUM([May-21]@row:[Jun-20]@row)/12*12), IF(SUM([May-21]@row:[Jun-20]@row)/(([2021 Target]@row- [Start Date]@row)/365*12)<0,0, SUM([May-21]@row:[Jun-20]@row)/([2021 Target]@row- [Start Date]@row)/365*12)*12))
Basically, what this is saying is:
- IF the 2021 Target (Date?) minus the Start Date divided by 365 and multiplied by 12 is greater than or equal to 12, then: ________________[, otherwise?]
- IF the sum of rows May-21 to Jun-20 divided by 12 are less than 0, show 0, otherwise show the sum of rows May-21 to Jun-20 divided by 12 and multiplied by 12
That big blank spot there is where I would usually expect to see a definitive answer, i.e. "0", or show "", etc.
Hope that helps!
If this answered your question, please press "Yes" below - it helps the community (and Google searchers) find solutions much faster.
If this answer answers your question, please press "Yes" above - it helps the community (and those random Googlers out there 👀) find solutions like yours faster.
Love,
Brett Wyrick | Connect with me on LinkedIn.
------------------------------------------------------------------------------
2023 update: I'm no longer working on Smartsheet stuff. I started working at Microsoft in 2022, plus I have 1-year-old twins at home and frankly, I don't have enough time to do Smartsheet anymore. It's been real, Smartsheeters!
- Is the formula "divide by 365 and multiply by 12" there to get months between dates?
-
I'm not going to try to figure out what you are doing, as I don't have a clue just looking at the formula. I'll just fix/simplify the math, and get rid of formula errors to get you a result. * is my thoughts, # is formula suggestions. I've posted the final at the bottom.
=IF(([2021 Target]@row- [Start Date]@row)/365*12>=12,
*if duration is greater than 1 year, then true, else false. More easily written as such:
#=IF(([2021 Target]@row- [Start Date]@row)/365>=1,
IF(SUM([May-21]@row:[Jun-20]@row)/12<0,
*if sum of values between may and june /12 < 0, then true, else false. Only true when sum is negative, /12 can be taken out
#IF(SUM([May-21]@row:[Jun-20]@row)<0,
0,
SUM([May-21]@row:[Jun-20]@row)/12*12),
*Sum of values between may and june. /12*12 = 1. Can be taken out for same result.
#SUM([May-21]@row:[Jun-20]@row)),
IF(SUM([May-21]@row:[Jun-20]@row)/(([2021 Target]@row- [Start Date]@row)/365*12)<0,
*if sum from may to jun divided by target duration /365*12 < 0 then true, else false.
*only true if one of the sums is negative. There is also a missing parenthesis that is a cause of the
*unparseable. An equal way of writing it would be
#if(SUM([May-21]@row:[Jun-20]@row)/([2021 Target]@row- [Start Date]@row) < 0,
0,
SUM([May-21]@row:[Jun-20]@row)/([2021 Target]@row- [Start Date]@row)/365*12)*12))
*sum may to jun divided by target duration divided by 365*12*12
*extra close parenthesis after first 12 with no open, that wouldn't effect the formula anyway
#SUM([May-21]@row:[Jun-20]@row)/([2021 Target]@row- [Start Date]@row)/365*12*12
=IF([2021 Target]@row- [Start Date]@row)/365>=1,IF(SUM([May-21]@row:[Jun-20]@row)<0,IF(SUM([May-21]@row:[Jun-20]@row)<0,0,SUM([May-21]@row:[Jun-20]@row)),if(SUM([May-21]@row:[Jun-20]@row)/([2021 Target]@row- [Start Date]@row) < 0,0,SUM([May-21]@row:[Jun-20]@row)/([2021 Target]@row- [Start Date]@row)/365*12*12
-
- IF the 2021 Target (Date?) minus the Start Date divided by 365 and multiplied by 12 is greater than or equal to 12, then: _IF true then sum 12 months of revenue _______________[, otherwise?]
- IF the sum of rows May-21 to Jun-20 divided by 12 are less than 0, show 0, otherwise show the sum of rows May-21 to Jun-20 divided by 12 and multiplied by 12
The blank is if true then sum May21@row:Jun20@row; if false then take the average of the months the employee has been with the company. For example, if it is 8 months then sum of revenue divided by 8*12
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!