Function Problems

How to Detect Consecutive Peak-Dip-Peak Sequences in Excel

Maira Mehtab Sep 28, 2026 868 views

Question details

The user needs an Excel formula to identify and count specific data patterns consisting of a set of high values, followed by significantly lower values, and returning to high values.

Product: Excel
Device & OS: not provided
Scenario: Data analysis and pattern recognition involving thresholds and moving sequence windows.
Observed behavior: The user wants to extract the total count of patterns where 'X' consecutive values at or above a high threshold are followed by 'X' values lower by at least 'Z', followed by another 'X' high values.

Before you start

Ensure your dataset is organized in a single continuous column or row, and dedicate specific cells to define your variable thresholds (run length X, high threshold, and dip difference Z) for easier formula management.

Solution 1Recommended

Use Dynamic Array Functions (LET, SEQUENCE, SUMPRODUCT)

This method allows you to define thresholds as variables and evaluate overlapping or distinct rolling windows of data efficiently in modern Excel versions.

Using dynamic arrays makes it possible to evaluate complex sequential logic without relying on VBA macros. By combining LET for variable definition, SEQUENCE for window offsets, and SUMPRODUCT for boolean logic arrays, you can evaluate the entire sequence dynamically.

Define variable reference cells

Create reference cells for your parameters: set cell A1 for 'Run Length (X)', A2 for 'Upper Threshold', and A3 for 'Dip Difference (Z)'.

Identify the target data range

Locate the column containing your sequence values, for example, C2:C100.

Construct the LET function

Start your formula with the LET function to store these variables, keeping the main logic clean and readable.

Generate rolling windows

Use the SEQUENCE function inside your formula to generate rolling data windows of size 3X (representing the Peak + Dip + Peak length).

Apply boolean logic and calculate

Apply boolean logic arrays (e.g., peak_condition * dip_condition * peak_condition) wrapped in SUMPRODUCT to count how many windows match your exact peak-dip-peak criteria.

Excel Version Compatibility: Functions like LET and dynamic array SEQUENCE require Microsoft 365, Excel 2021, or compatible modern spreadsheet software like WPS Office.

Solution 2

Use Helper Columns for Traditional Excel Versions

If your spreadsheet software does not support dynamic arrays, you can break the sequence logic into helper columns.

Analyze Complex Data Patterns Using WPS Office

WPS Spreadsheet fully supports advanced dynamic array functions like LET, SEQUENCE, and SUMPRODUCT, making it incredibly easy to detect complex peak-dip-peak patterns without needing heavy macros.

1. Open your dataset: Launch WPS Spreadsheet and open the file containing your data.
2. Define your variables: Enter your sequence variables (X length, high threshold, dip drop Z) into empty reference cells.
3. Input the dynamic formula: Select an output cell and input your dynamic array formula using LET and SUMPRODUCT to evaluate the data range.
4. Calculate the result: Press Enter to instantly view the calculated count of your peak-dip-peak sequences.

Fully compatible with Microsoft Excel formulas and data structures.Supports modern dynamic array functions for advanced sequence detection.Lightweight and runs smoothly even with large data sets.

microsoft office alternative - wps office

Frequently Asked Questions

Can I detect overlapping sequences or just distinct ones?

It depends on your formula structure. Using a rolling SEQUENCE offset by 1 row will count overlapping patterns. To count distinct, non-overlapping sequences, you must offset the window by the total pattern length (3X) or filter the results.

Why does my LET formula return a #NAME? error?

The #NAME? error usually occurs if your version of Excel does not support the LET function (requires Microsoft 365 or Excel 2021) or if there is a typo in your defined variable names within the formula.

How do I handle missing or blank data points in the sequence?

You can modify your Boolean logic to use the ISNUMBER or ISBLANK functions, ensuring the formula ignores or appropriately flags empty cells before checking the threshold conditions against zero.

Excel & Data Problems