Formula syntax

Basics

A formula refers to your other columns by their heading, and always produces a number.

Area * 1.15
Width * Height
round(Area, 2)

A heading with a space in it goes in square brackets: [Clear Width] / 2. Headings are matched ignoring case.

Operators, loosest to tightest: || && · = != · < <= > >= · + - · * /. Brackets work as you expect, and a comparison answers 1 or 0, so it adds up to a count.

There is no ^ and no %, on purpose. % reads as “percent” to almost everyone who would type it here, and it would mean remainder — a wrong number that looks perfectly plausible. For a share of a total, add a percentage calculated value instead. ^ is gone because Width * Width says the same thing and says it better: the app can see that multiplying two lengths makes an area, and cannot see through a power. Type either one and it will tell you this.

Nothing you type is ever executed. The formula is parsed into a small stack machine — there is no eval anywhere in this app.

Units — read this one

A formula sees the stored value, and everything is stored in SI: metres, square metres, cubic metres, kilograms, radians. A door 2,350 mm high is 2.35 to a formula, whatever the Height column displays.

So Height * 2 gives 4.7 — correct, in metres, and confusing beside a column reading 2,350. Tell the app what the result measures using the “Result is a” dropdown, and the column converts like any other: pick length and it shows 4,700 mm.

The app works out the answer from the formula itself and offers it to you — Width * 2 is a length, Width * Height is an area, Volume / Area is a length again. It will not change your choice silently; it only tells you when it disagrees.

It also catches expressions that cannot mean anything, like Width + Area — adding a length to an area. That is arithmetically fine and physically nonsense, and it used to produce a number with no complaint.

Text

Text goes in to a formula, and can come back out as a label you choose. Put text in quotes, single or double. All text comparison ignores case.

contains(Type, "FD")          → 1 if the type contains FD
starts(Mark, "D")             → 1 if the mark starts with D
ends(Mark, "01")              → 1 if it ends with 01
Family = "Basic Wall"         → 1 if it matches exactly
Family != "Basic Wall"        → 1 if it does not

Because those answer 1 or 0, they drop straight into if() and into a column total: give the column a Sum and you have counted the matching rows.

A column of 1s and 0s reads badly, though. Set “Result is a” to yes or no and the same formula answers Yes or No instead — Column formatting can then show it as Yes/No, TRUE/FALSE, Y/N or a tick. There is no special syntax for this; it is a property of the calculated value, not something you write into the formula.

You lose nothing by doing it: a yes still counts as 1, so a Sum total on that column still tells you how many matched. The Yes / No style dropdown only appears for columns that can actually be one.

A number stored as text — "2400" in a Mark field — is only treated as a number when you ask, with number(Mark). That is deliberate: implicit conversion would make Mark + 1 mean different things in different files.

Arithmetic on text gives a blank cell rather than a made-up number.

Answering with a word

Set “Result is a” to text and the column keeps whichever label the formula picked. Useful for sorting a schedule into buckets you invented, then grouping and counting by them:

if(Area > 10, "Large", "Small")
if(contains(Type, "FD"), "Fire door", "Standard")

Whatever the column says it holds is what it will hold: on a text column a result that is not text comes out blank, so if(Area > 10, "Large", 0) leaves the small rows empty rather than printing a 0 among the words. The same rule the other way is why a formula returning text on a plain number column is blank. A branch of "" counts as no value at all, so those rows collapse together with rows that never had one.

Two pieces of text cannot be joined. Type + " " + Mark is a blank, not “Door D-101” — + is arithmetic only. Putting columns side by side is what a spreadsheet is for once the schedule is exported.

Functions

When a cell comes out blank

A blank is always deliberate — it never shows NaN or Infinity:

• a column the formula names has no value on that row;
• division by zero;
• arithmetic applied to text, including + between two pieces of it;
• a result that is not a finite number;
• a result that is not the kind the column says it holds.

A formula that cannot be parsed at all is different: it reports the mistake under the box as you type, rather than quietly giving you an empty column. A heading that two columns share is reported too, instead of the app picking one of them for you.

Recipes

Count the fire doors

contains(Type, "FD")

Result is a plain number. Give the column a Sum total.

…or mark them Yes / No instead

contains(Type, "FD")

Same formula. Set Result is a to yes or no. A Sum still counts them.

…or give each one a name

if(contains(Type, "FD"), "Fire door", "Standard")

Set Result is a to text. Sort on the column and switch itemise off, and the schedule collapses to one line per kind with a count.

Sort rooms into size bands

if(Area > 20, "Large", if(Area > 8, "Medium", "Small"))

Result is text. if() nests, so a third band costs one more. Group by this column for a summary of the whole floor.

Area with a 15% allowance

Area * 1.15

Result is an area.

Wall area from its dimensions

Length * Height

Result is an area — the app will suggest that.

A square, without a power operator

Width * Width

Result is an area, and the app can tell. Width ^ 2 could not be.

Flag anything under a minimum

if([Clear Width] < 0.85, 1, 0)

Note the 0.85: comparisons are against stored SI values, so that is 850 mm. Result is a plain number.

Only count the ones that are both

if(contains(Type, "FD") && Height > 2.1, 1, 0)