Function Problems

Find the Maximum Sum of Seven Consecutive Values in Excel

Olivia MillerOlivia Miller Nov 18, 2026 868 views

Question details

Find the largest sum of seven consecutive values in a 1,000-element vector and return its starting position.

Find the Maximum Sum of Seven Consecutive Values in Excel
Product
Microsoft Excel
Device & OS
not provided
Scenario
A user wants to compare every seven-value window in a column of 1,000 numeric values.
Observed behavior
The desired result is the starting position and total of the seven-value window with the largest sum.
Before you start

Place the 1,000 numeric values vertically in cells A1:A1000, with no header in A1. If your data is elsewhere, adjust the cell references and interpret the position relative to the first value.

Solution 1Recommended

Use a Sliding-Window Formula in Excel

Calculate the maximum and its starting position in one formula without repeatedly summing each seven-cell range.

The formula calculates the first total, then updates it by adding the incoming value and subtracting the outgoing value. It includes the first window in the comparison.

1
Enter the formula

In an empty cell with an empty cell to its right, enter =LET(v,A1:A1000,init,SUM(TAKE(v,7)),diff,DROP(v,7)-DROP(v,-7),totals,VSTACK(init,SCAN(init,diff,LAMBDA(a,b,a+b))),maxTotal,MAX(totals),HSTACK(XMATCH(maxTotal,totals,0),maxTotal)).

2
Read the result

Press Enter. The formula spills the starting position into the formula cell and the maximum total into the cell to its right.

Use a Sliding-Window Formula in Excel
Function availability and ties: This formula requires an Excel version that supports LET, TAKE, DROP, SCAN, LAMBDA, VSTACK, HSTACK, and XMATCH. If multiple windows share the maximum, it returns the first starting position.
Calculate in WPS Spreadsheet

Find the Largest Seven-Value Sum in WPS Spreadsheet

Use a helper column in WPS Spreadsheet to calculate each seven-value total, then find the maximum and its starting position.

  1. 1. Open your data: Open WPS Spreadsheet and place the 1,000 numeric values in A1:A1000.
  2. 2. Calculate each total: Enter =SUM(A1:A7) in B1, then drag the fill handle down through B994.
  3. 3. Find the maximum: Enter =MAX(B1:B994) in D1 to display the largest total.
  4. 4. Find its position: Enter =MATCH(D1,B1:B994,0) in E1 to display the first starting position with that total.
Compare consecutive seven-value sums with familiar formulasOpen and save Microsoft Excel XLSX filesUse SUM, MAX, and MATCH to find the resultWork with a familiar spreadsheet interface
microsoft office alternative - wps office

Frequently Asked Questions

How many seven-value windows are in 1,000 values?

There are 994 windows. The first starts at position 1 and the last starts at position 994.

What happens if two windows have the same maximum sum?

Both XMATCH in the sliding-window formula and MATCH in the helper-column method return the first matching starting position.

Why does the sliding-window formula include the initial sum separately?

SCAN calculates totals after the first window. VSTACK adds the initial seven-value sum so it is also included when MAX compares the windows.

Can I use the helper-column method if my data starts below a header?

Yes. For values in A2:A1001, enter =SUM(A2:A8) in a helper cell and fill it through 994 rows. MATCH returns a position relative to the first value; add 1 to that position to get the worksheet row number.