How to Count How Many Runs Have the Maximum Length in Excel
Question details
Calculate the exact number of times a consecutive run of positive or negative values matches a previously determined maximum run length.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- A user has a dataset with consecutive positive and negative values and needs to find the frequency of streaks (runs) that equal the maximum streak length, separated by zeros or opposite sign values.
- Observed behavior
- The user requires an array formula that accurately evaluates the length of each run and counts how many of those runs equal the maximum length value.
Ensure you already know your maximum run length (e.g., stored in a specific cell like C3) and verify the exact range containing your dataset (e.g., A1:A15) before applying the array formula.
Use a SUMPRODUCT and FREQUENCY Array Formula
Apply a combined array formula using SUMPRODUCT, FREQUENCY, IF, and ROW to count the number of maximum length streaks.
The FREQUENCY function is excellent for grouping array sizes, and when combined with IF and ROW statements, it can identify streaks of consecutive positive numbers. By wrapping this in SUMPRODUCT, you can compare these run lengths against your maximum length value to get a total count.
Click on the cell where you want the final count to be displayed (for example, D3).
Input the formula: =SUMPRODUCT(--(FREQUENCY(IF(A1:A15>0,ROW(A1:A15)),IF(A1:A15<0,IF(A1:A15<>0,ROW(A1:A15))))=C3)). Replace A1:A15 with your actual data range, and C3 with the cell referencing your maximum run length.
If you are using an older version of Excel, you must confirm the formula by pressing Ctrl + Shift + Enter instead of just Enter. This will wrap the formula in curly brackets {}.
Count Consecutive Data Runs Free with WPS Spreadsheet
You can seamlessly process complex array formulas, including SUMPRODUCT and FREQUENCY combinations, to analyze data streaks using WPS Spreadsheet. It features an intuitive interface that makes advanced data analysis accessible for everyone.
- 1. Open your dataset: Launch WPS Spreadsheet and open the workbook containing your positive and negative values.
- 2. Enter the formula: Select an empty cell and input the SUMPRODUCT and FREQUENCY array formula tailored to your data range.
- 3. Calculate the result: Press Ctrl + Shift + Enter to execute the array formula and instantly count your maximum length runs.
Frequently Asked Questions
How do I count negative runs instead of positive runs?
Modify the logical operators in your formula. Change IF(A1:A15>0, ...) to IF(A1:A15<0, ...) and adjust the separator IF condition from A1:A15<0 to A1:A15>0.
Why is my array formula returning an error or zero?
In legacy versions of Excel, array formulas require you to press Ctrl + Shift + Enter to calculate correctly. If you just press Enter, the formula cannot evaluate the arrays, resulting in an error or an incorrect count of 0.
How do I calculate the maximum run length in the first place?
You can find the maximum streak length by using an array formula combining the MAX and FREQUENCY functions, such as =MAX(FREQUENCY(IF(A1:A15>0,ROW(A1:A15)),IF(A1:A15<0,ROW(A1:A15)))), before using the count formula.
