Computing ratings, spreadsheet?

Anyone make a spreadsheet of ratings, say, from = to +/- 400 points in maybe ten point increments? The minimum you can win is 1 point I think, the max 32? Not sure. So if someone is 100 points higher and wins, how many points does he win? If = the winner gets 16 points?

A few simple ones:

Win Expectancy

Equal 50%
10 points worse 48.6%
100 points worse 36.0%
200 points worse 24.0%

opposite for better e.g.

+10 = 51.4%
+100 = 64%

If you win you gain points equal to 32 x the chance of you losing.

If you beat someone 200 pts higher you gain 32 x 76% = 24 pts

Originally posted by sonhouse
Anyone make a spreadsheet of ratings, say, from = to +/- 400 points in maybe ten point increments? The minimum you can win is 1 point I think, the max 32? Not sure. So if someone is 100 points higher and wins, how many points does he win? If = the winner gets 16 points?

For a minute I was hoping you would volunteer. 🙂

Seems it would be easy. Maybe better to input the two ratings and just have it spit out the results, rather than trying to construct a chart of rating differences. The input of the two ratings could also handle the different K-factors.

The easy way to think of how many points you gain is simply you gain 16 points if you beat someone rated the same as you and you you win 17 points if you play someone 25 points higher than you...just keep thinking like that every 25 points is an extra point.

Originally posted by National Master Dale
The easy way to think of how many points you gain is simply you gain 16 points if you beat someone rated the same as you and you you win 17 points if you play someone 25 points higher than you...just keep thinking like that every 25 points is an extra point.

That sounds a bit easier to spreadsheetize. I might do it. There should be several sets though, one graph with K=32, K=24, and K=16 and provisional. Not sure how that is calculated, since there are wild rides there possible.

Was looking at the FAQ on ratings, and it says K=16 if you are 2400 and above. Does that mean the max a 2400 player can win against anyone is 16 points and the max anyone loses is 16 points? Also, if a 2400 player plays a provisional, does that mean he can only win 8 points?

Also, the faq does not say anything about draws. What is the formula for that?

So we are up to 6 separate graphs just for rated players so far!

So we need 3 graphs with two lines each, one for each K, not sure if you can make it all in one graph.

The general idea would be X is rating difference, Y = points from 0 to 32.

If I make such a graph, how would I be able to post it? Say I make a graph with Excel and have it saved, could we send it here? If so, how?

Originally posted by sonhouse
Anyone make a spreadsheet of ratings, say, from = to +/- 400 points in maybe ten point increments? The minimum you can win is 1 point I think, the max 32? Not sure. So if someone is 100 points higher and wins, how many points does he win? If = the winner gets 16 points?

This is Kewpie's posting from the Tournaments forum, in the thread about more banded tournaments. It's great, and I think it is what you are looking for:

Originally posted by Kewpie
This rating table is not guaranteed accurate above 2000, but it will do for this purpose. It was published in Thread 80000 some time ago.
Rating Chart

Difference ____ H -- D -- L
0-10 __________ 16 - 0 - 16
11-32 _________ 15 - 1 - 17
33-54 _________ 14 - 2 - 18
55-77 _________ 13 - 3 - 19
78-100 ________ 12 - 4 - 20
101-124 _______ 11 - 5 - 21
125-149 _______ 10 - 6 - 22
150-176 _______ 9 - 7 - 23
177-205 _______ 8 - 8 - 24
206-237 _______ 7 - 9 - 25
238-273 _______ 6 - 10 - 26
274-314 _______ 5 - 11 - 27
315-364 _______ 4 - 12 - 28
365-428 _______ 3 - 13 - 29
429-523 _______ 2 - 14 - 30
524-719 _______ 1 - 15 - 31
720+ __________ 0 - 16 - 32

With a 400-point rating difference, lower-rated player earns 29 points for a win, loses 3 points for a loss. (31 may have been an exaggeration, but it seems to be about right for a 550-point difference.)

