Trying to use IF-AND but keep getting #UNPARSEABLE

Options

Best Answers

  • markkrebs
    markkrebs ✭✭✭✭✭✭
    Answer ✓
    Options

    you are missing a comma here: "B2" IF

    need "B2",IF

    =IF(AND([Area11]@row <= 0, [Area11]@row <= 17), "E2", IF(AND([Area11]@row > 17, [Area11]@row <= 31), "E1", IF(AND([Area11]@row > 31, [Area11]@row <= 55), "D", IF(AND([Area11]@row > 55, [Area11]@row <= 73), "C", IF(AND([Area11]@row > 73, [Area11]@row <= 91), "B2", IF(AND([Area11]@row > 91, [Area11]@row <= 105), "B1"))))))

  • BarryO
    BarryO
    Answer ✓
    Options

    Stared at this guy for a while-didn't see it. Thanks. It works as I'd hoped.

Answers

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Hi @BarryO

    The Community would be happy to help, however we need more information. Would you be able to provide the following:

    • Copy/Paste the current formula you're using
    • Screen capture of the sheet you're putting it in, showing column names (blocking out sensitive data)
    • Explanation of column types you're referencing / using
    • Bullet point list of what you want the formula to acheive

    Thank you!

    Genevieve

  • BarryO
    Options

    Area11 is a Text/Number column type holding values from 000 to 105 which were directly entered into the sheet. Area1 (used initially in the statement below) values are derived thus: =VALUE(RIGHT([Room Number]@row, 3)) and I was thinking Area1 returned values were giving the #UNPARSEABLE error so I hand entered values into Area11. Still no-go.

    I used <=0 as a sort of error handling as any report I generate having negative numbers will stick out like a sore thumb and I'll need to sus that out if it happens. It shouldn't, BUT-

    The goal is to assign an Area based on the room numbers 0-17;E2, 18-31;E1, 32-55;D and so on and "BadValue" is there just in case.

    =IF( AND( [Area11]@row <=0, [Area11]@row <=17), "E2", IF( AND( [Area11]@row >17, [Area11]@row <=31), "E1", IF( AND( [Area11]@row >31, [Area11]@row <=55), "D", IF( AND( [Area11]@row >55, [Area11]@row <=73), "C", IF( AND( [Area11]@row >73, [Area11]@row <=91), "B2" IF( AND( [Area11]@row >91, [Area11]@row <=105), "B1", "BadValue" ))))))


    Any help will be appreciated.

  • BarryO
    Options
    • Copy/Paste the current formula you're using
    • =IF( AND( [Area11]@row <=0, [Area11]@row <=17), "E2", IF( AND( [Area11]@row >17, [Area11]@row <=31), "E1", IF( AND( [Area11]@row >31, [Area11]@row <=55), "D", IF( AND( [Area11]@row >55, [Area11]@row <=73), "C", IF( AND( [Area11]@row >73, [Area11]@row <=91), "B2" IF( AND( [Area11]@row >91, [Area11]@row <=105), "B1", "BadValue" ))))))
    • Screen capture of the sheet you're putting it in, showing column names (blocking out sensitive data)
    • >Hopefully attached
    • Explanation of column types you're referencing / using
    • Area11 is a Text/Number column type same for the cell which I'm inserting the statement
    • Bullet point list of what you want the formula to achieve
    • The goal is to assign an Area based on the room numbers 0-17;E2, 18-31;E1, 32-55;D and so on and "BadValue" is there just in case.


  • markkrebs
    markkrebs ✭✭✭✭✭✭
    Answer ✓
    Options

    you are missing a comma here: "B2" IF

    need "B2",IF

    =IF(AND([Area11]@row <= 0, [Area11]@row <= 17), "E2", IF(AND([Area11]@row > 17, [Area11]@row <= 31), "E1", IF(AND([Area11]@row > 31, [Area11]@row <= 55), "D", IF(AND([Area11]@row > 55, [Area11]@row <= 73), "C", IF(AND([Area11]@row > 73, [Area11]@row <= 91), "B2", IF(AND([Area11]@row > 91, [Area11]@row <= 105), "B1"))))))

  • BarryO
    BarryO
    Answer ✓
    Options

    Stared at this guy for a while-didn't see it. Thanks. It works as I'd hoped.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!