Unparseable Formula

Umesh Shah

I have sheet with a few columns, two of which are:

1. symbol column with the status bar
2. 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?

Umesh Shah

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

Paul Newcome

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")))))

Paul Newcome

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?

Umesh Shah

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

Paul Newcome

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")))))

Umesh Shah

That worked, thank you !!

Paul Newcome

Happy to help. 👍️