I am trying to get a checking account balance, adding positive and negative amounts to the previous balance to see what I should have at the end. In trying to get the SUM function in the correct format, I lost a whole column of figures and have to start a new spreadsheet. Having taken care of that, I am not getting the results I want.
I was able to solving my current reconciliation problem; but I would still like to setup the document. After going online to obtain formating directions, they are not understandable. I am getting unreadable results. I have all my debits and credits in Column C showing debits as negative numbers. Next to each transaction I want a running balance in Column D. Thank you.This is version 7.0.2.2 on Windows 7 64-Bit. CommunitySample.xls
Formatting should not be a problem so long as all cells involved in the SUM are formatted as numbers. Are you copying the numbers from a website or someone else’s spreadsheet? It would help a lot if you gave us an example of the problem you are having.
I started with a balance out of my checking account and tried to total all of the transactions since. For the =SUM formula I used cell locations. The formula is in the column where I want the changing balances. Results are not showing as numbers. All cryptic. The function is not working.
Can you provide a sample spreadsheet that shows the problem? Edit your question and use the paperclip to attach your document.
Hello
Does the SUM function applies to a range of cells including the location of the SUM function ?
Edit the description of your question and append a sample spreadsheet without sensitive data.
In cell D2, I tried to enter the formula =SUM(C3:C20, D2:D19) although my actual register goes to almost 40 entries. For the sample I did not enter the formula so that the spreadsheet would be free of whacky codes. Good luck.
There is an answer posted that does not tell me what to do.
If you are copying and pasting the numbers from your on-line bank statement, you must format the numbers in LibreOffice as Numbers; otherwise the “numbers” will probably be treated as Text, producing nonsense results.
Also, those “all cryptic” notations that you are trying to avoid are actually error messages from LibreOffice, trying to tell you what is wrong. Please tell us what the cryptic notations are, or give us an actual example.
Are the “cryptics” a series of # ? If so, this means that the column width is not large enough or the size of the font too large.
So you created a circular calculus which leads to infinite and therefore is not allowed by default.
Besides the use of a coma instead of a semi-colon but this might be allowed by some setup I do not know.
Edit
LO means LibreOffice
Here is an example spreadsheet to provide a running balance for each movement.
It does not look nice but this is the one I am using : easy to detect a mistake and to correct or to expand : just (re-)paste the formula.
To show the community that the question has found its answer, please click on the ✓ aside the top of the correct answer and vote by clicking the ^ caret of all usefull answer.
Kind regards, Michel
Hello @tightpurchaser
Let’s simplify the formula to illustrate the circular reference : D2 = D1+D2. You should get an Err:522 instead of the result. Unless the D2 cell where is inserted the formula is not on the same sheet as the operand of the SUM function.
To avoid this kind of inaccurate talk, could you, please edit the description of your question and append a sample sheet without sensitive data? This would greatly help us to help you.
Kind regards, Michel
The error code that shows up most in the D column is Err 509. I adjusted my sample document. Column D is where I want a running total next to each figure in Column C. For both Columns C and D I formated the cells as numbers, negative ones in parentheses. Each number in Column C is either a debit or credit. Thanks.
Error 509 means missing operator.
The error code that shows up most in the D column
From the above, it looks like this formula is pasted in several cells of the column D.
Please provide a sample of your spreadsheet, to help us help better.
How many spreadsheets do I have to send? Each time I sent a reasonable example of what I am trying to do. The only difference is that I did not put in formulas. I can do that if you want.
Here is another spreadsheet to upload. This time I dragged the cursor through Column D under cell 2, where I want the results of the formula I entered.
Hello @tightpurchaser
Thank you for the sample file. Sorry for not having seen it previously.
However, at opening, it only shows figures, no formula. Could you check, please?
I do not see any figures in column D but in D2 and all credit and debt figures are in column C.
Something I do not understand : as per the shape of the initially mentioned formula =SUM(C3:C20, D2:D19), I understand that D2 contains the previous balance or the balance to start with the accounting. So why should be the final/actuated balance placed in D2 ?
I forgot to tell you that LO works better with OpenDocument Format. Using xls format is unsafe. however, for a simple additions, this should not have a major impact.
Column D is where I want to show the changing balance for each transaction in Column C. My written register is 4-column accounting paper. That is just the way I want it to look. What is LO? You are talking to a novice here.
I think I see now what you are trying to do. But you don’t need the SUM formula at all!
As pointed out in the solution by @mgl simply enter =D2+C3 in cell D3. Then Copy cell D3 and highlight the rest of the column D as far as you want. Then use menu → Edit → Paste Special → Paste Special. Un-check all of the options under Paste Special, then check (only) Formulas and Formats. Click OK. The balances should appear in all the highlighted cells, as far as there is data to add.
Click on any cell in column D where there is data. You will see that the formula is automatically adjusted to add the new transaction in column C to the previous balance in column D, placing the new balance in the current cell of column D.
Thanks for the sample spreadsheet even though it looks like we are losing chronological order with these chats, posts, whatever here. Manually putting in the formula, I got the correct balance for C3. Copying it, Paste Special only gave me the option for Formulas, not Formats. I got nothing when I used that option. Yo, I got it to work! Gee, what will they think of next? I dragged the dark, lower right corner of C3 down to the last transaction; and they all added. Thanks. I hope in the future I remember how to do this. Just now I inserted that formula into cell C3 of my actual register. It worked; but the formula was subborn getting in there. It seemed that the program later inserted an extra cell name at the end; but eventually it worked to where I dragged that bottom right corner of the C3 cell so that I got the correct balances.