res-sheets: implement modern dynamic-array and lambda functions (LET, LAMBDA, MAP, REDUCE, ARRAYFORMULA, SEQUENCE, etc.) #14

New issue

Open

opened 2026-07-27 18:55:22 +00:00 by agent · 0 comments

agent commented

2026-07-27 18:55:22 +00:00

Member

Background

A user provided a massive real-world lambda formula (extra/test-eval.txt) that factorizes integers using modern Google Sheets / Excel dynamic-array functions. When converted to .sheet.csv and run through res-sheets calc, the engine returns #VALUE! because the parser does not recognize the core functions the formula is built on.

Concrete test case

File: extra/test-eval.txt
Converted CSV: extra/test-eval.sheet.csv (input 100 in A1, formula in B1)
Expected: a prime-factorization string like 2^2*5^2
Actual: #VALUE!

Missing functions

The formula engine needs the following functions to evaluate the test case (and to reach parity with modern spreadsheet standards):

Function	Category	Notes
`LET`	binding	Local name bindings (`let(name, value, expr)`)
`LAMBDA`	functional	Anonymous function definition (`lambda(param, expr)`)
`MAP`	functional	Apply lambda over an array (`map(array, lambda)`)
`REDUCE`	functional	Fold/reduce over an array (`reduce(array, lambda, init)`)
`ARRAYFORMULA`	array	Lift scalar formula to array context
`SEQUENCE`	array	Generate numeric sequence (`sequence(rows, [cols], [start], [step])`)
`IFS`	logical	Already partially present; verify multi-branch works inside `LET`
`SWITCH`	logical	Already present; verify works inside `LET`

Additionally, the formula uses GCD, FLOOR, LOG, TEXTJOIN, MOD, FIND, MID, SUBSTITUTE, ISERROR, ROUND, PRODUCT, SQRT, LEFT, RIGHT, CONCAT, IF, PI, RADIANS, DEGREES, SIN, COS, TAN, ASIN, ACOS, ATAN, ATAN2, SINH, COSH, TANH, EXP, LN, LOG10, EVEN, ODD, FACT, COMBIN, PERMUT, SUMSQ, QUOTIENT, CEILING, MROUND, ROUNDDOWN, ROUNDUP, RAND, RANDBETWEEN, MEDIAN, MODE.SNGL, STDEV.P, STDEV.S, VAR.P, VAR.S, LARGE, SMALL, PERCENTILE.INC, QUARTILE.INC, COUNTA, COUNTBLANK, COUNTIF, COUNTIFS, SUMIF, SUMIFS, AVERAGEIF, AVERAGEIFS, MAXIFS, MINIFS, AND, OR, NOT, XOR, TRUE, FALSE, IFERROR, IFNA, LEN, UPPER, LOWER, PROPER, TRIM, REPT, CHAR, CODE, EXACT, VALUE, T, TEXT, DATE, TODAY, NOW, YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, WEEKDAY, EDATE, EOMONTH, DATEDIF, DAYS, NETWORKDAYS, WORKDAY, ROW, COLUMN, ROWS, COLUMNS, CHOOSE, INDEX, MATCH, VLOOKUP, HLOOKUP — most of which are already implemented. The blocker is specifically the functional / array layer.

Acceptance criteria

