Solved: How to use LAMBDA with MAP to calculate a rolling 90-day weighted average from a filtered dynamic array

📌 The Problem Explained

In advanced financial modeling or inventory management, a standard average often fails to tell the whole story. You frequently need a Volume Weighted Average Price (VWAP) or a weighted trend over a specific lookback period—in this case, 90 days.

The challenge arises when your source data is dynamic. If you use a FILTER function to isolate a specific product or region, traditional AVERAGEIFS or SUMIFS functions become difficult to implement because they don’t always play well with “spilled” arrays. You need a way to iterate through every row of your filtered result, look back 90 days from that specific row’s date, and perform a weighted calculation on only the relevant subset of data.

💡 The Quick Solution

Assuming your filtered data (Dates in Col A, Prices in Col C, Volumes in Col D) is captured in a variable called filtered_data, use this formula structure:

=LET(
    f_data, FILTER(A2:D100, B2:B100="Product A"),
    f_dates, INDEX(f_data,,1),
    MAP(f_dates, LAMBDA(d,
        LET(
            window, FILTER(f_data, (INDEX(f_data,,1) <= d) * (INDEX(f_data,,1) > d-90)),
            w_val, INDEX(window,,3),
            w_weight, INDEX(window,,4),
            IFERROR(SUMPRODUCT(w_val, w_weight) / SUM(w_weight), 0)
        )
    ))
)

🛠️ Step-by-Step Breakdown

To implement this, you will use the Formulas > Insert Function logic within a LET wrapper to keep your calculation clean and efficient.

Step	Component	Purpose
1	LET(...)	Defines names for your filtered data so you don’t have to re-calculate the filter multiple times, improving performance.
2	f_data	The initial FILTER function that creates your dynamic array based on your criteria (e.g., Product Name).
3	MAP(f_dates, ...)	Iterates through every date in your filtered list, one by one.
4	LAMBDA(d, ...)	Creates a custom internal function where d represents the “Current Date” of the row being processed.
5	window	A secondary FILTER inside the LAMBDA that shrinks the dataset to only rows where the date is between d and d-90.
6	SUMPRODUCT / SUM	Calculates the weighted average: (Value1Weight1 + Value2Weight2) / Total Weights.

How to apply this to your sheet:

1. Define your Source: Ensure your data is in a Table (Insert > Table) for easier referencing.
2. Initialize LET: Start your formula with LET to define your “Filtered” range.
3. Deploy MAP: Use MAP on the date column of your filtered range. This tells Excel: “Do the following calculation for every date in this list.”
4. Create the Window: Inside the LAMBDA, use a nested FILTER. This is the secret sauce—it looks at the whole filtered set but only picks the rows falling in the 90-day lookback relative to the current row’s date.
5. Weighted Logic: Use SUMPRODUCT of your values and weights, divided by the SUM of weights.

⚠️ Common Mistakes to Avoid

• Date Formats: Ensure your date column is actually formatted as a Date. If Excel sees dates as text, the d-90 logic will return a #VALUE! error.
• Empty Windows: If a specific 90-day window has no transactions, the SUM divisor will be zero, resulting in a #DIV/0! error. Always wrap your final calculation in an IFERROR(..., 0) to maintain a clean dynamic array.
• Array Constraints: MAP cannot return an array within an array. Ensure your LAMBDA calculation results in a single value per row so that the final result can “spill” correctly.