Forum Discussion
Ranking Names in Excel
Hi all,
Looking for a way to attach a ranking to a name based on birthday. For example, if PersonA was born 1/1/1991 and PersonB was born 1/2/1991 then PersonA would rank 1 and PersonB would rank 2.
However, I also want to be able to remove someone from the list and have their rank replaced. So, if PersonA was removed, then PersonB would automatically move from rank 2 to rank 1.
Currently, I am using the =MAX(A$2:A2)+1 function where the ranking cell starts in A3 but I can't figure out how to "tie" the rank to the rest of the information in the filtered table. Right now, when I sort the table the rank does not tie in despite being part of the table filter
4 Replies
List of Names and Birthdays:
- In column A, list the names of people.
- In column B, list their corresponding birthdays.
Add Dynamic Ranking:
- In column C, you can add a ranking formula that dynamically ranks the sorted names based on birthdays. Use the RANK function:This ranks the birthdays in ascending order (1 for the oldest), assuming your data goes from row 2 to row 100. Adjust the range according to your actual data.excel=RANK(B2, B$2:B$100, 1)
Example:
Name Birthday Rank
PersonA 1/1/1991 1 PersonB 1/2/1991 2 PersonC 1/3/1991 3 Let's say the dates of birth are in A2:A100.
In the rank column, enter the following formula in row 2:
=RANK.EQ(A2:A100, A2:A100, 1)
HansVogelaar is there a way to do this with no duplicates? For example if two people had the same birthday, they are not both ranked #5
How would you like the ties handled?
I see two approaches: HansVogelaar 's solution with RANK.EQ or using XMATCH.
=XMATCH([@DOB],SORT(UNIQUE([DOB])))It really depends on how you'd like the duplicates (and birthdays after the dupes) handled:
