Computing ratings, spreadsheet?

Originally posted by Mad Rook
Yeah, the numbers should pop into the output cells as soon as you hit the return key after entering the second rating number.

#Value means there's something wrong with the formula or the formula in that cell is trying to reference a cell that contains text instead of a number.

First, check to see if you have good output numbers in these three cells:
lem with cells E29-31 (which seems to be the case). Double check the formulas in these cells.

Do you enter your win in E20? I tried that but no joy. If not, where do you enter wins? or losses or draws? I cut and pasted those E20 and up in each one. I think they are entered as it says in your post. Does the formula in E20 stay there as a formula or is the formula supposed to disappear? F24, G24 also has formula same thing. Is that ok?

In your original post, you mentioned possible problem using semicolons instead of commas, do you think that is the problem I am seeing? I am using excel. Should I try this in Open Office instead?

Originally posted by sonhouse
Do you enter your win in E20? I tried that but no joy. If not, where do you enter wins? or losses or draws? I cut and pasted those E20 and up in each one. I think they are entered as it says in your post. Does the formula in E20 stay there as a formula or is the formula supposed to disappear? F24, G24 also has formula same thing. Is that ok?

You don't enter wins in E20. E20 is the win expectancy, which is automatically calculated from the two rating numbers.

You don't enter wins, draws, or losses anywhere. The spreadsheet calculates the rating changes for all three possibilities.

Once you enter the formula into E20 (or F24 or G24), the formula should disappear from the cell (and a number may or may not appear). If the formula doesn't disappear, then there's something wrong with the formula. (Are you adding the equal sign at the beginning of each formula? The equal sign is needed. )

One possible reason for the problem might have to do with the semicolon/comma issue I mentioned earlier in the thread. I'm using an older version of OpenOffice, which uses semicolons as separators in the conditional statements. However, Excel uses commas instead of semicolons. It might be that Excel can't handle the semicolons. If you're using something other than OpenOffice, you could try replacing all of the semicolons in all the cut-and-pasting stuff with commas, and see if that works.

Edit - I just saw your edit. Yes, the semicolons might be the problem if you're using Excel. (See above)

Originally posted by sonhouse
In your original post, you mentioned possible problem using semicolons instead of commas, do you think that is the problem I am seeing? I am using excel. Should I try this in Open Office instead?

You can use OpenOffice if you want to, but I am curious to know if it will work in Excel if you change the semicolons to commas. Then you or I could repost the spreadsheet formulas for the benefit of others who might want to try it in Excel.

Originally posted by Mad Rook
You can use OpenOffice if you want to, but I am curious to know if it will work in Excel if you change the semicolons to commas. Then you or I could repost the spreadsheet formulas for the benefit of others who might want to try it in Excel.

It works if you replace it with commas.

Originally posted by Uries
It works if you replace it with commas.

Thanks for the confirmation.

I may move things around a bit, clean things up to make them look better, then repost the formulas for both OpenOffice and Excel.

Originally posted by Mad Rook
Thanks for the confirmation.

I may move things around a bit, clean things up to make them look better, then repost the formulas for both OpenOffice and Excel.

Yeah, they jump around a bit as played.
Does anyone know if the method of calculating provisional ratings here is the same as in ELO or USCF or FIDE?. BCF would be clearly different since the numbers are an order of magnitude lower.

Would a spreadsheet that allows calculation of new ratings in all these systems be too cumbersome to use?

Originally posted by sonhouse
Yeah, they jump around a bit as played.
Does anyone know if the method of calculating provisional ratings here is the same as in ELO or USCF or FIDE?. BCF would be clearly different since the numbers are an order of magnitude lower.

Would a spreadsheet that allows calculation of new ratings in all these systems be too cumbersome to use?

I don't really follow the FIDE or BCF rating systems, but I'm sure there are differences from the RHP method.

Regarding the USCF rating system, it's an iterative calculation process, with numerous differences from the RHP system. Mark Glickman has posted a pdf document on his web site that gives the details of the USCF rating system. The first link is the detailed explanation; The second link is the document for the approximating formulas; and the third link is Glickman's rating web page:

http://www.glicko.net/ratings/rating.system.pdf

http://www.glicko.net/ratings/approx.pdf

