Attempting to do an ELO Style sheet for UK Football

Hi All,

In short, I was doing a little bit of reading on sports and came across a book called Profitable Football Betting and thought I'd try and put together one of the suggested systems in the book (I already do weighted and unweighted least squares using solver but just wanted try something else).

Basically it is an ELO style idea (it's not pure as in the ELO chess way of doing things), all teams start with 20 points, and then you move the ratings up and down based on predictions against results etc.

Having done a little bit of digging around I am aware of the SHG template on ELO, so have used that in line with the way the ratings are calculated in the book.

My problem is essentially around carrying the ratings forward (the attached is very much work in progress - I do want to table the data vertically in the end but for the moment I am not overly worried about it)

Columns A to L I am fine with the formula in them (if someone could take a quick look at E & F I would appreciate it as I am not 100% sure that they are clean).

My problem arises over at M45 (where I have modified SHG's formula which I didn't 100% understand - considered trying to do this with a HLOOKUP but an IF seemed cleaner).

In short I have got it to pick up the new Accrington figure correctly at 19.88.

M46 however then returns a "false" and I can't figure why?

Therefore when it gets down to Accrington's next game on Row 58 highlighted, column F on this line picks up the "false", column L then returns the new rating as -0.08, which gets correctly picked up in M58.

I don't think that I am too far away, but I just want to get it to work for 1 team to start with as then carrying the process through shouldn't be too difficult hopefully.

Thanks in advance for any suggestions.

Div 3 ELO Power Rankings - Forum Copy.xlsx

I haven't done a painstaking reading of your post or workbook but the thing that caught my attention there and in your upload RE: "M46......returns "false" and I can't figure why."

M46 contains "IF($B46=$M$43,$L46)". Since Shrewsbury (B46) does not equal Accrington (M43) your formula returns "FALSE".

In E47 and below you are trying to reference rows with HLOOKUP where there is no data. So it adds 0.2 (from C2) to 0.

F47 and below is much the same story.

In E47 try changing your formula from =IF(A46="", "", HLOOKUP(A46,$M$43:$AJ46, ROWS(A$43:A46) - 1, FALSE) )+($C$2)

to at least =IF(A46="", "", HLOOKUP(A46,$M$43:$AJ$45, 3, FALSE) )+($C$2).

This will "anchor" your Table_array argument with absolute addressing. For the Row_index_num argument it appears that you want data from the last (3rd) row. Change it to 2 if your wish to return values from the 2nd row.

Thanks for taking the time to look at it. I will take a look at what you've suggested as I could kind of figure out why I returned "false" (or the logic behind it), but couldn't figure out what I had done wrong with the formula to work round it (one of those, I had the concept in my head, know that's it's do-able, not 100% sure I know how to do it moments ). I think with your ideas, I may be able to work round what the issue is.

Glad it helps. Thanks for the feedback.