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.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 495 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!