If between 2 numbers, then
![Angie Hatfield](https://us.v-cdn.net/6031209/uploads/drupal_profile/files/pictures/63/6f/n636f71d5d8910027e5a0ff6225156cdb.jpg)
I'm sure there's a simple way to do this, but I can't figure it out without going through each number, 1 by 1. I have 48 numbers, so it would be lengthy.
If cell = the below, return the size. It is a set cell for the number and a set cell for the formula.
1 = XS
2-6 = S
7-10 = M
11-24 = L
25-48 = XL
>48 = XXL
I greatly appreciate this community. Thank you!🙂
Best Answer
-
Try
=IF(cell@row > 48, "XXL", IF(cell@row > 25, "XL", IF(cell@row > 11, "L", IF(cell@row > 7, "M", IF(cell@row > 1, "S", "XS")))))
What this does is it first checks for if it is 49 or higher, then goes down the list. then if none of the above is true return "XS".
This will work how ever it could provide an issue with if you have a 0. This will also return a "XS". if this is a non issue the first formula would work. IF so use the second formula. In either case the second formula would work for that you provided above.
=IF(cell@row > 48, "XXL", IF(cell@row > 25, "XL", IF(cell@row > 11, "L", IF(cell@row > 7, "M", IF(cell@row > 1, "S", IF(cell@row = 1, "XS"))))))
The reason this works is because of how IF statements works. IT will look for the FIRST statement that is true and return that. IT would also work in the other direction if you changed the > to <.
If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.
Answers
-
Embedding a bunch of IF statements would work fine.
IF(Number@row = 1, "XS",IF(AND(Number@row>1,Number@row<6),"S",IF(… etc etc
Let me know if this works.
-
Try
=IF(cell@row > 48, "XXL", IF(cell@row > 25, "XL", IF(cell@row > 11, "L", IF(cell@row > 7, "M", IF(cell@row > 1, "S", "XS")))))
What this does is it first checks for if it is 49 or higher, then goes down the list. then if none of the above is true return "XS".
This will work how ever it could provide an issue with if you have a 0. This will also return a "XS". if this is a non issue the first formula would work. IF so use the second formula. In either case the second formula would work for that you provided above.
=IF(cell@row > 48, "XXL", IF(cell@row > 25, "XL", IF(cell@row > 11, "L", IF(cell@row > 7, "M", IF(cell@row > 1, "S", IF(cell@row = 1, "XS"))))))
The reason this works is because of how IF statements works. IT will look for the FIRST statement that is true and return that. IT would also work in the other direction if you changed the > to <.
If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.
-
@Mark.poole Thank you. The second one worked perfect and I add " " to the end so if blank it stays blank. I had tried a version of that but it wasn't quite the same and wouldn't work. This is perfect. Thanks so much for your time and knowledge!👍️😊👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 352 Global Discussions
- 198 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 135 Brandfolder
- 127 Just for fun
- 128 Community Job Board
- 443 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 284 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!