Elysia Tools

Navigation

Text Processing

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

Written by the Elysia Tools team · Last updated:2026-09-12

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.

Details

What this tool helps you do

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.

Execution

Run this tool

Fill in the form, run the tool, and review the result in one place.

Samples

Examples that match this tool

Related

Continue with connected tools and hubs

Elysia Tools

Browse tools, examples, visual explainers, and topic guides from one clear workspace.

HomeTools3201Samples593Visualizations465Viewers103Hubs233Tags333

Top Categories

Images, Audio & Video657

Image, audio, and video processing, conversion, and optimization tools

Math, Date & Finance644

Calculators, numerics, date logic, statistics, and finance tools

Design & Color297

Color, layout, graphics, visual styling, and design helper tools

Text & Writing209

Writing, cleanup, formatting, extraction, and text analysis tools

Developer & Web193

Developer utilities, networking, web debugging, and automation helpers

Conversion & Encoding175

Format, file, archive, unit, and encoding conversion tools

Hubs

FLAC/WAV Source Screening and Evidence HandoffSelf-Hosted Giveaway Widget Review and Embed HandoffStripe Promo Code Batch Review and Import PreflightBatch Photo Culling Review and Editing Shortlist HandoffUI Screenshot Structure Review and Development Handoff
Language
ZHESPTRUFRDEEN

Tool usage guide

Learn when to use this tool, what it supports, and how real users apply it.

Key facts

Category
Text Processing
Input types
select, text, textarea
Output type
html
Sample coverage
4
API ready
Yes

Overview

The Excel 365 Dynamic-Array Formula Explainer and Reverse Translator is a dual-mode workbench designed to inspect and generate modern spreadsheet formulas. In expand mode, it deconstructs complex nested LET, LAMBDA, MAP, SCAN, and REDUCE expressions into an abstract syntax tree with step-traced variable bindings and evaluated matrix outputs. In reverse mode, it converts plain-English analytical requirements into validated, syntactically correct dynamic array formulas.

When to use

© 2026 Elysia Tools. All rights reserved.

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

    1. 1Select either expand mode to inspect an existing formula or reverse mode to generate one from a text intent.
    2. 2For expand mode, input the formula and optionally paste comma-separated or tabbed values into the A1-anchored sample grid to resolve range references.
    3. 3For reverse mode, enter your computational goal, such as calculating an exponential moving average or finding percent change across rows.
    4. 4The 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.

    Examples

    1. Tracing LET Bindings and Array Arithmetic

    Financial Analyst

    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`.

    2. Generating a 7-Day Rolling Average Formula

    Operations Manager

    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.

    FAQ

    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.

    Python

    Web Image Processing Python Samples

    Web Python image processing examples using PIL/Pillow including reading, saving, resizing, and format conversion

    Data

    XLS Samples

    Sample legacy Excel (XLS) files for binary spreadsheet parsing and import tests

    Data

    XLSX Samples

    Sample XLSX spreadsheets for worksheet parsing and data extraction testing

    Cloud Services

    AWS S3 Lambda Samples

    AWS S3 and Lambda serverless architecture examples including S3 triggers, Lambda functions, and serverless application patterns

    Format Conversion

    CSV to Excel Converter

    Convert CSV data to Excel format with customizable parsing and formatting options

    Format Conversion

    Excel to CSV Converter

    Convert Excel file to CSV format, extracting data from the first worksheet

    Format Conversion

    Excel to HTML Table Converter

    Convert Excel file to HTML table format, extracting data from the first worksheet

    Format Conversion

    Excel to JSON Converter

    Convert Excel file to JSON format, extracting data from the first worksheet

    Hub

    Excel Formula Engineering

    Treat formulas as code with a full toolchain — build them from common functions and operators, evaluate them against real cell values with the references explained, expand and reverse-translate Excel 365 dynamic-array formulas like LET, LAMBDA, MAP, and REDUCE, and graduate anything repeated into a generated VBA macro.

    Docs
    Pricing
    About
    Terms of Service
    Privacy Policy
    Cookies
    Contact