Calculating balance in Numbers

Using the example in my earlier post:

The yellow filled dot at the centre o the bottom edge of cell D4 is the Fill control.

Hover the pointer near the bottom of D4 to make it appear.

Grab the dot with thep ointer and drag down to fill the formula into the rest of the cells in column D.

You'll see no immediate change, due to the IF switch suppressing calculation until the row of column B or C has a value entered.

Same table after entries in C5 and B6. Also displaying (below the table) the formula, as it appears in D1.

Adding a new row to the bottom of the table automatically copied the formula to the new row, and adusts it to fit that location.:

This automatic function will also work with Yellowbox's example, and with SGIII's example.

Regards,

Barry

Here's an example:

Table has two header rows.

Starting Balance is entered in D1

Debits entered as positive values in column B

Credits entered as positive values in column C.

Formula shown below the table is entered in cell D3, and filled down as far as needed.

D3: IF(AND(B3="",C3=""),"",D$1−SUM(B$3:B3)+SUM(C$3:C3))

Core formula, shown in bold, calculates the current balance to 'this row'.

The core formula is wrapped in an IF statement that suppresses calculation and returns a null string to cells in rows where neither column B nor column C has received an entry.

Entries in either column or in both columns will trigger calculation on that row.

Regards,

Barry

You're welcome.

You'll also find good information regarding Numbers's supported functions, their uses, and their syntax in the Function Browser. The Browser opens in the right sidebar when you click on any cell and type an =.

Double-clicking the name of a function in the list puts that function in the Formula Editor.

Happy studies!

Regards,

Barry

Hi mountainshack,

Try this:

Type your starting balance into E2

Formula in E3 (and fill down): E2+C3−D3

Regards,

Ian.

Have a look at this Checkbook Register (Dropbox download) for a working example.

This is based on the template that was included with Numbers many years ago.

SG