Trying to use IF-AND 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 while-didn'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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
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.
-
- 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.
-
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 while-didn't see it. Thanks. It works as I'd hoped.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!