Auto-rank newly added to sheets without skipping a number and sort it-CodePudding

I am getting a sheet ranked automatically when a new row gets added (google sheets). The ranking is to avoid skipping a number in case of a tie, i.e., if two people have a RANK of 7, then the next person in the line is RANKED as 8 (and not 9)

Here is my formula

=ArrayFormula(SUM(if(A1<A:A,1/COUNTIF(A:A,A:A))) 1)

Question:

How can this formula be auto-repeated when new are added?
How can the rows be shuffled to get the RANK 1 to the top?

CodePudding user response：

This formula will automatically rank as I understand you want it. In C1 (the header in my example) put this:

={"Rank";BYROW(B2:B,LAMBDA(each,IFERROR(RANK(each,UNIQUE(B2:B)))))}

Then you can sort the range by Column C (if it doesn't appear by default, choose "advanced range sorting options" and you'll find it!)

With advanced options if needed:

And this would be the result: