Unparseable Formula
I have sheet with a few columns, two of which are:
- symbol column with the status bar
- a % complete field
I want a formula in the status bar which auto picks (i.e. column formula) based on the % in the second column.
so i have this formula in column 1:
=IF([% Complete]@row <>" ", if([% Complete]@row=0, "Empty", if(and([% Complete]@row >0, [% Complete]@row <= .25), "Quarter", if(and([% Complete]@row >.25, [% Complete]@row <=.5), "Half", if(and([% Complete]@row >.5, [% Complete]@row <=.75), "Three Quarter"', if([% Complete]@row=1, "Full"))))))
if the % Complete column is blank, then i don't want it to evaluate the formula, hence the first IF.
I get an error saying this formula is unpareseable.
Anyone know why?
Best Answers
-
yes, this a copy/paste from the sheet.
good catch, i modified the formula to be:
=IF([% Complete]@row <>" ", if([% Complete]@row=0, "Empty", if(and([% Complete]@row >0, [% Complete]@row <= .25), "Quarter", if(and([% Complete]@row >.25, [% Complete]@row <=.5), "Half", if(and([% Complete]@row >.5, [% Complete]@row <=.99), "Three Quarter"', if([% Complete]@row=1, "Full"))))))
thanks, still the same error though
-
Lets try this...
Here is a simplified version of your formula that will work the same way:
=IF([% Complete]@row = "", "", IF([% Complete]@row = 1, "Full", IF([% Complete]@row > 0.5, "Three Quarter", IF([% Complete]@row > 0.25, "Half", IF([% Complete]@row > 0, "Quarter", "Empty")))))
But depending on your region you may need to swap out commas for semicolons and periods for commas (I think this is going to be the issue even with your existing formula):
=IF([% Complete]@row = ""; ""; IF([% Complete]@row = 1; "Full"; IF([% Complete]@row > 0,5; "Three Quarter"; IF([% Complete]@row > 0,25; "Half"; IF([% Complete]@row > 0; "Quarter"; "Empty")))))
Answers
-
I don't see any issues with it as it is in your post. Did you copy/paste that directly from your sheet?
We can actually simplify it a bit, but I do have one question... The way it is currently written, if the [% Complete] is between 75% and 99%, then the formula will output a blank. What would you want for that percentage range?
-
yes, this a copy/paste from the sheet.
good catch, i modified the formula to be:
=IF([% Complete]@row <>" ", if([% Complete]@row=0, "Empty", if(and([% Complete]@row >0, [% Complete]@row <= .25), "Quarter", if(and([% Complete]@row >.25, [% Complete]@row <=.5), "Half", if(and([% Complete]@row >.5, [% Complete]@row <=.99), "Three Quarter"', if([% Complete]@row=1, "Full"))))))
thanks, still the same error though
-
Lets try this...
Here is a simplified version of your formula that will work the same way:
=IF([% Complete]@row = "", "", IF([% Complete]@row = 1, "Full", IF([% Complete]@row > 0.5, "Three Quarter", IF([% Complete]@row > 0.25, "Half", IF([% Complete]@row > 0, "Quarter", "Empty")))))
But depending on your region you may need to swap out commas for semicolons and periods for commas (I think this is going to be the issue even with your existing formula):
=IF([% Complete]@row = ""; ""; IF([% Complete]@row = 1; "Full"; IF([% Complete]@row > 0,5; "Three Quarter"; IF([% Complete]@row > 0,25; "Half"; IF([% Complete]@row > 0; "Quarter"; "Empty")))))
-
That worked, thank you !!
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 430 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!