google-sheetsranking-functions

auto ranking of rows when new data is added to sheet


I have a Google sheet (sheet A) that gets fed from another sheet (sheet B). I am trying to auto-rank the rows when new data gets added to sheet B. 

In the provided example screenshot (Example below), I use a formula for the "Points" column (M). All it does is if Column L has a value of 1 then assign 7 points, if the value is 2, then assign 5 points, and if the value is 3 then assign 3 points. All others get 1 point. So every new row is added - the points are automatically assigned.

={"Points";arrayformula(IF(L2:L="",,IF(L2:L=1,7,IF(L2:L=2,5,IF(L2:L=3,3,1)))))}

The Rank column (L) uses the formula =RANK(K2, K:K).

I want a similar formula similar to points formula that ranks each of the new rows based on the Points.

I tried this formula but it does not work -

={"Rankings";arrayformula(IF(K2:K="",,IF(K2:K>1,RANK(K2, K:K))))}

Any help is appreciated.

Example Screenshot


Solution

  • you can try this in Column L

    ={"Rank";BYROW(K2:K,LAMBDA(ax,IF(ax="",,RANK(ax,K2:K))))}
    

    enter image description here