Unparseable Formula

Options
✭✭✭✭

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?

• ✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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?

• ✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭
Options

That worked, thank you !!

• ✭✭✭✭✭✭
Options

Happy to help. 👍️

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!