Changing a simple Excel formula to work for Smartsheet.

tgattsh

I imported and Excel spreadsheet to Smartsheet and selected to keep the Excel formulas. How ever I get an #UNPARSEABLE. Here is what the formula looks like,

=IF(OR([CITY METERSIZE]11="5/8"" BY 3/4""",[CITY METERSIZE]11="5/8""",[CITY METERSIZE]11="3/4""",[CITY METERSIZE]11="1"""),"Small",IF(OR([CITY METERSIZE]11="1 1/2"" Flange",[CITY METERSIZE]11="1 1/2"" Spud",[CITY METERSIZE]11="1 1/2""",[CITY METERSIZE]11="2"""),"Intermediate",IF(OR([CITY METERSIZE]11="3""",[CITY METERSIZE]11="4""",[CITY METERSIZE]11="6""",[CITY METERSIZE]11="8""",[CITY METERSIZE]11="10""",[CITY METERSIZE]11="12""",[CITY METERSIZE]11="16"),"Large","")))

Is there too many IF statements being used?

Sam Knight

The issue is the " inch symbol used in METERSIZE. Can you add a helper column to your sheet that removes the symbol from the value in METERSIZE and then search the helper column.

In your helper column enter the formula

=SUBSTITUTE([CITY METERSIZE]@row, CHAR(34), "")

this will create a value in the helper column that does not have the " symbol.

your original formula would then be revised to read

=IF(OR([HELPER COLUMN NAME]11="5/8 BY 3/4",[HELPER COLUMN NAME]11="5/8",[HELPER COLUMN NAME]11="3/4",[HELPER COLUMN NAME]11="1"),"Small",IF(OR([HELPER COLUMN NAME]11="1 1/2 Flange",[HELPER COLUMN NAME]11="1 1/2 Spud",[HELPER COLUMN NAME]11="1 1/2",[HELPER COLUMN NAME]11="2"),"Intermediate",IF(OR([HELPER COLUMN NAME]11="3",[HELPER COLUMN NAME]11="4",[HELPER COLUMN NAME]11="6",[HELPER COLUMN NAME]11="8",[HELPER COLUMN NAME]11="10",[HELPER COLUMN NAME]11="12",[HELPER COLUMN NAME]11="16"),"Large","")))

Sam Knight

You have an error with your double quotes after the 1st dimension. Some have there """ others only have 2 ""

tgattsh

@Sam Knight The formula works in Excel. The City METERSIZE column shows

5/8"

5/8" BY 3/4"

1"

1 1/2" Flange

And so on.

I've tried removing "

Any suggestions would be appreciated. Thanks for taking a look...

Sam Knight

The issue is the " inch symbol used in METERSIZE. Can you add a helper column to your sheet that removes the symbol from the value in METERSIZE and then search the helper column.

In your helper column enter the formula

=SUBSTITUTE([CITY METERSIZE]@row, CHAR(34), "")

this will create a value in the helper column that does not have the " symbol.

your original formula would then be revised to read

=IF(OR([HELPER COLUMN NAME]11="5/8 BY 3/4",[HELPER COLUMN NAME]11="5/8",[HELPER COLUMN NAME]11="3/4",[HELPER COLUMN NAME]11="1"),"Small",IF(OR([HELPER COLUMN NAME]11="1 1/2 Flange",[HELPER COLUMN NAME]11="1 1/2 Spud",[HELPER COLUMN NAME]11="1 1/2",[HELPER COLUMN NAME]11="2"),"Intermediate",IF(OR([HELPER COLUMN NAME]11="3",[HELPER COLUMN NAME]11="4",[HELPER COLUMN NAME]11="6",[HELPER COLUMN NAME]11="8",[HELPER COLUMN NAME]11="10",[HELPER COLUMN NAME]11="12",[HELPER COLUMN NAME]11="16"),"Large","")))

tgattsh

@Sam Knight You just moved into "HERO" status! THANK YOU!