In practice it doesn't work quite that way. The higher player may start 400 points above, but after 10 wins he'd be 430 points above and in the next bracket; after 20 more wins he'd be 470 points above. One loss would cost him 30 points and bring him back to 440; a second loss would make him 410.
Not going to happen like this of course, but as someone already said the rating tends to regress to the mean.

Originally posted by sonhouse
Anyone make a spreadsheet of ratings, say, from = to +/- 400 points in maybe ten point increments? The minimum you can win is 1 point I think, the max 32? Not sure. So if someone is 100 points higher and wins, how many points does he win? If = the winner gets 16 points?

Thanks a lot, pal. Now you have me playing around with this, trying to get higher on the spreadsheet learning curve. I don't know if I'll come up with anything, but I'm sure wasting my time. 😠

Originally posted by Mad Rook
Thanks a lot, pal. Now you have me playing around with this, trying to get higher on the spreadsheet learning curve. I don't know if I'll come up with anything, but I'm sure wasting my time. 😠

Hehe, at least I figured out the formula for draw, its in the formula, under 'score', 0=loss, 0.5= draw, 1.0= win. I tried doing the formula on one casio programmable but got nonsense output. It works with a casio scientific but I don't want to keep entering the whole thing for one solution. Now am trying a TI-84 using equation solver. Hope that one works out. If not, I will go to the big boy, my old HP-48.
The TI84 manual is almost 700 pages. I only print out the section I need. Almost a ream and a half if you don't have double side printing, 350 pages with double side.

We can do this in a spreadsheet actually. Although I'm not sure how accurate it is with really large differences in rating. Here's how I did it.

1: Open excel.
2: In cell B1 type "Input"
3: In cell A2 type "Old Rating"
4: In cell A3 type "Opponent's Rating"
5: In cell A4 type "Result"
6: In cell E1 type "Output"
7: In cell D2 type "New Rating"
8: In cell D3 type "Points Gained"
9: In cell D4 type "Win Expectancy"
10: In cell D5 type "K"
11: In cell D6 type "Score"
12: In cell E2 type "=B2+E5*(E6-E4)"
13: In cell E3 type "=E2-B2"
14: In cell E4 type "=1/(10^((B3-B2)/400)+1)"
15: In cell E5 type "=IF(B2<2100,32,IF(B2<2400,24,16))"
16: In cell E6 type "=IF(B4="W",1,IF(B4="D",0.5,IF(B4="L",0,"enter result"😉))"

Edit: that smiley is a " and a )

Now just enter current rating, opponents rating and the result.

Originally posted by Uries
We can do this in a spreadsheet actually. Although I'm not sure how accurate it is with really large differences in rating. Here's how I did it.

1: Open excel.
2: In cell B1 type "Input"
3: In cell A2 type "Old Rating"
4: In cell A3 type "Opponent's Rating"
5: In cell A4 type "Result"
6: In cell E1 type "Output"
7: In cell D2 type "New Rating"
8: In enter current rating, opponents rating and the result.

I believe it's completely accurate at any rating differences. (Compared it to my version - results matched perfectly.) Good work! The only thing it doesn't handle is the cases when your opponent is provisional. (That complicates things considerably.)

Btw, I'm using an older variant of OpenOffice, so I may not be completely compatible with Excel. But in cells E5 and E6, I had to change the commas to semicolons to get it to work on my PC. (Maybe Excel can handle commas but OpenOffice can't?)

Originally posted by Uries
We can do this in a spreadsheet actually. Although I'm not sure how accurate it is with really large differences in rating. Here's how I did it.

1: Open excel.
2: In cell B1 type "Input"
3: In cell A2 type "Old Rating"
4: In cell A3 type "Opponent's Rating"
5: In cell A4 type "Result"
6: In cell E1 type "Output"
7: In cell D2 type "New Rating"
8: In enter current rating, opponents rating and the result.

15: In cell E5 type "=IF(B2<2100,32,IF(B2<2400,24,16))"

