How to write formulas

doglegjack wrote:

I have forgotten how to write formulas.

You can let Numbers do a lot of the work in entering the formulas.

Here, I'm entering the formula in C2. So I click C2 then type = to call up the formula editor.

Then I type SUM and click that in the bar of suggestions that Numbers provides:

That gives you this within the formula editor:

With the formula editor still open select cells A1:A2, giving you this:

Click the A$1-A$2 oval within the formula editor, then the down triangle, and uncheck Preserve Row under End:

Continue with the formula by typing - followed by SUM:

Click the 'value' oval and select cells B1:B2, then click the B$1:B$2 and uncheck Preserve Row under End, giving you something like this.

Click the green checkmark to accept the formula. Then fill or copy that formula down the column by selecting the cell, hovering near the bottom edge and dragging the yellow dot down. (You can also click C2, command-c to copy, select the cells below, and command-v to paste):

Now, when you enter values in columns A and B you get a running balance.

SG

Here's an example:

The table has two Header rows—one to to contain a "starting Balance" amount in column C, the second to enable placing a column label at the top of each column

The main formula is the one shown below the table. This is entered as shown in cell C3, then filled down to C10.

Here is a text version of the same formula;

IF(AND(A3="",B3=""),"",
C$2+SUM(A$3:A3)−SUM(B$3:B3))

The core formula, shown in BOLD gets the opening balance value fro C2, adds the SUM of values in row 3 to 'this row' of column A, then subtracts the SUM of the values in row 3 to 'this row' of column B, and places the result in 'this row' of column C.

The core formula is wrapped with an IF statement.

IF checks for content in 'this row' of columns A and B, and places a null string ( "" ) in its cell if none is found. If either column A or column B has an entry, IF calls the core formula to do its task.

As entries are added to columns A and B, the formulas will automatically calculate the new balances (and totals).

There are two other formulas in the table, both in the Footer row (Row 11)

A11: SUM(A)

This is filled right one column to B11

C11: A11-B11

As rows are added to the table, they will push the Footer row down to keep it at the bottom of the table.

Regards,

Barry