http://www.glicko.net/ratings.html


I've never thought about whether it's possible to "spreadsheet-ize" the USCF rating system. I doubt it, but at any rate, the USCF sytem is so complicated that I have no desire to even attempt it.

Sorry for the long post. 😞

OK, here's cleaned up versions of the "RHP Rating Changes" spreadsheet for both Excel and OpenOffice. The only real difference is that Excel uses comma separators in conditional statements, and OpenOffice uses semicolons. However, I've also moved stuff around and added some stuff, so many of the cell numbers have changed.

As noted previously, your rating cannot be provisional. Also, if your opponent is provisional, the provisional output for your opponent will display as very large negative 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.

For entering data into cells, enter information between the quotes, but leave off the quotes. Copy and pasting is quicker and more accurate than retyping the data.



Here's the data for the Excel spreadsheet:

In cell A5, type "Enter your rating (non-provisional) in cell E5"
In cell A8, type "Enter your opponent's rating in cell E8"
In cell A10, type "If opponent is provisional, enter P in cell E10"
In cell A12, type "If opponent is provisional, enter number"
In cell A13, type "of games opponent has played in cell E13"
In cell A18, type "Your win expectancy"
In cell A19, type "Your K factor"
In cell A20, type "Provisional opponent adjustment factor"
In cell A22, type "Your rating change for"
In cell A27, type "Your new rating for"

In cell C23, type "Your win"
In cell C24, type "Your draw"
In cell C25, type "Your loss"
In cell C28, type "Your win"
In cell C29, type "Your draw"
In cell C30, type "Your loss"

In cell D17, type "Output"
In cell D18, type "=IF(AND(OR(E10="P",E10="p" ),E13<5),1/(10^((1200-E5)/400)+1),1/(10^((E8-E5)/400)+1))"
In cell D19, type "=IF(AND(0<=E5,E5<=2099),32,IF(AND(2099<E5,E5<=2399),24,16))"
In cell D20, type "=IF(OR(E10="P",E10="p" ),0.5,1)"
In cell D23, type "=D20*D19*(1-D18)"
In cell D24, type "=D20*D19*(0.5-D18)"
In cell D25, type "=D20*D19*(0-D18)"
In cell D28, type "=E5+D23"
In cell D29, type "=E5+D24"
In cell D30, type "=E5+D25"

In cell E3, type "Input"

In cell F1, type "RHP Rating Changes"

In cell G18, type "Opponent's win expectancy (if non-provisional)"
In cell G19, type "Opponent's K factor (if non-provisional)"
In cell G22, type "Opponent's rating change (if non-provisional) for"
In cell G27, type "Opponent's new rating (if non-provisional) for"

In cell I23, type "Opponent's win"
In cell I24, type "Opponent's draw"
In cell I25, type "Opponent's loss"
In cell I28, type "Opponent's win"
In cell I29, type "Opponent's draw"
In cell I30, type "Opponent's loss"

In cell K17, type "Output"
In cell K18, type "=IF(OR(E10="P",E10="p" ),-9999999,1/(10^((E5-E8)/400)+1))"
In cell K19, type "=IF(OR(E10="P",E10="p" ),-9999999,IF(AND(0<=E8,E8<=2099),32,IF(AND(2099<E8,E8<=2399),24,16)))"
In cell K23, type "=IF(OR(E10="P",E10="p" ),-9999999,K19*(1-K18))"
In cell K24, type "=IF(OR(E10="P",E10="p" ),-9999999,K19*(0.5-K18))"
In cell K25, type "=IF(OR(E10="P",E10="p" ),-9999999,K19*(0-K18))"
In cell K28, type "=IF(OR(E10="P",E10="p" ),-9999999,E8+K23)"
In cell K29, type "=IF(OR(E10="P",E10="p" ),-9999999,E8+K24)"
In cell K30, type "=IF(OR(E10="P",E10="p" ),-9999999,E8+K25)"





And here's the data for the OpenOffice spreadsheet:

