Forum Discussion
Solved
Running Total Formula Changing on me
I'm trying to make a running total column in a table (spreadsheet is tracking stock buying/selling/profits.
This example here the original formula I had in N16, was: =IF(ISBLANK([@[$ Return]]),"",SUM(K15:K16))
As soon as I add a new row to the table by typing the new date for a new line, it changes my formula, advancing the K16 to K17. I've also tried with $K$15:$K16 and get the same issues.
Side note: it's also changing that columns font to Calibri 11 instead of the default I have as Times New Roman 12.
How do I fix these errors?
I have had that happen one other time, where somehow a correction to a formula in a table doesn't populate with the corrected formula to new rows; it's as if Excel remembers somewhere in the back of its inscrutable mind the original formula and keeps insisting on using it.
In the attached, although I didn't finish your conditional formatting (nor did I repair the graph), I totally deleted the original column N, then inserted a new one, and wrote the correct formula, which now does seem to propagate correctly. You obviously know how to complete the rest of the stuff.
Basic message, you may need to start with an altogether new spreadsheet.
By the way, I think it might be better to keep the transaction history in one sheet, the summary reporting in another. Mixing Input and Output on one sheet (especially as you've done it here, with summary on top of the transaction history) can cause the editing/revising of either one to mess up the other. People often talk of creating a "dashboard" sheet that presents the high level summary, and keeping it separate from (in the same workbook, but on a different tab) the raw data.
You might also consider--here I don't know your clients, the people you're creating this for--creating a simpler transaction history, where the buying of shares of ABC is one row (with date, ticker, quantity, price, total) as one row, and the sale of shares another row. This would be almost essential if your clients do a series of buys of ABC in, say, quantities of 500, then did sales in quantities of 100 after significant capital gains (or losses)..... Your design presumes buying and selling the same quantities (there's only one column for "Shares"); which could be a design flaw.
9 Replies
Since you've gone to the trouble to create a spreadsheet with no confidential data, so as to post the image, would you be so kind as post a copy of that actual spreadsheet so we can look at the real thing, not just an image? Post it on OneDrive or GoogleDrive, with a link here that grants edit access to it.
The formula in cell N16 should be
=IF(ISBLANK([@[$ Return]]),"",SUM($K$16:K16))You'll note that I made a few other minor changes. I would not recommend "forcing" headings to two lines, as you had with "Sell Price" -- you may think it makes things look nicer, but it makes formulas read strangely. Far better would be to do "Sell_Price" and if you need to keep the column narrow, tilt it in "Format....Cells....Alignment" Then the formulas AND the headers are readily legible
Here, for example, are the headings on a similar tracking sheet I have for my investments in options:
It's very easy to read (that image size is greatly reduced from the actual).
