Advanced Spreadsheets and Reporting Documentation
Index

Formulas

A formula starts with = and references cells the way you expect — but what follows the = is a Business Central AL expression, evaluated by the extension's built-in AL interpreter. This page is the reference for how formulas behave.

The Basics

=B2*1.25
=Round(B2*1.25, 0.01)
=StrSubstNo('%1 of %2', A1, B1)
=CalcDate('<-1M>', C2)
=SUM(A1:A10) / COUNT(A1:A10)
=A1>10 ? 'High' : 'Low'
  • Cell references: A1, ranges B2:D13. References are case-insensitive.
  • Text literals use single quotes: 'like this'. Double quotes are AL identifiers ("Balance (LCY)"), used for quoted field names — never for text.
  • Texts join with +; Format() turns a number or date into text.
  • The ternary operator works: =SUM(A1:A3)>5 ? 'big' : 'small'.
  • An empty referenced cell evaluates as the number 0.
  • Formula names are case-insensitive (=sum(...) works).

AL Typing Rules — the One Trap

AL is statically typed, and the left operand decides an expression's type:

  • =7/2 is 4 — both operands are integers, so the result is an Integer and 3.5 is rounded to 4 (this is rounding, not truncating div division: =7 div 2 is 3). Write =7.0/2 to get 3.5.
  • Comparing mismatched types yields #ERROR — ='5'=5 does not coerce. Make both sides the same type first: =Format(A1) = '5'.
  • =Round(A1, 0.01) rounds to a precision, not a digit count.
  • Numbers typed into cells are decimals, so =B5/100 divides properly. But a formula result keeps its AL type: a cell holding =7/2 is the integer 4, and a formula referencing it continues in integer math. When in doubt, lead with a decimal: =1.0 * A1 / A2.

Worked example — a three-line margin block, with B2 revenue and B3 cost typed as numbers:

B4: =B2 - B3                              → the margin
B5: =Round(B4 / B2 * 100, 0.1)            → margin % with one decimal
B6: =B5 >= 30 ? 'On target' : 'Below target'

Excel-to-AL Quick Map

Excel Here
=IF(A1>10, "High", "Low") =A1>10 ? 'High' : 'Low'
=TODAY() / =NOW() =Today / =CurrentDateTime
=ROUND(A1, 2) =Round(A1, 0.01)
=ABS(A1), =A1^2 =Abs(A1), =Power(A1, 2)
=LEN(A1) =StrLen(A1)
=LEFT(A1, 3) / =MID(A1, 2, 3) =CopyStr(A1, 1, 3) / =CopyStr(A1, 2, 3)
=UPPER(A1) / =LOWER(A1) =UpperCase(A1) / =LowerCase(A1)
=EDATE(A1, 1) =CalcDate('<1M>', A1)
=CONCAT(...) =StrSubstNo('%1 of %2', A1, B1)
=A1 & " pcs" =Format(A1) + ' pcs'

The full map lives in the editor itself: Help → Cheatsheet for Excel Users.

Absolute References ($)

$A$1, A$1, $A1 pin a reference's column and/or row:

  • Through the fill handle, copy and paste, and when a template is applied: pinned parts stay put, relative parts shift.
  • Through row/column insert and delete: like Excel, $ does not stop the shift — inserting a row above $A$1 makes it $A$2. A reference into a deleted row or column becomes #REF!.

Evaluation Order

Formulas are evaluated exactly once per recalculation, in dependency order — a cell computes only after the formula cells it references. Chains of any depth resolve in a single pass. Circular references are broken instead of looping (the cell that closes the cycle sees the other cell's current value).

Evaluation happens after slicers and data regions have filled, so formulas always see live region data; charts, KPI cards and conditional formats run after formulas.

Errors

A formula that cannot compile or evaluate shows #ERROR and carries the message (visible when you select the cell). Its value is treated as 0 by other cells; one broken formula never blanks out unrelated cells.

Formulas on Region Cells

  • Existing user content can shadow a region placed at the same address. You cannot type a new formula into a live read-only region cell that has no user content already; that edit is ignored. Put calculations in adjacent ordinary cells, or use a table region's virtual columns. Drill-down still targets the underlying record when existing content shadows a drillable region cell.
  • A formula typed into a writable region cell (an editable table region, or a budget matrix amount cell) writes its computed result back to the database after every evaluation. The write-back is bound to the record shown when you typed the formula: if paging, sorting or filtering later renders a different record at that address, the write-back stops. Results are written only when they differ from what is stored.

IntelliSense

While typing a formula the editor offers completions: the sheet functions, a curated set of AL built-ins, the procedures your init code defines, and every table region's record global with its table's fields (plus the built-in GL). Typing ? at the start of a cell offers the five slicer kinds the same way.