Challenge - Cant figure this out! (ranking scores)

Trying to rank games by positions over a month.

Receive a XML file for each individual day for a month. In that file are game names and their ranking (top 50)

I want to import those XML files into LibreCalc and have the spreasheet recognise the scores for each particular game to create an overall score

For example on 1st May Game X may rank 5th so needs to be allocated 46pts on the spreadsheet. The next day the game may rank 8th so needs 43pts on the spreadsheet. However because of the structure Game X is not getting imported into the same Row on Calc.

Its probably a basic formula I need but cant suss it out

You don’t share a lot of info. Without knowing more about your data layout and your ranking system, it is not easy to suggest any formula. My best shot is =51 - <ranking>, but that depends on the ranking being present as a series from 1 to 50, and you want to award points in a linear scale with 50 points for the top game and 1 point for the one on the bottom of the ranking.

Otherwise, some functions which may be useful:

  • MATCH() locates an item in a list, and returns the position.
  • INDEX() picks a specific item from a range (list).
  • RANK() returns the ranking of an item within a list.