IF, SWITCH, LET and LAMBDA: logic in Grid by Zaweba

Logic is what lets a sheet make decisions. Pass or fail, in stock or reorder, early or late. Grid by Zaweba has 19 logical functions, from the IF you probably already know to LET and LAMBDA, which let a formula hold its own working and even define new functions.

Making decisions

IF checks a condition and returns one value when it's true and another when it's false. It's the right tool for a single yes or no. When there are several outcomes, nesting IFs inside each other soon becomes hard to read, and that's where IFS and SWITCH help.

IFS takes pairs of conditions and results and returns the first result whose condition is true, which suits grade boundaries or price bands. SWITCH compares one value against a list of possibilities and returns the matching result, which suits codes: turn "N", "S", "E" and "W" into the full names of regions without a tangle of brackets. Give each a final catch-all so a value you didn't expect produces something sensible rather than an error.

AND, OR and NOT combine conditions. An order might need to be both over a certain value and from a returning customer before a discount applies, which is an AND inside an IF. IFERROR catches any error a formula throws and replaces it with a value of your choice. Use it deliberately: hiding every error can hide real problems, so keep it for the places where a failed lookup or a division by an empty cell is expected.

LET: formulas that explain themselves

LET gives names to parts of a calculation inside a single formula. If a long formula works out the same subtotal three times, LET lets you work it out once, call it something like "net", and use that name in the rest of the formula. The formula is shorter, quicker to recalculate and much easier to read the next time you open the sheet.

A good habit is to give LET names that describe the business meaning, such as "rate", "hours" or "vat", rather than single letters. Someone checking your sheet can then follow the reasoning without tracing every reference.

LAMBDA and the helper functions

LAMBDA lets you write a function of your own. You describe its inputs and the calculation, and then either call it straight away or, more usefully, give it a name in the Name Manager. From then on it works like any built-in function anywhere in the workbook. A named LAMBDA for your company's delivery charge rule, or for converting a score into a grade, means the rule lives in one place and every formula that uses it stays in step.

Several functions take a LAMBDA and apply it across data. MAP runs it on every value in a range and returns a matching range of results. BYROW and BYCOL run it on each row or each column, so you can get a total or a maximum per row with one spilled formula. REDUCE walks through a range carrying a running result and returns the final value. SCAN does the same but returns every step along the way, which gives a running balance in one formula. MAKEARRAY builds a block of values of a size you choose by calling a LAMBDA for each position.

Keeping it maintainable

These tools are powerful, and it's easy to write a formula nobody else can follow. Start plainly with IF and the familiar functions, reach for LET when a formula repeats itself, and move to a named LAMBDA when the same rule appears in many places. Put a note on a cell that holds an important formula to say what it's for.

When a formula misbehaves, Insert Function and the argument tip show what each part expects, and Show Formulas lets you see the logic across the sheet at once. Logic in Grid by Zaweba works the way spreadsheet users expect, so formulas you write here travel with the workbook when it's saved as .xlsx.