Finance spreadsheet in Numbers

[Vundu]

Hi All,

I have a spreadsheet with my finances on and I want a running total rather than doing the calculations manually.

For example:

Column C is my incomings
Column D is my outgoings
Column E is the running total

Any help would be appreciated.

 

[B_Z]

So assuming you are starting in row 1, you can enter a formula in E1 that says "=C1-D1", which will show you C1 minus D1 in E1. Then if you hover your mouse along the bottom edge of E1, you'll see a yellow dot appear. Grab the yellow dot and pull it down. This will copy the formula from E1 into each cell of E that you pull the handle down over. E2 will become C2-D2, E3 will become C3-D3, etc.

EDIT: As I read over your question again, I think I misunderstood a bit. Is what you want, for example, E3 to reflect the sum of all incomings and outgoings from row 1 through 3, and then E4 to reflect all incoming and outgoing from row 1 through 4, etc.?

EDIT2: Ok, got it. Same assumption that your Incoming column C is positive numbers, and your Outgoing column D is also positive numbers, you can write the following formula in E1:

=SUM($C$1:C1)−SUM($D$1:D1)

As you pull the yellow handle on E1 and copy that formula down the column, the $ signs hold the variables after them constant. So once you copy this formula down, E2 will say:

=SUM($C$1:C2)−SUM($D$1:D2)

And E3 will say

=SUM($C$1:C3)−SUM($D$1:D3)

And so on. So it adds up all the Incomings from the top down to your current cell, then adds up all the Outgoings from the top down to your current cell, and subtracts Outgoings from Incomings.

 

[ApfelKuchen]

Personally, I would simply use the Sum function for Column C and Column D, and then do a formula in Column E (C-D). Depending on how you add rows to the bottom of the sheet as you add transactions, you may have to edit the Sum formula to include the newly-added rows.

I like placing the sums and calculations in a header row at the top of the sheet and freezing the header rows so that you can scroll anywhere in the sheet and always see the totals. Makes it easy to see the current situation as soon as the sheet opens.

Row C Sum | Row D Sum | C-D |
Row C Data | Row D Data |
Row C Data | Row D Data |
etc.

However, you might not find that satisfying, if you're trying to mimic a traditional bank statement, which includes a current balance in each row. In that case, Column E would need to have a formula in every row (that you can use the fill function to copy into each cell). That formula would be, effectively, E1+C2-D2. Once the formula is set, copying it into subsequent rows automatically adjusts for the current location (if you're currently on row 135, the formula automatically becomes E134+C135-D135.

I have the feeling you're very new to the concept of spreadsheets. There are many more considerations to making this a useful document. You may want to separate transactions by month, each on a separate tabbed worksheet, and carry one month's totals forward from one worksheet to the next, for example. You can make those changes as you go along and as your understanding of what spreadsheets can accomplish grows.

 

[Vundu]

Excellent I have got it working with "=SUM($C$1:C1)−SUM($D$11)".

Thanks both for your help.