1

I wrote this Rank formula to rank the numbers in column D by the values associated with them in Column E. I wanted the formula to resolve ties randomly, e.g. if there was a tie for first place, it would just randomly choose one of the accounts to be first and the other to be second. The way that I've incorporated randomness into this formula is hurting me though and I don't know how to fix it. Basically if there's a tie, its randomly choosing a number between 1 and 2 for both of the objects that are ranked #1, which means that there's a 50% chance that both will be the same number, and the tie won't be resolved. Can anyone help me sort this out?

=IF(COUNTIF($E:$E,$E2)=1,RANK($E2,$E:$E,0),INDEX(RANK($E2,$E:$E,0)+(RANDARRAY(1,COUNTIF($E:$E,$E2),0,1)<0.5),COUNTIF($E:$E,$E2)))

enter image description here

Mayukh Bhattacharya
  • 12,541
  • 5
  • 21
  • 32
CNG09d
  • 19
  • 2
  • Try this and give it a vote if it helps: https://stackoverflow.com/a/58640367/4961700 You will need to edit it to match your situation though. – Solar Mike Apr 28 '23 at 15:05
  • 1
    If you are agnostic about the order of matches, (and have an up-to-date Excel version) you could use `=LET(a,E2:E11,s,SEQUENCE(ROWS(a)),n,INDEX(SORT(HSTACK(a,s),1,-1),,2),MATCH(s,n,0))` – DS_London Apr 28 '23 at 17:12
  • Benefit of that is that it works on alphanumeric. It isn't random though. – P.b Apr 28 '23 at 18:51
  • @P.b Yeah, I wasn’t sure if randomness was a requirement or just part of a possible solution. Adding randomness will make the ranked range volatile, which may slow the sheet down with constant re-calcs. – DS_London Apr 29 '23 at 08:05
  • **will** slow down, not *may*. It will change on each change in the sheet (anywhere). It's probably better to find a VBA solution. – P.b Apr 29 '23 at 13:04

1 Answers1

1

Could you sort on the counts, then on a random value, then do a lookup?

=LET(values,DROP(TOCOL(E:E,1),1),n,ROWS(values),seq,SEQUENCE(n),sort,
SORTBY(seq,values,-1,RANDARRAY(n),1),XMATCH(seq,sort))

enter image description here

Tom Sharpe
  • 30,727
  • 5
  • 24
  • 37