res-sheets calc extra/test-eval.sheet.csv produces a meaningful result (not #VALUE!) for input 100.
Each new function has at least one unit test in lib/sheets/formula_test.go.
The implementation follows the existing evalCall dispatch pattern in formula_eval.go.

extra/test-eval.txt — the original formula
extra/test-eval.sheet.csv — the converted CSV test fixture
docs/extra/extra.md — res-sheets documentation

## Background A user provided a massive real-world lambda formula (`extra/test-eval.txt`) that factorizes integers using modern Google Sheets / Excel dynamic-array functions. When converted to `.sheet.csv` and run through `res-sheets calc`, the engine returns `#VALUE!` because the parser does not recognize the core functions the formula is built on. ## Concrete test case File: `extra/test-eval.txt` Converted CSV: `extra/test-eval.sheet.csv` (input `100` in A1, formula in B1) Expected: a prime-factorization string like `2^2*5^2` Actual: `#VALUE!` ## Missing functions The formula engine needs the following functions to evaluate the test case (and to reach parity with modern spreadsheet standards): | Function | Category | Notes | |----------|----------|-------| | `LET` | binding | Local name bindings (`let(name, value, expr)`) | | `LAMBDA` | functional | Anonymous function definition (`lambda(param, expr)`) | | `MAP` | functional | Apply lambda over an array (`map(array, lambda)`) | | `REDUCE` | functional | Fold/reduce over an array (`reduce(array, lambda, init)`) | | `ARRAYFORMULA` | array | Lift scalar formula to array context | | `SEQUENCE` | array | Generate numeric sequence (`sequence(rows, [cols], [start], [step])`) | | `IFS` | logical | Already partially present; verify multi-branch works inside `LET` | | `SWITCH` | logical | Already present; verify works inside `LET` | Additionally, the formula uses `GCD`, `FLOOR`, `LOG`, `TEXTJOIN`, `MOD`, `FIND`, `MID`, `SUBSTITUTE`, `ISERROR`, `ROUND`, `PRODUCT`, `SQRT`, `LEFT`, `RIGHT`, `CONCAT`, `IF`, `PI`, `RADIANS`, `DEGREES`, `SIN`, `COS`, `TAN`, `ASIN`, `ACOS`, `ATAN`, `ATAN2`, `SINH`, `COSH`, `TANH`, `EXP`, `LN`, `LOG10`, `EVEN`, `ODD`, `FACT`, `COMBIN`, `PERMUT`, `SUMSQ`, `QUOTIENT`, `CEILING`, `MROUND`, `ROUNDDOWN`, `ROUNDUP`, `RAND`, `RANDBETWEEN`, `MEDIAN`, `MODE.SNGL`, `STDEV.P`, `STDEV.S`, `VAR.P`, `VAR.S`, `LARGE`, `SMALL`, `PERCENTILE.INC`, `QUARTILE.INC`, `COUNTA`, `COUNTBLANK`, `COUNTIF`, `COUNTIFS`, `SUMIF`, `SUMIFS`, `AVERAGEIF`, `AVERAGEIFS`, `MAXIFS`, `MINIFS`, `AND`, `OR`, `NOT`, `XOR`, `TRUE`, `FALSE`, `IFERROR`, `IFNA`, `LEN`, `UPPER`, `LOWER`, `PROPER`, `TRIM`, `REPT`, `CHAR`, `CODE`, `EXACT`, `VALUE`, `T`, `TEXT`, `DATE`, `TODAY`, `NOW`, `YEAR`, `MONTH`, `DAY`, `HOUR`, `MINUTE`, `SECOND`, `WEEKDAY`, `EDATE`, `EOMONTH`, `DATEDIF`, `DAYS`, `NETWORKDAYS`, `WORKDAY`, `ROW`, `COLUMN`, `ROWS`, `COLUMNS`, `CHOOSE`, `INDEX`, `MATCH`, `VLOOKUP`, `HLOOKUP` — most of which are already implemented. The blocker is specifically the **functional / array** layer. ## Acceptance criteria 1. `res-sheets calc extra/test-eval.sheet.csv` produces a meaningful result (not `#VALUE!`) for input `100`. 2. Each new function has at least one unit test in `lib/sheets/formula_test.go`. 3. The implementation follows the existing `evalCall` dispatch pattern in `formula_eval.go`. ## Related - `extra/test-eval.txt` — the original formula - `extra/test-eval.sheet.csv` — the converted CSV test fixture - `docs/extra/extra.md` — res-sheets documentation

agent referenced this issue

2026-07-27 19:43:41 +00:00

res-sheets: embed core/lib/edit for formula editing (scroll, undo, syntax highlight) #16

agent added the

proj-extra-sheets

tier-3-hard

labels

2026-07-27 19:48:16 +00:00

agent referenced this issue

2026-08-03 17:54:49 +00:00

residual workspace audit — 2026-08-03 #19