Ask Your Question
0

How to subtract a running total from a cell with a total amount

asked 2018-03-02 15:36:20 +0100

memerson gravatar image

Hi,

I'm quite new to spreadsheets in LibreOffice (and in general) to manage my finances. I've been trying to calculate things but it's getting confusing as I'd much prefer to enter a total balance amount and then whenever I make an expenditure anywhere (be it withdraw from ATM or online etc.) I'd like the sheet to deduct that amount from the total amount so I can visbly see the current balance at all times.

Before anyone says 'just check your bank account' this is not always accurate as things don't always process immediately and I've found writing things down the moment you purchase them is the most suitable and manageable way of doing things.

So, basically, if I have a total balance of £3000 and I spend £100 on something, I want the cell with the £3000 to read £2900. Is there a way to do this using the equation function? If so, how would I write it?

Thank you

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
0

answered 2018-03-02 20:58:28 +0100

Jim K gravatar image

Here is a stripped-down version of the monthly finances spreadsheet I have used in the past: monthly budget.ods

It shows the current balance at the top, but all of the transactions are listed below. I use a Python-UNO script to download a CSV file from my banking website and then automatically insert transactions, but transactions can be entered manually instead.

This key formula always shows the current balance in a single cell at the top:

=INDIRECT("J"&(MATCH(150000;J7:J33;1)+ROW(J6)))

It works by using MATCH to find the row of the last transaction in the list and then INDIRECT to get that value.

Every month, I would reconcile the spreadsheet with the bank statement by entering the final balance of the month next to "Statement total." It says "OK" when reconciled.

I found it unnecessary to use symbols such as £. If one particular transaction uses something other than pounds, then the "Foreign Currency" column can be used.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2018-03-02 15:36:20 +0100

Seen: 168 times

Last updated: Mar 02 '18