Hi guys
I'm struggling with a problem. I'm trying to make a model that prf edicts the outcome osoccer matches. I have a great dataset available with all matches in The Netherlands since 1956.
I'm trying to do the following:
I have Three columns: match nr, score Ajax (Hollands no 1 club!!) and FADE
I want to add a new column in excel, named P (Power) which indicates the strength (=probability of winning the next match) of Ajax at that very moment
Take a look:
A B C
match match nr score Ajax FADE
1 0 3 1
2 1 1 0,5
3 2 1 0,25
4 3 3 0,125
5 4 -1 0,0625
6 5 0 0,03125
7 6 2 0,015625
8 7 5 0,0078125
9 8 -1 0,00390625
10 9 4 0,0019531d25
The score P (in column D) in each row should be the sumproduct(B$1:Bx;Cx:C$1). Note that the
column C should be reversed
To illustrate this for row 10: (1*4+0,5*-1+0,25*5, etc). So column C should be reversed and then used as the input for a sumproduct with column B.
The problem is that excel automatically neglects my formula sumproduct(B$1:Bx;Cx:C$1) and turns it into sumproduct(B$1:Bx;C$1Cx), giving the wrong answer!!!
Do any of you have an elegant solution? One that don''t need creating new columns, but rather a in-memory solution?
Thanks!!!! Greets from Amsterdam
Bookmarks