Ratings Model - Unable to change from one rating to the next

[cassca]

Hello.

I initially posted this in general excel forum (sorry! any mods might want to remove it from there).

My issue is more to do with formulas, with which I am still very much a beginner. Any help for the below would be much appreciated.

I am trying to solve the attached issue spread over two excel sheets/tabs.

I have worked out the rating of each person's chances of winning (SHEET 1) and I now want to make this more automated so I can enter ratings and the data will automatically update with the expected chances of that person winning (SHEET 2)

I have worked out that a person rated as '1' has an expected chance of winning against any person rated 1,2,3,4,5 and so on... and i can enter in different ratings of the opponent and it calculates fine.

How do I create a spreadsheet that will allow me to enter someone rated '2' or '3' and their expected chances, or simply change the rating of the player and have the spreadsheet update.

I want to be able to change the ratings at any time and have the spreadsheet replicate this.

[loginjmor]

Hi -

I think I understand what you're doing. Basically you want to create a matrix with player matchup odds across the top and down the left side. That way, for any combination of matchups you can easily look up the correct statistic. So, on your first tab, I rearranged your data into a matrix as described. Then, on the second tab, I used a combination of VLOOKUP and MATCH to search the matrix. That formula looks like this:

=VLOOKUP(B10,Sheet1!$A$12:$F$17,MATCH(Sheet2!A10,Sheet1!$A$12:$F$12,0))

You are familiar with how VLOOKUP works. That finds the correct row. I then just supplement the column VLOOKUP goes to by using the MATCH function to find the correct opponent, and therefore, the correct column.

Attached is your spreadsheet. I have highlighted the areas I have mentioned in pink.

Hope this helps.