Excel - combining SUMPRODUCT

user_15945113 0
Viewed 61

I have an Excel sheet that I am using to update player ratings.

Player Placing IniRating 1 2 3 4 FinRating
A 1 2.0 1.000 0.018 0.016 0.014 2.007
D 2 -2.0 54.598 0.982 0.856 0.775 -1.758
C 3 -0.1 8.166 0.147 0.128 0.116 -0.213
B 4 0.1 6.686 0.120 0.105 0.095 -0.036

The formulas in cells D2 to G5 is

=EXP(-$C2)/SUMPRODUCT(--($B$2:$B$5<=D$1)*(EXP(-$C$2:$C$5)))

and the formulas in column H (FinRating) is

=((SUMPRODUCT(($D$1:$G$1>=B2)*$D2:$G2)-1)*0.15)+C2

I want to apply these formulas across multiple sheets (events) and increase the number of Players (which would require adding more columns) so would be ideal to have them combined in one formula.

Ideally the formula would go into column FinRating I could adjust the ranges it referenced and the FinRating would be calculated. Final table would look like this

Player Placing IniRating FinRating
A 1 2.0 2.007
D 2 -2.0 -1.758
C 3 -0.1 -0.213
B 4 0.1 -0.036

I've spent some time trying to combine these formulas with no luck, and my exprience using SUMPRODUCT is a little light.

Any thoughts on how to achieve this?

excel-formula excel
1 Answers

Here's a solution that uses 3 helper columns (which could be hidden). Note that you don't need to add more helper columns for a larger number of players --- 3 columns works for all cases.

In D2 (1st helper column):

=EXP(-C2)

and fill down.

In E2 (2nd helper column):

=1/SUMIF($B$2:$B$5,"<="&B2,$D$2:$D$5)

and fill down.

In F2 (3rd helper column):

=SUMIF($B$2:$B$5,">="&B2,$E$2:$E$5)

and fill down.

And finally in G2 (the final rating):

=(D2*F2-1)*0.15+C2

and fill down.

I would recommend putting the data in a table and using table formulas. The advantage of this is you don't need to edit formulas when the last row changes (in your example the last rows are all marked with $5).

The formulas are pretty much the same, except column names take the place of A1-style references.

In helper column 1, which I've named Calc1:

=EXP(-[@IniRating])

In helper column 2, which I've named Calc2:

=1/SUMIF([Placing],"<="&[@Placing],[Calc1])

In helper column 3, which I've named Calc3:

=SUMIF([Placing],">="&[@Placing],[Calc2])

And in the FinRating column:

=([@Calc1]*[@Calc3]-1)*0.15+[@IniRating]
user_50300880
Related