Share via

Rank against each name when a particular date is selected (dynamic) in Excel.

Anonymous
2024-04-30T21:35:40+00:00

Hi, I am trying to find out rank against each name when a particular date is selected (dynamic) in Excel.

Please find the sample data set below:

Data range: A1:F6 (including headers)

Dynamic date cell : G1

Ranks to be in column H(H2:H6)

=RANK.EQ(INDEX($B$2:$F$6,MATCH(A2,$A$2:$A$6,0),MATCH($G$1,$B$1:$F$1,0)),INDEX($B$2:$F$6,MATCH($G$1,$B$1:$F$1,0)),0)

  1. Tried the above formula but didnt really help me out as I am looking to rank without ties, for example: consider date 01/04/2023, the ranking i wanted to be as Asa: 4, Sab:2, Fus:3, Kus:1, Kuy:5, so this way it should change whenever date is changed in cell G1.
  2. The same way, I also wanted to have a rolling 3 months Rank with change in date(Dynamic ): So that it should be sum of values for these dates 01/04/2023(selected date in G1), 01/03/2023 and 01/02/2023 and rank them(for Asa it will be sum of 10+10+6=26, in this case Asa will be ranked as 1 and the same rule for ties). I dont want an array formula as the data is going to be a massive one, sorry for being specific.

Could someone please help me to resolve this. Thanks in advance.

Names 01/02/2023 01/03/2023 01/04/2023 01/05/2023 01/06/2023 01/04/2023 (Dynamic Date) Rank for the selected date in cell G1 against all names
Asa 10 10 7 6 8
Sab 2 3 8 2 6
Fus 2 3 8 6 6
Kus 1 2 9 8 7
Kuy 6 2 2 8 7
Microsoft 365 and Office | Excel | For business | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

Rich~M 20,370 Reputation points Volunteer Moderator
2024-04-30T22:41:33+00:00

Hi Asham.

For your question #2 add a helper column to calculate the running totals for the three days, in your question above that would be 1/2, 1/3, and 1/4. Use this formula and drag down in the helper column--Column J in the screenshot below.

=XLOOKUP($G$1,$B$1:$F$1,$B2:$F2)+XLOOKUP($G$1-1,$B$1:$F$1,$B2:$F2)+XLOOKUP($G$1-2,$B$1:$F$1,$B2:$F2)

Use this formula to rank the 3 day totals in Column K below and drag down.

=RANK(J2,$J$2:$J$6)+COUNTIF($J$2:J2,J2)-1

Was this answer helpful?

1 person found this answer helpful.
0 comments

Answer accepted by question author

Rich~M 20,370 Reputation points Volunteer Moderator
2024-04-30T22:19:30+00:00

Hi Asham. I am an Excel user like you.

Here is a formula that will do what you are looking for in question #1.

=RANK(XLOOKUP($G$2,$B$2:$F$2,$B3:$F3),XLOOKUP($G$2,$B$2:$F$2,$B$3:$F$7))+COUNTIF(XLOOKUP($G$2,$B$2:$F$2,$B$3:$F3),XLOOKUP($G$2,$B$2:$F$2,$B3:$F3))-1

Question #2 will require another approach. Did you mean months when you said,

"The same way, I also wanted to have a rolling 3 months Rank with change in date"

or did you mean days. That seems to be what you explained. I will continue to work on question #2 or perhaps someone else will be able to jump in and add something on this one.

Was this answer helpful?

1 person found this answer helpful.
0 comments

3 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2024-05-20T20:40:51+00:00

    It worked. Thank you :-)

    Was this answer helpful?

    0 comments
  2. Deleted

    This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

    Comments have been turned off. Learn more

  3. Ashish Mathur 101.9K Reputation points Volunteer Moderator
    2024-04-30T23:38:11+00:00

    Hi,

    Given the size of your data, I'd recommend using the Power tools of Excel - PowerPivot/Power Query. Since PowerPivot keeps crashing on my system, i cannot share that solution. However, since the formula language of these tools is similar to that of PowerBI, you may download the PBI file from here. You will have to transform the data and write these measures in Excel.

    Hope this partly helps.

    Was this answer helpful?

    0 comments