Excel 365 LET / LAMBDA / MAKEARRAY / MAP / REDUCE Dynamic-Array Formula Explainer and Reverse Translator

Background

An analyst received a shared financial model containing dynamic array formulas that transform projected figures across several steps.

Problem

Understanding intermediate calculations produced by nested LET variables before the final aggregation.

How to use

Set Mode to 'Explain a formula (expand)', enter `=LET(x, SEQUENCE(5), y, x*2, SUM(y))` into the formula field, and run the evaluation.

Outcome

The tool displays a 10-node AST, shows variable `x` bound to `[1;2;3;4;5]`, `y` bound to `[2;4;6;8;10]`, and evaluates the final sum to `30`.

Background

An operations manager tracks daily incoming ticket volumes and needs a single-cell spilled formula for a weekly smoothing metric.

Problem

Writing an error-free combination of MAP, SEQUENCE, and INDEX to calculate rolling windows without manual dragging.

How to use

Set Mode to 'Translate an intent (reverse)' and enter `7-day rolling average of daily revenue` into the plain-English intent field.

Outcome

Generates `=LET(x, Data#, n, 7, MAP(SEQUENCE(ROWS(x)), LAMBDA(i, IF(i < n, "", AVERAGE(INDEX(x, i - n + 1):INDEX(x, i))))))` alongside a successful syntax parse verification.