Formulas and values

How to write formulas, what a cell value can be, and how dynamic arrays, names and LAMBDA work.

Syntax

Write formulas the way Excel stores them in a file: English function names, a comma between arguments and a dot as the decimal separator. The leading = is optional; Cell.Formula adds it if missing. Localized function names such as СУММ or SOMME are not recognized.

References:

Build a workbook in code

var workbook = new Workbook();
Worksheet sheet = workbook.AddSheet("Sheet1");
sheet["A1"].Value = 2;
sheet["A2"].Formula = "=A1*3";
workbook.Recalculate();
CellValue result = sheet["A2"].Value;   // 6

Reading values

A CellValue is one of: Number, Text, Boolean, Error, Array, Empty, Missing or Lambda. Dates and times are numbers: serial days since the workbook's date system epoch. Errors such as #DIV/0! are values, not exceptions.

static string Describe(CellValue value) => value.Kind switch
{
    CellValueKind.Number => value.AsNumber().ToString(CultureInfo.InvariantCulture),
    CellValueKind.Text => value.AsText(),
    CellValueKind.Boolean => value.AsBoolean() ? "TRUE" : "FALSE",
    CellValueKind.Error => value.AsError().ToText(),   // "#DIV/0!", "#N/A", ...
    CellValueKind.Array => $"array of {value.AsArray().GetLength(0)} x {value.AsArray().GetLength(1)}",
    CellValueKind.Empty => "(empty)",
    _ => value.ToString(),
};

The As methods throw InvalidOperationException when the value is of another kind. Every member is listed in the CellValue reference.

Dynamic arrays

A formula that returns several values spills into the cells below and to the right. The spilled cells hold values but no formula: their Formula is null.

sheet["C1"].Formula = "=SEQUENCE(3)";   // spills into C1:C3
sheet["D1"].Formula = "=SUM(C1#)";      // C1# is the whole spilled range
workbook.Recalculate();
// sheet["C3"].Value is 3 and sheet["D1"].Value is 6

When a cell in the way holds something, the anchor shows #SPILL! and the other cells stay as they are:

sheet["C2"].Value = "in the way";
workbook.Recalculate();
// sheet["C1"].Value is #SPILL!; clear C2 and recalculate to spill again

Names and LAMBDA

DefineName gives a formula a name. A name whose formula is a LAMBDA can be called like a function.

workbook.DefineName("TaxRate", "=0.2");
workbook.DefineName("WithTax", "=LAMBDA(amount, amount * (1 + TaxRate))");
CellValue gross = workbook.Evaluate("=WithTax(100)");   // 120

A function's name alone is the function as a value, wherever a LAMBDA is expected: in MAP, REDUCE, SCAN, BYROW, BYCOL, MAKEARRAY and as the value of a LET name. It takes its arguments as a call by name would. Custom functions can be passed the same way.

sheet["D1"].Formula = "=BYROW(A1:B2, SUM)";            // 3 and 7: SUM is passed as a function
sheet["E1"].Formula = "=LET(size, LEN, size(\"abc\"))";  // 3
workbook.Recalculate();

Formulas outside cells

CellValue sum = workbook.Evaluate("=SUM(1, 2, 3)");   // 6, no sheet needed

Errors in formula text

Text that is not a valid formula throws FormulaParseException, which says where parsing stopped. The cell is left unchanged.

string? problem = null;
try
{
    sheet["A1"].Formula = "=SUM(1,";
}
catch (FormulaParseException e)
{
    problem = $"at position {e.Position}: {e.Message}";
}

A formula read from a file that SharpCell cannot handle does not throw. Once calculated, it is #NAME? and listed in Workbook.Diagnostics as UnsupportedFormula. See Reading .xlsx files.