Share via

Calculating a Winning Streak

Anonymous
2012-09-14T11:28:42+00:00

I would like to calculate a winning streak based on having a "Yes" in column A.  In so doing, Excel would calculate the highest number of "Yes" responses that occurred, in a row.

As an example, suppose the data was as follows

Column A

  1. Yes

2.  Yes

3.  Yes

4.  No

5.  Yes

6.  Yes

7.  Yes

8.  Yes

9.  No

10.  No

11.  Yes

In the above example, the highest winning streak would be 4 (cells A5 through A8).

What kind of formula is required to perform this kind of calculation?

On the flip side, how woudl a losing streak, desginated by the highest number of consecutive "No" responses, be represented?

Jeffrey

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
  1. Anonymous
    2012-09-14T11:50:51+00:00

    Hi,

    You can do it with this ARRAY formula, see below on how to enter it

    =MAX(FREQUENCY(IF(A1:A20="Yes",ROW(A1:A20)),IF(A1:A20<>"Yes",ROW(A1:A20))))

    This is an array formula which must be entered by pressing CTRL+Shift+Enter

    and not just Enter. If you do it correctly then Excel will put curly brackets

    around the formula {}. You can't type these yourself. If you edit the formula

    you must enter it again with CTRL+Shift+Enter.

    1 person found this answer helpful.
    0 comments

5 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2012-09-15T15:37:28+00:00

    Yeah, I liked Mike's method better than mine also!

    0 comments
  2. Anonymous
    2012-09-14T13:57:05+00:00

    Thank you Mike.  The one formula answered the question.

    You're welcome and thanks for the feedback.

    0 comments
  3. Anonymous
    2012-09-14T13:55:57+00:00

    Thank you Mike.  The one formula answered the question.

    0 comments
  4. Anonymous
    2012-09-14T11:38:55+00:00

    For the winning streak:  In cell B1, put this formula:  =IF(A1="Yes",1,0)

    Then in B2 put this formula:

    =IF(A2="No",0,B1+1)

    And fill that formula down to the bottom of your list of yes/no entries.  To get the longest winning streak you can use =MAX(B:B)

    To work with the losing streaks, just change "Yes" to "No" in the formulas.

    0 comments