In this step, does that statement cover the 2100-2400 area? I see all three K's there but how does it cover all three conditions? Not following the logic. Thanks.

Oh, I think I see it, if <2100, it's 32, if its <2400 it's 24, all else is 16, right?

Also, in the last line, you have 3 (((, but at the end only 2)). Will that work?

Also, do the word entries have to have " " around them? Will it work without?
So to run it you just put in the number of the old rating in A2 right next to it?, what about B1, input, what do you type there?

Originally posted by sonhouse
15: In cell E5 type "=IF(B2<2100,32,IF(B2<2400,24,16))"

In this step, does that statement cover the 2100-2400 area? I see all three K's there but how does it cover all three conditions? Not following the logic. Thanks.

Oh, I think I see it, if <2100, it's 32, if its <2400 it's 24, all else is 16, right?

Also, in the last line, you have 3 (((, but of the old rating in A2 right next to it?, what about B1, input, what do you type there?

"=IF(B2<2100,32,IF(B2<2400,24,16))"

The way the logic in excel works is:

IF(condition, true, false)

Broken down, it will be this:

If B2<2100 then return 32 else it will apply this:

If B2<2400 then return 24 otherwise it will return 16.

Therefore:

B2 below 2100, return 32.
B2 above 2100 and below 2400, return 24.
B2 above and equal to 2400, return 16.

The last line had a smilie which replaced one of the close brackets.

You don't need the " for typing text in excel directly.

"Input" is just a title above where you put the inputs. "Old Rating", "Opponent's Rating" and "Result" is put in b2, b3 and b4 (either W, D or L) respectively.

Originally posted by sonhouse
15: In cell E5 type "=IF(B2<2100,32,IF(B2<2400,24,16))"

In this step, does that statement cover the 2100-2400 area? I see all three K's there but how does it cover all three conditions? Not following the logic. Thanks.

Oh, I think I see it, if <2100, it's 32, if its <2400 it's 24, all else is 16, right?

Also, in the last line, you have 3 (((, but of the old rating in A2 right next to it?, what about B1, input, what do you type there?

Lausey's right, don't add the smiley. 😉 There need to be three closing parentheses at the end to match the others.

For the cells in column E, I don't think you can add the quotes, as that will mess up the formulas.

Add your old rating to cell B2, your opponent's rating to cell B3, and either a W, D, or L to cell B4.

Edit - Okay, lausey beat me to the punch with his edits. 🙂

Originally posted by sonhouse
Anyone make a spreadsheet of ratings, say, from = to +/- 400 points in maybe ten point increments? The minimum you can win is 1 point I think, the max 32? Not sure. So if someone is 100 points higher and wins, how many points does he win? If = the winner gets 16 points?

Interesting thread, Sonhouse. Here's some grist for your mill... five and past year opponent weighted, simple and to the point:

1) Wins + Draws = Non-Loss % (Draws are often both prudent and valuable. Of the three chess outcomes, only one hurts).
2) Loss%.
3) Non-Loss% divided by Loss% = Non-Loss/Loss Ratio.

* Example: Your 65% wins + 6% draws = 71% Non-Losses divided by your 29% losses = 2.45 x 100 (eliminating the decimal)
= 245 Non-Loss/Loss Ratio.

4) Your present RHP Rating = 1731, We'll ignore it in favor of your 5-year and past year averages relative to your opponents.

* Example: Non-Loss/Loss Ratio = 245 x .91 (1480/1624, 5-year opponent/personal average ratings) = 223 x .90
(1532/1695, 1-year) = 202 RHP Strength Rating as of 19 Aug '10).


Note: This overall RHP Strength Rating may possiblly provide a useful tool for clan leaders negotiating realistic match-ups.



..........................................................

Here's my spreadsheet version. I hope it works with Excel. I guess the big difference is that I used semicolons instead of commas for separators. I probably should have started in column A instead of col B, but that's water under the bridge. I don't think it matters much.

My version is longer than Uries' version, but mine also factors in provisional opponents. Note that your rating cannot be provisional. Also note that if your opponent is provisional, the provisional output for your opponent will display as very large numbers, indicating the fact that provisional ratings are calculated in a different manner that requires knowing the prior provisional rating history. This is clearly something that can't be modeled with this spreadsheet.

My version also gives complete delta R and new R for all three possible outcomes all at once.

I didn't bother rounding off the rating results. That shouldn't be a problem unless someone enters a fractional rating (Don't do it! ) that might cause some minor problems with the K-factor splits at 2099 and 2399.


For entering data into cells, enter information between the quotes, but leave off the quotes. And I guess I should point out the obvious; You don't need to actually type the info into the cells. Copy and paste is quicker and more accurate.


In cell B5 type "Enter your rating (non-provisional)"
In cell B9 type "Enter your opponent's rating"
In cell B11 type "If opponent is provisional, enter P here"
In cell B13 type "If opponent is provisional, enter number"
In cell B14 type "of games opponent has played"
In cell B20 type "Your win expectancy"
In cell B23 type "Your rating change for"
In cell B28 type "Your new rating for"

In cell C24 type "Your win"
In cell C25 type "Your draw"
In cell C26 type "Your loss"
In cell C29 type "Your win"
In cell C30 type "Your draw"
In cell C31 type "Your loss"

In cell E18 type "Output"
In cell E20 type "=IF(AND(OR(E11="P";E11="p" );E13<5);1/(10^((1200-E5)/400)+1);1/(10^((E9-E5)/400)+1))"
In cell E24 type "=F24*G24*(1-E20)"
In cell E25 type "=F24*G24*(0.5-E20)"
In cell E26 type "=F24*G24*(0-E20)"
In cell E29 type "=E5+E24"
In cell E30 type "=E5+E25"
In cell E31 type "=E5+E26"

In cell F24 type "=(IF(OR(E11="P";E11="p" );0.5;1))"

In cell G24 type "=(IF(AND(0<=E5;E5<=2099);32;(IF(AND(2099<E5;E5<=2399);24;16))))"

In cell H20 type "Opponent's win expectancy"
In cell H21 type "(if non-provisional)"
In cell H23 type "Opponent's rating change (if non-provisional) for"
In cell H28 type "Opponent's new rating (if non-provisional) for"

In cell I24 type "Opponent's win"
In cell I25 type "Opponent's draw"
In cell I26 type "Opponent's loss"
In cell I29 type "Opponent's win"
In cell I30 type "Opponent's draw"
In cell I31 type "Opponent's loss"

In cell K18 type "Output"
In cell K20 type "=IF(OR(E11="P";E11="p" );-9999999;1/(10^((E5-E9)/400)+1))"
In cell K24 type "=IF(OR(E11="P";E11="p" );-9999999;L24*(1-K20))"
In cell K25 type "=IF(OR(E11="P";E11="p" );-9999999;L24*(0.5-K20))"
In cell K26 type "=IF(OR(E11="P";E11="p" );-9999999;L24*(0-K20))"
In cell K29 type "=IF(OR(E11="P";E11="p" );-9999999;E9+K24)"
In cell K30 type "=IF(OR(E11="P";E11="p" );-9999999;E9+K25)"
In cell K31 type "=IF(OR(E11="P";E11="p" );-9999999;E9+K26)"

In cell L24 type "=(IF(AND(0<=E9;E9<=2099);32;(IF(AND(2099<E9;E9<=2399);24;16))))"


Now format cells E5, E9, E11, and E13 with a border and a light background color (I used light pastel yellow).

Now format cells E20, E24-26, E29-31, K20, K24-26, and K29-31 with a light pastel blue color.


By now it should be clear where you enter the data, but you enter your rating in cell E5, your opponent's rating in cell E9, a P in cell E11 if your opponent is provisional, and the number of games your opponent has played in cell E13 if he is provisional.