Maths and trig functions in Grid by Zaweba

Most spreadsheets are built on a handful of sums. Add up a column, round a total to the penny, multiply a price by a quantity. Grid by Zaweba has 80 functions in its maths and trigonometry family to do that work, and a good many more for the jobs that come later, once a sheet has grown beyond a simple list.

This article walks through the ones you'll reach for most often, a few that are easy to overlook, and the habits that keep the answers right.

The everyday ones

SUM is where nearly everyone starts. Give it a range and it adds the numbers in it, skipping text and blanks. ROUND is the next most useful, because a sheet that shows two decimal places can still be holding many more behind the scenes. If an invoice total is built from rounded line amounts, round each line with ROUND rather than relying on the number format to hide the extra digits. The format changes what you see; ROUND changes the value itself, and that is the value every later formula picks up.

The rest of the rounding group follows the same idea for different rules: round up, round down, round to a multiple. Powers and logarithms are there too, along with square roots and the usual trigonometry for angles, so a sheet for a woodwork project or a physics homework problem can be worked out without a calculator beside the keyboard.

SUBTOTAL and AGGREGATE for filtered lists

These two are worth learning early. SUBTOTAL can total, count or average a range while ignoring rows you have hidden. That matters when you hide a few months of a budget to look at the rest, or when a list is grouped and folded away: the figure underneath follows what is on show rather than everything in the range.

AGGREGATE goes a step further. It offers a wider choice of calculations and options for what to leave out, which makes it handy when a column contains an error value that would otherwise stop a plain SUM in its tracks. A stock list with one broken lookup can still give you a total while you go and fix the broken cell.

A sensible habit is to use SUBTOTAL for any total that sits under a list you expect to filter or hide, and plain SUM for totals that should always include every row. Mixing the two up is the most common reason a total looks wrong after rows are hidden.

Matrix maths and random numbers

MMULT multiplies two ranges as matrices, MINVERSE gives the inverse of a square range and MDETERM its determinant. Because Grid has dynamic arrays, a function like MMULT that returns several values spills its answer into the cells below and to the right, so you type the formula once in the top left cell and the result fills out on its own. Leave room for it: if something is already sitting in the way, the formula can't spill.

RANDARRAY produces a block of random numbers in one go, which is useful for test data, drawing raffle numbers or shuffling a list when paired with sorting. Like any random function it gives fresh values whenever the sheet is worked out again, so if you need to keep a particular draw, copy the cells and use Paste Special to paste the values only.

The curious corners

ROMAN turns an ordinary number into Roman numerals, which is more useful than it sounds for chapter numbers, event editions or a reunion invitation. BASE writes a number in another base, such as binary or hexadecimal, for anyone working with codes, colours or electronics. For deeper work on number bases and bits, the engineering family has its own conversions.

Getting the most from them

When you can't remember an argument, open Insert Function and pick the maths and trig category. Each function is listed with a description and its arguments, and the argument tip that appears as you type shows which one you're on. That is quicker and safer than guessing the order.

Keep inputs in their own cells rather than typing numbers into formulas. A VAT rate or a conversion factor in a labelled cell can be changed once and every formula that uses it follows. And if a total looks wrong, trace its precedents to see arrows to the cells it is really using.

The maths family is the base everything else in Grid by Zaweba stands on. Statistics, finance and dates all build on the same sums, so a little care with rounding and hidden rows here pays off across the whole workbook.