In cell A5, type "Enter your rating (non-provisional) in cell E5"
In cell A8, type "Enter your opponent's rating in cell E8"
In cell A10, type "If opponent is provisional, enter P in cell E10"
In cell A12, type "If opponent is provisional, enter number"
In cell A13, type "of games opponent has played in cell E13"
In cell A18, type "Your win expectancy"
In cell A19, type "Your K factor"
In cell A20, type "Provisional opponent adjustment factor"
In cell A22, type "Your rating change for"
In cell A27, type "Your new rating for"

In cell C23, type "Your win"
In cell C24, type "Your draw"
In cell C25, type "Your loss"
In cell C28, type "Your win"
In cell C29, type "Your draw"
In cell C30, type "Your loss"

In cell D17, type "Output"
In cell D18, type "=IF(AND(OR(E10="P";E10="p" );E13<5);1/(10^((1200-E5)/400)+1);1/(10^((E8-E5)/400)+1))"
In cell D19, type "=IF(AND(0<=E5;E5<=2099);32;IF(AND(2099<E5;E5<=2399);24;16))"
In cell D20, type "=IF(OR(E10="P";E10="p" );0.5;1)"
In cell D23, type "=D20*D19*(1-D18)"
In cell D24, type "=D20*D19*(0.5-D18)"
In cell D25, type "=D20*D19*(0-D18)"
In cell D28, type "=E5+D23"
In cell D29, type "=E5+D24"
In cell D30, type "=E5+D25"

In cell E3, type "Input"

In cell F1, type "RHP Rating Changes"

In cell G18, type "Opponent's win expectancy (if non-provisional)"
In cell G19, type "Opponent's K factor (if non-provisional)"
In cell G22, type "Opponent's rating change (if non-provisional) for"
In cell G27, type "Opponent's new rating (if non-provisional) for"

In cell I23, type "Opponent's win"
In cell I24, type "Opponent's draw"
In cell I25, type "Opponent's loss"
In cell I28, type "Opponent's win"
In cell I29, type "Opponent's draw"
In cell I30, type "Opponent's loss"

In cell K17, type "Output"
In cell K18, type "=IF(OR(E10="P";E10="p" );-9999999;1/(10^((E5-E8)/400)+1))"
In cell K19, type "=IF(OR(E10="P";E10="p" );-9999999;IF(AND(0<=E8;E8<=2099);32;IF(AND(2099<E8;E8<=2399);24;16)))"
In cell K23, type "=IF(OR(E10="P";E10="p" );-9999999;K19*(1-K18))"
In cell K24, type "=IF(OR(E10="P";E10="p" );-9999999;K19*(0.5-K18))"
In cell K25, type "=IF(OR(E10="P";E10="p" );-9999999;K19*(0-K18))"
In cell K28, type "=IF(OR(E10="P";E10="p" );-9999999;E8+K23)"
In cell K29, type "=IF(OR(E10="P";E10="p" );-9999999;E8+K24)"
In cell K30, type "=IF(OR(E10="P";E10="p" );-9999999;E8+K25)"




If you want to, you can dress up the spreadsheet a little:

You can change the font of the "RHP Rating Changes", "Input", and "Output" headings to bold and a larger size font.

You can add a background color (light pastel yellow or something similar) to input cells E5, E8, E10, and E13.

You can add a background color (light pastel blue or something similar) to output cells D18-D20, D23-D25, D28-D30, K18, K19, K23-K25, and K28-K30.

You can even add a single border around the blocks of cells D18-D30, E5-E13, and K18-K30.

Originally posted by Mad Rook
I've never thought about whether it's possible to "spreadsheet-ize" the USCF rating system. I doubt it, but at any rate, the USCF sytem is so complicated that I have no desire to even attempt it.

Looking at Glickman's paper again: If possible, it would be a biiiiiig job, at least in my world. Probably the biggest problem in doing it is that in some special situations, you need to know the prior scoring history of players (that is, if they've had either all wins or all losses in all previous rated games.)

For anyone who didn't want to spend the 30 minutes required to paste in the spreadsheet data and dress up the visuals, I've uploaded the RHP_Ratings spreadsheet to this URL:

http://drop.io/vacaland1

It's in three file formats:

.ods is OpenOffice Open Document format
.sxc is OpenOffice 1.0 format
.xls is Excel 97/2000/XP format

The URL will be active for 1 year or until I decide to dump it, whichever comes first.

I hope you like it!

