Find the Last Positive Position in Each Excel Sequence
Question details
Find the relative row position of the last positive value in each sequence and spill the results into a dynamic array.

- 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.
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.
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.
Click an empty cell outside the data range, such as C2, and ensure the cells below it are clear.
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.
Review the results below the formula cell. Position 1 represents A2, position 2 represents A3, and so on.

Mark sequence endings with a helper column
Use a helper column if you do not have the required dynamic array functions or prefer to filter the results manually.
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.

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.




