Rank Dates
Hi, I'm using the Meeting Management template. I'm looking to rank the meetings by age. I tried a few things but haven't had any luck yet. Here's an example of what I'm looking for. The Age Rank is the column I'm looking to add.
Thanks
Best Answer
-
The basic procedure is to first number the parent rows. Then, for the Age Rank in the child rows belonging to those parent rows, use the PARENT() function to bring in the number from the parent row.
Rank the parent rows.
(By relative position)
To rank the parent rows, you need unique values like row numbers. To get row numbers ([Row No]), you can use an auto-numbered Row ID column and the MATCH function.
There is a hidden column called ANCESTORS in the template, where a value of 1 indicates a parent row. By using the COLLECT function to create a range of row number values where ANCESTORS is 1 and then applying the RANKEQ or MATCH function, you can determine the order of the parent rows. By the way, this method is commonly used for WBS (Work Breakdown Structure) numbering.
Creating the Row No with the MATCH function is necessary because this template assumes rows will be moved together under the grandparent row in the archive, which would disrupt the order if you rely solely on the Row ID. (Note: Row ID 11 to 20 has been moved behind Row ID 27 in the image above)
(By Date Value)
If we use the Date Value, we can apply the RANKEQ or MATCH function. Using the Date Value solves the tie situation if you have multiple meetings on the same date. (Note: From Row 11, Paren rank or A1 are 4, 4, 6.)
Formulas
Here are the specific formulas:
[Row No] =MATCH([Row ID]@row, [Row ID]:[Row ID], 0)
[Date Value] = IF(Ancestors@row = 1, YEAR(Date@row) * 365 + MONTH(Date@row) * 30 + DAY(Date@row))
(Note: 2024/01/30 date value < 2025/12/01 where as 2024+1+30 = 2055 > 2025 + 12 + 1 = 2038)
Parent order or rank:
[A1 by RANKEQ] =IF(Ancestors@row = 1, RANKEQ([Row No]@row, COLLECT([Row No]:[Row No], Ancestors:Ancestors, 1), 1))
[A1 by MATCH] =IF(Ancestors@row = 1, MATCH([Row No]@row, COLLECT([Row No]:[Row No], Ancestors:Ancestors, 1), 0))
[A1 by Date V] =IF(Ancestors@row = 1, MATCH([Date Value]@row, COLLECT([Date Value]:[Date Value], Ancestors:Ancestors, 1), 0)) (You can use RANKEQ as well)
You can use RQNKEQ when ordering numbers, whereas MATCH for numeric and non-numeric values, assuming they are all unique.
Age Rank
[Age Rank] =IF(Ancestors@row = 2, PARENT([A1 by RANKEQ]@row), IF(Ancestors@row = 1, [A1 by RANKEQ]@row))
These formulas will help you achieve the desired ranking of meetings by age.
I hope this helps! Let me know if you need any further assistance.
Answers
-
What are your age rankings? Something like this? Once you confirm we can send you a formula using the today() function.
1=today or last 7 days
2=last 8-14 days
3=last 15-21 days
4=22 days or older
-
1 is most recent.
2 is second most recent
so on and so on.
-
You need a helper column to convert the date to a number, then you can use RANKAVG to rank it.
For the Helper Column (let's call it Date Value):
=YEAR(Date@row) + MONTH(Date@row) + DAY(Date@row)
Then for the Rank column:
=RANKAVG([Date Value]@row, [Date Value]:[Date Value])
-
Is there a way to not have ties increase the rank numbers? Here's the current result. Added another column to demonstrate the end result I'm looking for.
-
Yes if you add a checkbox to indicate what row is a Tier, let's call it "Tier?", then adjust your formula to be
=IFERROR(RANKAVG([Date Value]@row, COLLECT([Date Value]:[Date Value],[Tier?]:[Tier?],false)),"")
This formula:
- Collects all the Date Values where the "Tier?" checkbox is not checked.
- Ranks the current row against that collection of "non-tier" Date Values
- Puts a blank in the Rank column instead of an error for Tier rows
Also, if you don't want the .5 part then wrap everything in a ROUNDDOWN like this:
=IFERROR(ROUNDDOWN(RANKAVG([Date Value]@row, COLLECT([Date Value]:[Date Value],[Tier?]:[Tier?],false))),"")
-
No matter how the boxes are check, it's still not ranking sequentially without skipping numbers.
-
The basic procedure is to first number the parent rows. Then, for the Age Rank in the child rows belonging to those parent rows, use the PARENT() function to bring in the number from the parent row.
Rank the parent rows.
(By relative position)
To rank the parent rows, you need unique values like row numbers. To get row numbers ([Row No]), you can use an auto-numbered Row ID column and the MATCH function.
There is a hidden column called ANCESTORS in the template, where a value of 1 indicates a parent row. By using the COLLECT function to create a range of row number values where ANCESTORS is 1 and then applying the RANKEQ or MATCH function, you can determine the order of the parent rows. By the way, this method is commonly used for WBS (Work Breakdown Structure) numbering.
Creating the Row No with the MATCH function is necessary because this template assumes rows will be moved together under the grandparent row in the archive, which would disrupt the order if you rely solely on the Row ID. (Note: Row ID 11 to 20 has been moved behind Row ID 27 in the image above)
(By Date Value)
If we use the Date Value, we can apply the RANKEQ or MATCH function. Using the Date Value solves the tie situation if you have multiple meetings on the same date. (Note: From Row 11, Paren rank or A1 are 4, 4, 6.)
Formulas
Here are the specific formulas:
[Row No] =MATCH([Row ID]@row, [Row ID]:[Row ID], 0)
[Date Value] = IF(Ancestors@row = 1, YEAR(Date@row) * 365 + MONTH(Date@row) * 30 + DAY(Date@row))
(Note: 2024/01/30 date value < 2025/12/01 where as 2024+1+30 = 2055 > 2025 + 12 + 1 = 2038)
Parent order or rank:
[A1 by RANKEQ] =IF(Ancestors@row = 1, RANKEQ([Row No]@row, COLLECT([Row No]:[Row No], Ancestors:Ancestors, 1), 1))
[A1 by MATCH] =IF(Ancestors@row = 1, MATCH([Row No]@row, COLLECT([Row No]:[Row No], Ancestors:Ancestors, 1), 0))
[A1 by Date V] =IF(Ancestors@row = 1, MATCH([Date Value]@row, COLLECT([Date Value]:[Date Value], Ancestors:Ancestors, 1), 0)) (You can use RANKEQ as well)
You can use RQNKEQ when ordering numbers, whereas MATCH for numeric and non-numeric values, assuming they are all unique.
Age Rank
[Age Rank] =IF(Ancestors@row = 2, PARENT([A1 by RANKEQ]@row), IF(Ancestors@row = 1, [A1 by RANKEQ]@row))
These formulas will help you achieve the desired ranking of meetings by age.
I hope this helps! Let me know if you need any further assistance.
-
Worked perfect, both methods achieved the outcome I was looking for. Thanks
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.3K Get Help
- 364 Global Discussions
- 199 Industry Talk
- 428 Announcements
- 4.4K Ideas & Feature Requests
- 136 Brandfolder
- 127 Just for fun
- 128 Community Job Board
- 445 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 283 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!