[Solved] Running total formulas

[Irishrose]

Hi I am new to the form. I have been using open office for some time now, originally on windows 7. It allowed me to use an old formula K2-G3+I3
which it would correct for me. This would help me keep a running total on my sheet that is used instead of a bank book.
Lately it has stopped working and I don't know how to make it work. I have tried looking at the tutorials and its seems like a garbled msg to me...
nothing seems to work.
I would much appreciate it if anyone has a simple way of making it work for me.
Thanks

[robleyd]

Is there any error?
Is there data in the cells referenced?
If so, is the data numeric type - use View|Value highlighting to show type of data in cells: text cells are formatted in black, formulae in green, and number cells in blue, no matter how their display is formatted.

You may wish to upload your spreadsheet for others to try and resolve the problem; [Forum] How to attach a document here

[Irishrose]

Hi not sure about errors. I'm including 2 png captures, One is where I am trying to do the right
entry for SUM but its not working and not sure what I am doing wrong, (not techy). The other png
showing the colors you talked about using the view/value highlighting. This is a previous sheet
that I lost the running totals on.
Thanks for your help.

[RoryOF]

Often (but not always). the ### marks in a column mean that the column is not wide enough. Select the columns by clicking on its header, and choose /Format /Optimal Column width and see if that helps.

Edit: also the Sum(K2);... looks wrong. Please tell us what that is in full.

[Irishrose]

When I go to optimal column width I get this...
=SUM(K2-G3+I3)
=SUM(K3-G4+I4)
=SUM(K4-G5+I5)
=SUM(K5-G6+I6)
=SUM(K6-G7+I7)
=SUM(K7-G8+I8)
=SUM(K8-G9+I9)

The formula that used to work K2-G3+I3 before I switched to win 10 from win 7 is what I know how to do
There must be a simple way to make it work that I'm not catching on to

[RoryOF]

/Tools /Options /OpenOffice Calc /View uncheck Formulae (on right of window under "Display" heading).

[Irishrose]

Thanks so much that worked now I have my totals all back for previous months. Now
to figure out the sum part for this month and future. Wish I understood these things
a bit better

[RoryOF]

Click on the last entry in Column K. Grab the little black dot in bottom righthand corner of that cell and pull down as far as you are likely to need. This should insert an updated formula in each cell.

[Irishrose]

Hi Rory
I tried what you suggested and this is what I get. It still seems like there is something wrong
that makes the running total.

[RusselB]

I'm going to suggest replacing the formula in your Balance (column K) with

Code: Select all

=if(and(g2="";i2="");"";sum($g$1:$g2)-sum($i$1:$i2))

This will give you the balance for any lines that have entries, but if both the Debit (column G) and Credit (column I) are empty, then the formula will show nothing.

As to the formula you are currently having problems with, I can see the progression, but I don't understand why you are getting the progression as nothing in the previous posts indicates to me that this progression should be appearing.
If you could copy and paste the formulas you are getting from the first 5 or 6 lines, then I might be able to see where the entry error occurred, as it appears to be an entry error (eg: 2 instead if G2)

[Irishrose]

Hi RusselB
Hope this helps you see better what I see sometimes. If I go into format cells it will show me what should be there
but I do not get the correct running calculation showing up on the sheet. If I was to start a sheet from scratch I
would not know how to get it to work with a running total. Presently I have to copy paste from another sheet that
has a working running total so that my final number is correct.... a bit tedious

[robleyd]

Is it practical for you to upload the actual spreadsheet you are working on? Otherwise we are pretty much working blind

PS You might also find [Tutorial] Ten concepts that every Calc user should know a useful read, especially Section 8. Using formulas and cell references.

[Irishrose]

Hi Robleyd
Thanks for your help. I apologise if I'm confusing anyone, am not a technical person much beyond the simple basic. Here is the actual
spreadsheet I am working on. Will read up on the info you suggested.

[robleyd]

Looks like you have somehow managed to format column K as Date I've changed that to a currency format with red font for negative numbers; if that isn't what you want, just select the area you need to change and apply your preferred format.

Then I simply put your original formula in K and things seem to be as desired.

[RusselB]

Here's your spreadsheet with a slightly modified version of my suggested formula, and the formatting change that robleyd already mentioned.