SMA_CUMSUM

This function computes a rolling simple moving average by subtracting offset cumulative sums.

If c_t = \sum_{i=1}^{t} x_i, then the average over a window of length w ending at index t is:

\text{SMA}_t = \frac{c_t - c_{t-w}}{w}

This approach avoids repeated summation inside each window and is efficient for long series.

Excel Usage

=SMA_CUMSUM(data, window)

data (list[list], required): Time-series observations as a 2D range (data points).
window (int, optional, default: 3): Number of observations per averaging window (points).

Returns (list[list]): Column vector of rolling simple moving-average values using full-window positions.

Example 1: Rolling SMA from cumulative sums

Inputs:

data					window
1	2	3	4	5	3

Excel formula:

=SMA_CUMSUM({1,2,3,4,5}, 3)

Expected output:

Result
2
3
4

Example 2: Two-point rolling average

Inputs:

data				window
5	7	9	11	2

Excel formula:

=SMA_CUMSUM({5,7,9,11}, 2)

Expected output:

Result
6
8
10

Example 3: Window length one returns original values

Inputs:

data			window
4	6	8	1

Excel formula:

=SMA_CUMSUM({4,6,8}, 1)

Expected output:

Result
4
6
8

Example 4: Scalar input with unit window

Inputs:

data	window
12	1

Excel formula:

=SMA_CUMSUM(12, 1)

Expected output:

12

Python Code

import numpy as np

def sma_cumsum(data, window=3):
    """
    Compute a simple moving average using cumulative-sum differencing.

    See: https://numpy.org/doc/stable/reference/generated/numpy.cumsum.html

    This example function is provided as-is without any representation of accuracy.

    Args:
        data (list[list]): Time-series observations as a 2D range (data points).
        window (int, optional): Number of observations per averaging window (points). Default is 3.

    Returns:
        list[list]: Column vector of rolling simple moving-average values using full-window positions.
    """
    try:
        def to2d(x):
            return [[x]] if not isinstance(x, list) else x

        data = to2d(data)

        if not isinstance(data, list) or not all(isinstance(row, list) for row in data):
            return "Error: Invalid input - data must be a 2D list"

        if window < 1:
            return "Error: window must be at least 1"

        values = []
        for row in data:
            for item in row:
                try:
                    values.append(float(item))
                except (TypeError, ValueError):
                    continue

        if not values:
            return "Error: data must contain at least one numeric value"

        if len(values) < window:
            return "Error: data length must be at least as large as window"

        arr = np.asarray(values, dtype=float)
        csum = np.cumsum(np.insert(arr, 0, 0.0))
        means = (csum[int(window):] - csum[:-int(window)]) / float(window)
        return [[float(x)] for x in np.asarray(means, dtype=float).tolist()]
    except Exception as e:
        return f"Error: {str(e)}"

Online Calculator

data *

Time-series observations as a 2D range (data points).

window

Number of observations per averaging window (points).