Function Problems

Find the Last Positive Position in Each Excel Sequence

Huda QurayshiHuda Qurayshi Nov 18, 2026 869 views

Question details

Find the relative row position of the last positive value in each sequence and spill the results into a dynamic array.

Find the Last Positive Position in Each Excel Sequence
Product
Microsoft Excel 365
Device & OS
not provided
Scenario
A column contains numeric values, and the user wants the positions where a positive sequence ends before zero or a negative value.
Observed behavior
The desired result is a spilled list of positions relative to the start of the range, including a positive value in the final row if it is treated as a sequence ending.
Before you start

Check that your data range contains at least two numeric cells and that Excel supports LET, FILTER, DROP, and VSTACK. Leave enough empty cells below the formula for the results to spill.

Solution 1Recommended

Use a dynamic array formula to return sequence-ending positions

Use this Excel 365 formula to spill the relative position of every positive value followed by a nonpositive value.

1
Select an empty cell

Click an empty cell outside the data range, such as C2, and ensure the cells below it are clear.

2
Enter the formula

Enter =LET(values,A2:A11,FILTER(ROW(values)-ROW(A2)+1,(values>0)*(VSTACK(DROP(values,1),0)<=0))) and press Enter. Replace A2:A11 and A2 with your range and its first cell.

3
Check the spilled positions

Review the results below the formula cell. Position 1 represents A2, position 2 represents A3, and so on.

Use a dynamic array formula to return sequence-ending positions
Final-row and no-match behavior: The appended 0 makes a positive final value count as a sequence ending. Replace that final 0 with 1 to exclude the final row. If no values match, FILTER returns a #CALC! error; add "No sequence endings" as its third argument to display a message instead.
Free Microsoft Office alternative

Work with Positive Sequences in WPS Spreadsheet

WPS Office is a free, lightweight alternative with a familiar spreadsheet interface. Open your Excel workbook and use a helper column to identify positive sequence endings; advanced Excel formula compatibility can vary.

Use a helper column and filters to review positive sequence endingsOpen and save Microsoft Excel XLSX and XLS workbooksNavigate a familiar spreadsheet interface for an easier transitionUse free spreadsheet tools in a lightweight office suite
QA img-9

Frequently Asked Questions

Does the formula return worksheet row numbers or relative positions?

It returns positions relative to the range: for A2:A11, A2 is position 1. To return worksheet row numbers instead, replace ROW(values)-ROW(A2)+1 with ROW(values).

How do I exclude a positive value in the final row?

In VSTACK(DROP(values,1),0), replace the final 0 with 1. The positive boundary prevents the last value from being treated as followed by a nonpositive value.

Why does the formula show a #SPILL! error?

Excel needs empty cells below the formula to display the results. Clear any obstructing cells in the indicated spill range, then check the formula again.

What happens if there are no sequence endings?

FILTER returns a #CALC! error when there are no matches. Add a third argument, such as "No sequence endings", to FILTER to display that text instead.