I’m stuck on a problem with my ELO rating system in a spreadsheet for tracking 1v1 matches. The challenge I face is correctly obtaining the ‘ELO Before’ value. I need to identify the player’s last ‘ELO After’ score from earlier matches and default to 1500 for new players.
I’ve been trying a formula that works row-by-row to avoid pulling future data. Consider this approach:
It gets close but doesn’t solve my issue completely. Any suggestions for a formula update that can reliably process each row would be greatly appreciated. Thanks for your help!
hey there! have u tried using INDEX/MATCH instead of VLOOKUP? it’s more flexible. something like: =IF(COUNTIF(A$2:A2,A2)>1,INDEX(F$2:F2,MATCH(A2,A$2:A2,0)-1),1500) this should work better for finding the last elo score. let me kno if it helps!
I’ve been wrestling with a similar ELO tracking problem in my spreadsheets. What finally worked for me was using a combination of OFFSET and MATCH functions. Here’s a formula that might solve your issue:
This formula looks for the player’s name in column A, finds the last occurrence before the current row, and returns the corresponding ‘ELO After’ value from column F. It also defaults to 1500 for new players.
The beauty of this approach is that it’s pretty efficient and doesn’t require complex array formulas. It’s been reliable in my experience, even with large datasets. Give it a try and see if it helps streamline your ELO tracking!
I’ve encountered a similar issue in my ELO tracking system. One solution that worked for me was utilizing the MAXIFS function combined with INDEX/MATCH. Here’s a formula you could try:
This approach finds the most recent previous ELO score for each player, considering only rows above the current one. It defaults to 1500 for new players. The formula is complex, but it’s quite effective in handling row-by-row calculations without referencing future data.