Originally posted by Mad Rook
For anyone who didn't want to spend the 30 minutes required to paste in the spreadsheet data and dress up the visuals, I've uploaded the RHP_Ratings spreadsheet to this URL:

http://drop.io/vacaland1

It's in three file formats:

.ods is OpenOffice Open Document format
.sxc is OpenOffice 1.0 format
.xls is Excel 97/2000/XP format

The URL will be active for 1 year or until I decide to dump it, whichever comes first.

I hope you like it!

I downloaded the Excel version, it works fine, the one problem I had was not noticing where the file went when I saved it. It did not save right inside excel, had to do some fishing to get it inside excel but it is there now. I was playing with it and see about a difference of 723 points when you lose you get the difference between losing 1 point and losing 2 points. For some reason I thought the loss went smoothly to zero at a difference of 400 points.

So couldn't you do the same thing with just a bunch of numbers in memory? Have each rating difference refer to the appropriate memory which would be the answer? It doesn't have to be calculated each time if it scales linearly, the difference between a loss against an opponent rated 2200 and you at 1500 is the same as the ratings of 2100 and 1400, 2000 and 1300, etc. So the main key is the rating difference, so just a bunch of numbers in memory would give the same solution, right?

Well, thinking about it for about 2 seconds, I realized the job would be bigger than I thought because of the K factor, three times the numbers in memory than I first thought. So you would have to have about 725 solutions per K # or 3X 725 or 2175 memory slots used. I bet that would still be faster on a programmable calculator.


I got some of that formula in a Casio 9850 and it seemed to take forever just to calculate the win expectancy part. I bet if it was just a large bank of memory, 3 K #'s or no, it would be a lot faster since you are only accessing a memory rather than actually calculating something. I was surprised at how slow that casio was. I am going to try it in my trusty HP48G and see how long it takes there. The newer ones, like the HP49 is supposed to be 7 times faster than the 48, which must be 10 times faster than that old 9850!

Originally posted by sonhouse
I downloaded the Excel version, it works fine, the one problem I had was not noticing where the file went when I saved it. It did not save right inside excel, had to do some fishing to get it inside excel but it is there now. I was playing with it and see about a difference of 723 points when you lose you get the difference between losing 1 point and losing ...[text shortened]... supposed to be 7 times faster than the 48, which must be 10 times faster than that old 9850!

I know some of those handheld calculators can be slow, but I can't believe they're that slow. Although it would have to calculate a few powers, it's not a long calculation (no looping). I can't understand why it would take the Casio so long just to calculate the win expectancy.

Regarding the Casio, just how long was forever?

Originally posted by Mad Rook
I know some of those handheld calculators can be slow, but I can't believe they're that slow. Although it would have to calculate a few powers, it's not a long calculation (no looping). I can't understand why it would take the Casio so long just to calculate the win expectancy.

Regarding the Casio, just how long was forever?

I'd have to do it again, it was just entered in the equation solver so it's not in memory, I think. But it took maybe 20 seconds or so to do. I'll do it on the HP48, see if it's faster.
Obviously it would be microseconds or some such using like mathematica on a PC.

My physicist son in law gave me a cracked copy of mathematica but before I could start using it, a few days later I found it wasn't as cracked as it was supposed to be and asked for a licence. Dang🙂 Oh well. I then downloaded a bunch of mathematica imitators online for free. Like Euler Toolbox and Algebra Equation Solver.

Originally posted by sonhouse
I was playing with it and see about a difference of 723 points when you lose you get the difference between losing 1 point and losing 2 points. For some reason I thought the loss went smoothly to zero at a difference of 400 points.

To be honest, I'm not quite sure what you meant by this.


As an aside, I couldn't resist some tweaks to the spreadsheet. (I just discovered the data validity feature. 🙂 ) I uploaded version 1.2 to the drop ( http://drop.io/vacaland1 ). I left the old version there, at least for now.

Version 1.2 changes:
1) Changed data validity for input cells to not allow negative or fractional ratings.
2) Changed output cells to ensure that ratings will never go below zero.

These changes are extremely marginal, as I can't imagine why anyone would want to input negative or fractional ratings, and only nutjobs who try to get their ratings as low as possible would run into the problem of negative ratings output.