Trying to use IFAND but keep getting #UNPARSEABLE
Best Answers

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

Stared at this guy for a whiledidn't see it. Thanks. It works as I'd hoped.
Answers

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

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 nogo.
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 017;E2, 1831;E1, 3255;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.

 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 017;E2, 1831;E1, 3255;D and so on and "BadValue" is there just in case.

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

Stared at this guy for a whiledidn't see it. Thanks. It works as I'd hoped.
Help Article Resources
Categories
Check out the Formula Handbook template!