How to get smallest, 2nd smallest value and 3rd smallest value
Hello,
I need to run a formula which return smallest, 2nd and 3rd smallest value for a criteria. Example like below, when I key in "Banana" in cell D1, the result in F2 to F4 will give me 100, 150 and 200. This formula does not work because Smartsheet do not support array formula. Anyone have alternative way to make it happen?
Best Answer
-
=small(collect(qty:qty,products:products,"Banana"),1)
=small(collect(qty:qty,products:products,"Banana"),2)
=small(collect(qty:qty,products:products,"Banana"),3)
Answers
-
=small(collect(qty:qty,products:products,"Banana"),1)
=small(collect(qty:qty,products:products,"Banana"),2)
=small(collect(qty:qty,products:products,"Banana"),3)
-
Hi, it works. Thanks so much for your valuable answer.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Want to practice working with formulas directly in Smartsheet?
Check out the Formula Handbook template!
Check out the Formula Handbook template!