Rank Dates

smarterwithsmart

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

jmyzk_cloudsmart_jp

Hi @smarterwithsmart

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.

https://app.smartsheet.com/b/publish?EQBCT=5285fc2af45c45e4a5330cd071a0a11f

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.

Julie Fortney

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

smarterwithsmart

https://community.smartsheet.com/discussion/comment/443938#Comment_443938

1 is most recent.

2 is second most recent

so on and so on.

Brian_Richardson

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])

smarterwithsmart

https://community.smartsheet.com/discussion/comment/443949#Comment_443949

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.

Brian_Richardson

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:

1. Collects all the Date Values where the "Tier?" checkbox is not checked.
2. Ranks the current row against that collection of "non-tier" Date Values
3. 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))),"")

smarterwithsmart

https://community.smartsheet.com/discussion/comment/443967#Comment_443967

No matter how the boxes are check, it's still not ranking sequentially without skipping numbers.

jmyzk_cloudsmart_jp

Hi @smarterwithsmart

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.

https://app.smartsheet.com/b/publish?EQBCT=5285fc2af45c45e4a5330cd071a0a11f

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.

smarterwithsmart

https://community.smartsheet.com/discussion/comment/443987#Comment_443987

Worked perfect, both methods achieved the outcome I was looking for. Thanks