I want to use ARRAYFORMULA with RANK in google spreadsheet but I have no idea about it.

Here's example

1 X Y Z Rank of X
2 4 6 1 2
3 1 3 2 3
4 3 2 1 1

In this case, I can write expression =RANK(A2, A2:C2) and copy it for other cell (D2:D).

But I want to make column D automatically added when I add new data for XYZ.

So I first tried using ARRAYFORMULA like this expression for D2;

And it doesn't work very well because RANK function's second parameter already could receive cell range.

Is there any way to make this example work?

Thank you

Here's my project sheet link.

This sheet is for recording score and rank of 4-player game which could include more than 4 user. Additional user may be added in future, but total number of user would be less than 10.

In score page, each row has score of 4-player and other cells have blank value. We can assume that range of each score is -100000~100000

In rank page, each row show rank of players who participate that match.

  • 1
    Is your sample data in your post reflective of your actual project? That is, do you really only have three columns of numbers (A:C) from which to find the rank? If so, that can be done simply with comparison operators. If not, you'll want to be specific in your post (e.g., if you have 5 columns to rank or 50), since this will rule out certain approaches. And will you only want the rank of ONE column; or will you be wanting the rank for EVERY column in your real project? Details matter.
    – Erik Tyler
    Aug 10 at 0:08
  • Thank you for your comment. I added some details on post. Aug 10 at 4:41
  • It would be most helpful if you could share a link to a copy of your spreadsheet. However, at a minimum, please share the actual ranges where the raw data resides as well as the actual ranges where the rankings 1 - 5 will reside in each row. This will make it easier for those here to write the formula as well as for you to later adapt it to your actual sheet. If you are not sharing a link to a copy of your spreadsheet, please also inform the community what international locale your sheet is set to, since this will affect specifics of how any formula will be written.
    – Erik Tyler
    Aug 10 at 6:36
  • I added my google sheet link and more details. It helped a lot in reinforcing my question. Thank you. Aug 10 at 8:52
  • Hi and welcome. There have been a number of questions on this subject. Would you please summarise your own research - it will avoid volunteers covering the same ground.
    – Tedinoz
    Aug 10 at 9:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.