Create intermediate Works with: Any AI

Running Totals and Moving Averages

Table output Has example

Track cumulative values and trends.

The Open buttons send the prompt text as currently shown below to that site only when you click. Fill in the placeholders first.

Write formulas for running totals and moving averages. App: [SPREADSHEET_APP]. Data layout (date in A, value in B, optional group in C): [DATA_LAYOUT]. Window sizes needed (for example 7 days, 3 months): [WINDOW_SIZES]. Sample data (fake): [SAMPLE_DATA].

Provide a table: Need | Formula in row 5 | Explanation | Fill-down notes.
Cover: running total (=SUM($B$2:B5) or =B5+C4 pattern); running total reset by group (SUMIFS on the group up to the current row, or an IF comparing with the previous row); moving average of the last N rows (=AVERAGE(B3:B5) pattern) and the issue with the first N-1 rows; moving average by date window (AVERAGEIFS with date bounds); cumulative percent of total; year-to-date total; running balance with opening balance; month-to-date; and rolling 12-month sum.

Add: how to handle missing dates (gaps) so a "7-day average" means 7 calendar days, not 7 rows; how to blank out incomplete windows; a chart suggestion (line chart with both series); common mistakes (relative references that break after sorting, using AVERAGE on a range with blanks); and test cases with expected values for the first eight rows of my sample data.

Fill in before use

Optional: type your details and the prompt above updates. Nothing is saved or sent.

Replace each bracketed placeholder with your own details, then paste into Any AI or another assistant.

Row 5 formulas (date in A, value in B)
Running total: =SUM($B$2:B5)
Running total by group: =SUMIFS($B$2:B5, $C$2:C5, C5)
3-row moving average: =IF(ROW()<4, "", AVERAGE(B3:B5))
7-calendar-day average (gaps ok): =AVERAGEIFS($B:$B, $A:$A, ">"&A5-7, $A:$A, "<="&A5)
Sample: values 10, 20, 30, 40 give running totals 10, 30, 60, 100 and a 3-row average 20 at row 4 and 30 at row 5.
Mistakes: relative references broken by sorting; AVERAGE over blanks. For the first rows where the window is incomplete, return a blank instead of a misleading average. Chart both series as lines. Check the first eight rows against a hand calculation before filling the formula down.

• running-total