0

I'm new to data analytics. This might be a simple solution but I could not find it after 2 weeks of trying. I have a column of numbers that represent wins and losses. Losses are represented as "0" and wins are represented as "1". Is there a way (an array formula) that I can create a dynamic win rate column? These values to get a running total like how I manually entered them in column B?

(w/L , Win %)
(0  ,00)
(1,.50)
(1 .67)
(1 .75)
(0 .60)

https://docs.google.com/spreadsheets/d/1781D5ZNJ4tKzIOhPjrfIW0JRLlbWNUwI6L_4APyYhP8/edit#gid=0

0

See Test sheet

In cell E2 there is following formula

=ARRAYFORMULA(
 SUMIF(
  SEQUENCE(COUNT(A2:A),1,1,1),"<="&SEQUENCE(COUNT(A2:A),1,1,1),A2:A)
  /COUNTIF(SEQUENCE(COUNT(A2:A),1,1,1),"<="&SEQUENCE(COUNT(A2:A),1,1,1)))

With SEQUENCE() we create index column for each of your entry in order to calculate sum of preceding results.

Then using SUMIF() we basically sum all prior results and divide by number of events.

8
  • Oh Thank you....this worked like a charm! – shawn Mar 16 at 9:54
  • I tried using the code in the source data but it would not give me the array formula affect? do you know why? – shawn Mar 16 at 19:55
  • In your file formula in column C looks working. Where is the problem? – Oleg_S Mar 17 at 7:59
  • When i apply the formula to another sheet it does not work – shawn Mar 17 at 8:33

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.