Formula Validation

Real workbook validation for Excel-style recalculation in .NET .

FormulaEngineer is validated against workbook-style formula scenarios, not only simple demo formulas. These validations verify that FormulaEngineer can identify formula cells, separate cached values from evaluated values, and return Excel-style results for supported formulas.

Get FormulaEngineer on NuGet

Validated with lookup formulas, dynamic arrays, structured references, text formulas, date formulas, sorting, filtering, and Excel error results.

Real workbook scenarios

Validated with workbook-style formulas using surrounding data, references, dependent cells, and cached values.

Excel-style result handling

Returns numbers, text, dates, booleans, blanks, spilled arrays, and Excel error values as formula results.

Server-side recalculation

Focused on recalculating workbook logic when cached values are missing, stale, or workbook inputs change in code.

Bring your own workbook

The best test is your own workbook.

Try FormulaEngineer with a real file that contains the formulas your application depends on: lookups, date logic, conditional aggregation, text formulas, structured references, cross-sheet references, errors, and formulas that depend on changed inputs.

If your workflow needs formula recalculation on a server, in an API, in a background job, or inside a Linux/container environment where Excel and COM automation are not practical, FormulaEngineer is built for that scenario.

Contact for compatibility questions

Formula evaluation is more than returning a number

A real Excel workbook contains normal values, blank cells, formula cells, cached formula results, dynamic arrays, structured references, text results, dates, booleans, and Excel error values such as #N/A or #VALUE!.

FormulaEngineer reads the workbook, identifies which cells are formulas, separates the cached value from the evaluated value, and returns the calculated result using Excel-style behavior for supported functions.

Excel errors are also valid formula results. A correct formula engine should preserve them as first-class results, not treat them as crashes or unreadable cells.

Reading cached values is not recalculation

Reading a cached value from an .xlsx file is not the same as recalculating the formula.

A cached value is simply the last result saved by Excel. It may be missing, stale, or wrong after inputs change in code. Server-side applications often need to recalculate formulas without opening Excel.

FormulaEngineer focuses on actual formula evaluation, so a .NET application can calculate workbook logic directly.

Validated formula categories

Lookup formulas

VLOOKUP, HLOOKUP, LOOKUP, XLOOKUP, INDEX, MATCH, multi-criteria lookup, case-sensitive lookup, closest-match lookup, and two-way matrix lookup.

Dynamic array formulas

SORT, UNIQUE, FILTER, SEQUENCE, spilled arrays, horizontal sorting, multi-level sorting, sorted unique values, and sorted filtered results.

Text formulas

SEARCH, FIND, REPLACE, SUBSTITUTE, EXACT, TEXT, IFERROR, and text/error combinations.

Date and time formulas

DATE, DATEVALUE, TEXT date formatting, WORKDAY, SEQUENCE-generated dates, month/year extraction, and date comparisons.

Conditional and aggregate formulas

SUM, AVERAGE, COUNTIF, COUNTIFS, SUMIFS, LARGE, MIN, ABS, and formulas combining aggregation with arrays.

Workbook and reference behavior

Cross-sheet references, structured table references, named/table references, row and column ranges, cached values, formula text, and evaluated values.

Examples from the compatibility validation

Area	Example formula	Result verified
Multi-level dynamic sort	`=SORT(A2:C13,{2,3},{1,-1})`	Sorts by category ascending, then score descending. Top-left result: `Team Delta`
Unique + two-level sort	`=SORT(UNIQUE(E2:F40,FALSE,FALSE),{2,1},{1,-1})`	Produces sorted unique name/product rows. Top-left result: `Northwind / Apple`
Case-sensitive lookup	`=INDEX(D2:D20,MATCH(TRUE,EXACT(H2,A2:A20),0))`	Matches the exact-case lookup value and returns `125180`
XLOOKUP with exact text logic	`=XLOOKUP(TRUE,EXACT(H2,A2:A20),D2:D20)`	Returns the same exact-case match result: `125180`
Closest match lookup	`=INDEX(A2:A20,MATCH(MIN(ABS(C2:C20-H2)),ABS(C2:C20-H2),0))`	Finds the row closest to the target value and returns `Record B`
SEARCH with Excel error behavior	`=SEARCH(E2,A2)`	Returns `#VALUE!` when the search text is not found
IFERROR wrapping an error	`=IFERROR(SEARCH(E2,A2),"Not Found")`	Converts the `#VALUE!` result into `Not Found`
Dynamic sequence	`=SEQUENCE(5,1,10,5)`	Produces `10, 15, 20, 25, 30`
Sorted filtered results	`=SORT(FILTER(A2:B20,B2:B20>=LARGE(B2:B20,H2)),2,-1)`	Filters top records and sorts them descending
Structured reference calculation	`=[@Price]*[@Quantity]`	Evaluates row-level table formulas using structured references

Excel-style results, including errors

A correct formula engine should not treat every formula as a number, and it should not treat Excel errors as crashes.

FormulaEngineer returns typed formula results, including:

Numbers
Text
Dates
Booleans
Blank results
Spilled array values
Excel errors such as #N/A, #VALUE!, #REF!, and #DIV/0!

This matters because Excel errors are valid calculation results. For example, a lookup formula may correctly return #N/A when a match is not found, and a text-search formula may correctly return #VALUE! when the search text does not exist.

How to test fairly

A fair formula-engine test should measure recalculation, not only cached values.

Recommended test method:

1
Open a real workbook.
2
Change one or more input cells in code.
3
Ignore the cached formula values stored in the .xlsx file.
4
Recalculate formula cells.
5
Compare the result with Excel.

Cached formula values can be stale or missing. FormulaEngineer recalculates formulas directly.