Function Problems

Find the Longest Consecutive Run of Ones in Excel

Ayan MasoodAyan Masood Nov 18, 2026 868 views

Question details

Calculate the maximum number of consecutive 1 values in a column of zeros and ones.

Find the Longest Consecutive Run of Ones in Excel
Product
Microsoft Excel
Device & OS
not provided
Scenario
A column contains binary values, and the user wants the length of the longest uninterrupted sequence of 1s.
Observed behavior
For the sample sequence, the longest run contains four consecutive 1s.
Before you start

Place the zeros and ones in a single column, such as A1:A15, and identify an empty column for a helper calculation. Adjust the cell references if your data occupies a different range.

Solution 1Recommended

Count consecutive ones with a helper column

Use a running count and MAX to find the longest run in Excel without requiring dynamic array functions.

1
Start the count

In cell B1, enter =IF(A1=1,1,0) to count the first value.

2
Continue the count

In cell B2, enter =IF(A2=1,B1+1,0). This adds one for a 1 and resets the count for any other value.

3
Fill down

Select B2 and drag its fill handle down through B15 to calculate the run length at each row.

4
Find the maximum

In an empty cell, enter =MAX(B1:B15). For the sample data, the result is 4.

Count consecutive ones with a helper column
Runs at the end are included: The running count includes a run that reaches the last row, even if no zero follows it.
Calculate runs in WPS Spreadsheets

Find Consecutive Ones in WPS Spreadsheets

Use a helper column and the MAX function in WPS Spreadsheets to calculate the longest run of 1s in your data.

  1. 1. Open your data: Open WPS Spreadsheets and load your workbook, or enter the zeros and ones in A1:A15.
  2. 2. Start the helper column: In B1, enter =IF(A1=1,1,0).
  3. 3. Fill the running count: In B2, enter =IF(A2=1,B1+1,0), then drag the fill handle down through B15.
  4. 4. Calculate the longest run: In an empty cell, enter =MAX(B1:B15) to display the longest consecutive run.
Calculate consecutive runs with familiar IF and MAX formulasOpen and edit Microsoft Excel XLS and XLSX filesUse a familiar spreadsheet interfaceWork with a free, lightweight office suite
microsoft office alternative - wps office

Frequently Asked Questions

What happens if the column contains no ones?

The helper column contains zeros, so =MAX(B1:B15) returns 0.

Does the formula count a run at the end of the column?

Yes. The helper-column and SCAN methods both count a run that continues through the last cell in the selected range.

How do I use the helper-column method with a longer range?

Drag the formula in B2 down to the last data row, then update the MAX range. For example, use =MAX(B1:B100) for data in A1:A100.

What happens if there is a blank cell between ones?

The IF formula treats the blank as a break because it is not equal to 1. Enter a 1 in that cell if it should be part of the run.