IF AND formula returning UNPARSEABLE

System

This discussion was created from comments split from: Using IF(AND Formula.

Rob_UK

Good morning

I have a similar situation and have tried teh above but I see the annoying #UNPARSEABLE

Mine is:-

Proc_Supplier	Fail_Type	Repair Cost
Sup1	Fault1	100
Sup2	Fault1	100
Sup1	Fault2	100
Sup1	Fault3	0

=IF(AND([Proc_Supplier]@row = " Sup1 ", [Fail_Type]@row = "Fault1"), "100", IF(AND([Proc_Supplier]@row = "Sup2 ", [Fail_Type]@row = "Fault1"), "100", “0”)

I have just started with the first two with the same fault type

Any suggestions as to where I have gone wrong?

Kind Regards

Rob

Paul Newcome

See how the quotes around the zero at the end are slanted and the rest are straight up and down? The slanted ones are called "Smart Quotes" and are (ironically enough) not recognized as valid characters in Smartsheet formulas. Try retyping them directly in Smartsheet and see if that clears up the issue.

Also note… If you are planning on using this output as a number later on such as summing or comparing as less than or greater than, you are going to want to remove the quotes from around the output numbers altogether. Quotes around numbers will output a text string that just looks like a number.

Marcela

Hello @Rob_UK Just tagging you here to make sure you see the response above

Rob_UK

Good morning and thank you for your suggestions, it is appreciated

This morning I have replaced all the quotes in Smartsheet's directly to see if it was a quotation mark issue as above

0 only - No change

All numbers - No change

All areas which have quotes - No change

The Smart quotes may have just been because of a copy/ past issue unfortunately

I have also removed all teh quotes from teh numbers (as suggested making sure its a number not a text for future analysis). This also made no difference to the error message

Kind Regards

AdamSYNH

Hi @Rob_UK,

Your operators are correct and complete. When you edit the formula, are you getting the color coding for your cell references? Sometimes the #UNPARSEABLE error is down to misspellings in either your formula or column headers. I've included two screenshots below, the bottom one results in #UNPARSEABLE because of the extra space (red arrow).

A note on your Repair Cost column, if you want to format this column with a currency, best to remove the double quotes around the numbers in your formula so that they are not added as text.

Assuming the table data and formula in your question match your sheet exactly, when you resolve the #UNPARSEABLE error your formula will result in 0 for Proc_Supplier = " Sup1 " because of the extra space either side of the text (" Sup1 " vs "Sup1")

Paul Newcome

Can you provide a screenshot of the formula open in the sheet as if you are about to edit it?