Changing a simple Excel formula to work for Smartsheet.

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?

Tags:

Best Answer

  • Sam Knight
    Sam Knight ✭✭
    Answer ✓

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

Answers

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

  • tgattsh
    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
    Sam Knight ✭✭
    Answer ✓

    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
    tgattsh ✭✭✭✭

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!