0

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:

arrayformula(sum(offset(N2,0,0,sequence(10,1),1)))

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.

0

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.