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

1 Answer 1

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, 2021 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, 2021 at 19:55
  • In your file formula in column C looks working. Where is the problem?
    – Oleg_S
    Mar 17, 2021 at 7:59
  • When i apply the formula to another sheet it does not work
    – shawn
    Mar 17, 2021 at 8:33
  • docs.google.com/spreadsheets/d/…
    – shawn
    Mar 17, 2021 at 8:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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