I have a column of numbers 1-50 in a Google Sheet, which are a ranking list for a different column of fifty items (items are ranked relative to one another, so 1 is the best in the list and 50 is the worst; there are no repeats). I want to create a formula that will automatically adjust the list if I change the ranking of an item. For example, if I change one item's ranking from 25 to 30, I want the rankings 26-30 to automatically shift and become 25-29 (while still assigned to the same item they originally were). Are there existing functions I can use to do this?

  • Welcome. Please read how to and share a test sheet so as you can be easier helped. – marikamitsos Feb 28 at 4:41
  • Welcome. Have you tried the RANK.EQ formula? This will automatically adjust all the rankings if you modify any score. It is not clear from your question whether you expect the ranks to be merely recalculated, or whether you expect the entire table to be re-sorted according to the rankings. In the latter case, RANK.EQ combined with QUERY (ordered on the rank column) might suit. – Tedinoz Mar 1 at 10:33
  • Thank you so much! I wasn't aware of the RANK.EQ function, that was definitely very helpful. I'm not too sure how to use QUERY, but I wanted a list like the one described in the latter part of your example (and most importantly with no repeat numbers). My raw data was in column C, beginning in row 2, and this is what I used: =ArrayFormula(if(len(C2:C),array_constrain(sort({row(C2:C)-1,sort({row(C2:C)-1,C2:C},2,true)},2,true),9^9,1),)) – alex Mar 3 at 6:34

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.