Forum Discussion
Running Total Formula Changing on me
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.
https://1drv.ms/x/s!ApG1FtftEA3Ggt9zIUBER6OJuC1kWg?e=QE3CU4
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).
- P.S. It was in the Times Roman font on my Mac.....
- I like that tilt idea, thank you for that. So I downloaded your copy and tested with a new line and it still changed the formula on me. This link is the lines with the new line added, that's all I changed is adding a new line with info. Both the 2nd and 3rd line have K18 in the formula, it's not populating correctly.
https://1drv.ms/x/s!ApG1FtftEA3Ggt91gR3TTFlMfyMFKA?e=FplMczI'm not sure how you did it. I copied the formula from K16 and pasted it on down and it worked correctly. In your OneDrive file...
- I share this form blank with others, the problem is some people won't understand how to copy or drag the formula down, but just going to the next row under the table and adding a new row, once new row populates the previous row formula changes the K #'s.