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
- 64.3K Get Help
- 419 Global Discussions
- 221 Industry Talk
- 461 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 142 Just for fun
- 58 Community Job Board
- 462 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 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!
