Trying to use IF-AND but keep getting #UNPARSEABLE

BarryO

markkrebs

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

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

Genevieve P.

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

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

• 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

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

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