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.
