ELO calculating formula + how to get the biggest n numbers in a row

**sjuesju** · 06-17-2014, 10:47 AM

Hello and thanks in advance to everyone who help me to solve this one. We use an Excel sheet to follow and calculate the results in the table tennis tournament we participate in and we have some ideas to improve the file. If this happen it will spare us time, which would be great. However, we couldn't find the needed solution yet. This is our file turnir-montana-2014.xls.

On it, on the tab called 2014_05_29 is the table we want to use for following the results in our competition with one exception. We would like the Excel file to calculate automatically the ELO points the winner gets and loser losses after each match. Now, we do this manually through this site http://bzstats.strayer.de/bzinfo/elo/?lang=en. I see that on the site is given a formula, but I couldn't find a way to apply it to Excel.

My idea is to use an If to determine which one of the players is the winner (he or she will have 3 sets won). Then to be applied one of the two formulas for ELO rating calculations. Then the result to be pasted on the next match which the player will play and so on.

Is this possible?

My second question is related to the fact that we are going to play around 30 tournaments this year, but we will take into our calculations for the final standing only the best 12 results for each player. We follow these results on the tab Ranking. Is there a formula which could calculate only the best 12 results a player has achieved during all the tournaments we will play. Of course if the sell is empty (the player didn't participate in the tournament) the result won't be count.

I hope I have explained it correctly. I am sorry if there are some misunderstandings, but English is not my native language, which is obvious

**MrShorty** · 06-17-2014, 11:18 AM

Can you be specific about what you are having trouble with?

The expected score seems relatively easy to implement. Keeping the parentheses straight will be a challenge. The POW() function in strayer's language is the =POWER() function in Excel: http://office.microsoft.com/en-us/ma...930.aspx?CTT=1

The new rating function should also be straightforward. Strayer's "round" function would be one of Excel's round functions -- depending on the type of rounding you usually do -- ROUND(), ROUNDDOWN(), ROUNDUP(), etc.

Wikipedia's article may provide additional ideas: http://en.wikipedia.org/wiki/Elo_rating_system.

EA=1/(1+power(10,(RB-RA)/400)) I think
RAN=RA+round(K*(SA-EA),0) I think

**sjuesju** · 06-18-2014, 01:39 AM

Well, I am not good enough to do it, but I will try following your ideas. What about having only the 12 best results calculated. Is there such formula?

**MrShorty** · 06-18-2014, 03:05 PM

What about having only the 12 best results calculated. Is there such formula?

I would use the LARGE() [or SMALL() if "best" means lowest score] function (http://office.microsoft.com/en-us/ex...in=HP010342656) to extract the 12 best results. Then you can apply your other formulas/analysis to these values only.