I'm manually tracking games and wins every few days. Games goes up by 3-20 with each update, wins is about 50% of games. So data looks something like this:

Games   Wins    New Games  New Wins
2800    1531    10         6
2808    1535    8          4
2820    1542    12         7
2825    1544    5          2

I want to show a running win rate across the last X games. It should climb back up the past games a minimal number of rows until the sum of new games >= X, sum the wins for the same rows, and then divide the wins by the games. For example if X=20 on this data row 4 would use the bottom three rows and divide 13/25, while row 3 would only use the middle 2 rows and divide 11/20.

I could write a custom function, but it seems like it should be possible to do this with array functions and I want to learn more about them. I don't understand the finer details about what expands, what gets aggregated, and why. Searches are no help, all I find is endless redundant documentation and for-profit tutorials on the basics (yay SEO).

For example:

sum(offset(N2,0,0,10,1)) = sum(N2:N11)

But if I try to get an array of sums of different ranges:


I just get N2. I'm interested in a solution to this particular problem, but I'm more interested in documentation that explains how array formulas work so that things like this make sense.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.