1

I'm having problems with something that is likey very simple to correct. I have a form that submits data to a Google Spreadsheet, simply a date, name and score. On a separate sheet I am going to have a leaderboard which shows all submissions ranked by highest score (for simplicity in the example in the link below, I just have the leaderboard showing up on the right of the same sheet). I have it sorting the data fine, but I'm struggling with getting the 'rank' value to display. As shown for the first 3 rows (G2, G3, G4) I know what the formula is to display the 'rank' value...but what I'm struggling with is how to get that value to show without having to have that formula in each cell. Since the data will be coming from a form, there will obviously be new rows added regularly which means the leaderboard will automatically get adjusted and I want all of the rows to display the rank #. From what I have read, ArrayFormula should allow this to work, but even with looking at examples I can't figure out how to get it to work with my formula.

I know I could just highlight the entire 'G' column and paste in the formula, and hope it adds it to enough rows...but then it displays 'N/A' for all of the rows which don't currenlty have any data.

Hoping its just a simple solution that I'm being dumb and missing...any help would be greatly appreciated. The link to an example is below. To summarize, for all rows that have content in column H and I, the G cell for that row should show the rank value automatically.

https://docs.google.com/spreadsheets/d/1pCIJQi5g2scOtB6o2PgVVb-0azzhupEOPjiL0RMM57A/edit?usp=sharing

Thank you!

Mark
  • 739
  • 1
  • 9
  • 15

2 Answers2

1
=ARRAYFORMULA(RANK(INDIRECT("I2:I"&COUNTA(H:H)),$I$2:I,0))

This will automatically rank and sort, for all values, including additional ones that are added. You only need to enter it into G2, and it will dynamically fill in the rest for you.

Aurielle Perlmann
  • 5,323
  • 1
  • 15
  • 26
0

You can use 

IFERROR(RANK(...),"")

and drag it to all rows - this will leave blank cells instead of #N/As. I'm sure there are other ways but that seems like the easiest one to me.

oliver13
  • 996
  • 2
  • 7
  • 19