Rankeq
Hello Smartsheet Family:
I am new to Smartsheet and I have an ask from my manager using the Rankeq formula.
I have a set of data where I need to use the Rankeq formula to get the top 5 deals for the current month.
I have a field that uses obtains the month by doing "=month" using the create date, however there is another condition it has to meet. There is a separate column called report period which has "Current Month" and "Next Qtr"
So in short I am trying to figure out how I can use the Rankeq formula to get two results.
- Find the top five deals when the month is May, and the report period is "Current Month"
- Find the top five deals when the month is May, and the report period is "Next Qtr"
Sincerely
New User to Smartsheet Community
Regards
Balaji
Answers
-
Someone might come up with something a little better than mine, but below is what my solution would be.
Equation for first cell in return: (Deal column is value of return, you can swap large for small if you want the bottom values of the range)
=LARGE(COLLECT(Deal:Deal, Month:Month, 5, [Report Period]:[Report Period], @cell = "Current Month"), 1)
Drag down equation for rest of numbers
=LARGE(COLLECT(Deal:Deal, Month:Month, 5, [Report Period]:[Report Period], @cell = "Current Month"), COUNT([Form1]$1:[Form1]1) + 1)
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 221 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!