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
 10.8K Get Help
 65 Global Discussions
 69 Industry Talk
 385 Announcements
 3.6K Ideas & Feature Requests
 56 Brandfolder
 125 Just for fun
 50 Community Job Board
 464 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!