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
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
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
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 438 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 451 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 283 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!