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

Two-way Excel 365 dynamic-array workbench: expand LET/LAMBDA/MAP/SCAN/REDUCE/BYROW/BYCOL/MAKEARRAY formulas into an AST with step-traced evaluation (LET bindings, lambda first calls, intermediate matrices, optional A1-anchored sample data), and translate plain-English intents like "7-day rolling average" or "running total" into working LAMBDA/MAP/SCAN formulas.

> Canonical page: https://elysiatools.com/en/tools/excel-365-let-lambda-makearray-map-reduce-dynamic-array-formula-explainer-and-reverse-translator

- **Category:** Text Processing

- **Keywords:** excel 365 formula, let function, lambda, makearray, map function, reduce, scan, byrow, bycol, dynamic arrays, formula explainer, rolling average formula, running total, formula translator, spreadsheet

## Overview

Forward mode parses the formula (Excel operator precedence % > ^ > * / > +− > & > comparisons) into an AST and evaluates it with Microsoft 365 semantics: LET binds left-to-right, LAMBDA builds closures, MAP is element-wise, SCAN keeps a running accumulator per element, REDUCE returns only the final accumulator, BYROW/BYCOL collapse rows/columns (eta shorthand BYROW(a, SUM) ≡ LAMBDA(r, SUM(r))), MAKEARRAY builds matrices from 1-based (row, col). Modelled builtins: SUM, AVERAGE, MIN, MAX, COUNT, ABS, ROUND, SQRT, POWER, IF, LEN, CONCAT, LOWER, UPPER, TRIM, ISNUMBER, ROWS, COLUMNS, SEQUENCE, TRANSPOSE, INDEX, DROP, TAKE, VSTACK, HSTACK, SORTBY, SUMPRODUCT, MMULT. Range references (A1, A1:B3) resolve against the pasted sample grid anchored at A1. Reverse mode matches keyword templates (rolling/moving average, running total/max, percent change, row/column aggregates, EMA, last non-empty, Fibonacci, matrix product, clip/clamp, trim text) and re-parses every generated formula as a self-check.

## Inputs

- **Mode** (select)
- **Formula (with or without =)** (text): =LET(x, SEQUENCE(5), y, x*2, SUM(y))
- **Plain-English intent** (text): 7-day rolling average of daily revenue
- **Sample data grid (A1-anchored, optional)** (textarea): 10,15 12,18 14,21

## When to use

- When debugging complex nested LET and LAMBDA formulas to inspect intermediate variable states and matrix dimensions.
- When converting plain-English calculations like rolling averages or running totals into dynamic array formulas.
- When evaluating dynamic array logic against sample tabular data without opening Microsoft Excel.

## How it works

- Select either expand mode to inspect an existing formula or reverse mode to generate one from a text intent.
- For expand mode, input the formula and optionally paste comma-separated or tabbed values into the A1-anchored sample grid to resolve range references.
- For reverse mode, enter your computational goal, such as calculating an exponential moving average or finding percent change across rows.
- The tool parses the syntax using Excel 365 precedence rules, executes step-by-step lambda closures or template matching, and outputs an interactive AST trace or validated formula.

## Use cases

- Deconstructing legacy or shared dynamic array formulas to audit logic, step through LET assignments, and verify matrix shapes.
- Generating multi-row running totals and cumulative sums using SCAN without writing manual cell-by-cell copy-down formulas.
- Constructing sliding window metrics such as 7-day or 30-day moving averages using MAP, SEQUENCE, and INDEX.

## Frequently asked questions

### Which Excel dynamic array helper functions are supported?

The tool supports LET, LAMBDA, MAP, SCAN, REDUCE, BYROW, BYCOL, and MAKEARRAY, including eta-reduction shorthands like BYROW(range, SUM).

### How does the optional sample data grid work?

Pasting tabular data anchors the top-left cell to A1, allowing cell coordinates and range references such as A1:B10 to resolve against real values during AST evaluation.

### What happens during reverse translation mode?

The engine matches analytical keywords to formula templates, generates the corresponding dynamic array formula, and automatically re-parses it to verify syntax validity.

### Does the parser follow standard Excel operator precedence?

Yes, it models standard Excel operator precedence: percent (%), exponentiation (^), multiplication and division (*, /), addition and subtraction (+, -), concatenation (&), and comparison operators.

### Can the tool evaluate custom closures created inside LAMBDA?

Yes, the evaluator creates lexical closures for LAMBDA definitions and traces initial calls, parameter bindings, and intermediate array outputs.

## Related tools

- [CSV to Excel Converter](https://elysiatools.com/en/tools/csv-to-excel): Convert CSV data to Excel format with customizable parsing and formatting options
- [Excel to CSV Converter](https://elysiatools.com/en/tools/excel-to-csv): Convert Excel file to CSV format, extracting data from the first worksheet
- [Excel to HTML Table Converter](https://elysiatools.com/en/tools/excel-to-html): Convert Excel file to HTML table format, extracting data from the first worksheet
- [Excel to JSON Converter](https://elysiatools.com/en/tools/excel-to-json): Convert Excel file to JSON format, extracting data from the first worksheet
- [Excel to Markdown Converter](https://elysiatools.com/en/tools/excel-to-markdown): Convert Excel file to Markdown table format, extracting data from the first worksheet
- [Excel to XML Converter](https://elysiatools.com/en/tools/excel-to-xml): Convert Excel file to XML format, extracting data from the first worksheet
- [JSON-LD Generator from CSV](https://elysiatools.com/en/tools/json-ld-generator-from-csv): Turn CSV or Excel rows into Schema.org JSON-LD for articles, products, or events, with validation-ready output for SEO workflows
- [PDF to Excel](https://elysiatools.com/en/tools/pdf-to-excel): Extract tabular data from PDF files and convert to Excel spreadsheets with customizable parsing options

## Samples

- [Web Image Processing Python Samples](https://elysiatools.com/en/samples/web-image-processing-python): Web Python image processing examples using PIL/Pillow including reading, saving, resizing, and format conversion
- [XLS Samples](https://elysiatools.com/en/samples/xls-samples): Sample legacy Excel (XLS) files for binary spreadsheet parsing and import tests
- [XLSX Samples](https://elysiatools.com/en/samples/xlsx-samples): Sample XLSX spreadsheets for worksheet parsing and data extraction testing
- [AWS S3 Lambda Samples](https://elysiatools.com/en/samples/aws-s3-lambda): AWS S3 and Lambda serverless architecture examples including S3 triggers, Lambda functions, and serverless application patterns