Spreadsheet: 737395d1624392854-calculating-running-average-cost-example_avg_cost-3-
Sheet: Version3

Limits: |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Values exposed as incorrect here in RED are dependencies on column M values and should correct automatically when formula gets corrected…
Trade Symbol | Transaction | Date | [NAME] | Quantity | Price | Total Cost | Buy Fees | Sell Fees |  | Running Qty | [NAME] | Current Balance (FIFO) | Sales Only -  Separated for RAW data |  | Gain (Loss) P & L | What current balance values SHOULD be
 | Buy | 2021-02-22 00:00:00 |  | 3.514923 | 14.21 |  | 0 | 0 |  |  |  |  |  |  |  | 49.94705583
 |  |  |  | 1.95554 | 12.8 |  | 0 | 0 |  |  |  |  |  |  |  | 74.97796783000001
 | Buy | 2021-02-23 00:00:00 |  | 2.121327 | 11.78 |  | 0 | 0 |  |  |  |  |  |  |  | 99.96719989000002
 | Buy | 2021-03-24 00:00:00 |  | 374.242645 | 13.45 |  | 0 | 0 |  |  |  |  |  |  |  | 5133.53077514
 | Sell | 2021-03-29 00:00:00 | Short | -381.834442 | 13.34 |  | 0 | 0 |  |  |  |  |  |  |  | -39.8593188599989 | Negative value here, not a positive (lost money on the trade)
 | Buy | 2021-04-17 00:00:00 |  | 13.380624 | 37.72 |  | 0 | 0 |  |  |  |  |  |  |  | 464.86 | Not $504.72
 |  |  |  | 12.706329 | 39.39 |  | 0 | 0 |  |  |  |  |  |  |  | 965.36 | All of these -39.86 to account for previous loss
 | Buy | 2021-04-27 00:00:00 |  | 28.600846 | 35.18 |  | 0 | 0 |  |  |  |  |  |  |  | 1971.54 | All of these -39.86 to account for previous loss
 | Buy | 2021-05-04 00:00:00 |  | 74.626678 | 67.52 |  | 0 | 0 |  |  |  |  |  |  |  | 7010.33 | All of these -39.86 to account for previous loss
 |  |  |  | 270.208195 | 74.56 |  | 0 | 0 |  |  |  |  |  |  |  | 27157.05 | All of these -39.86 to account for previous loss
 | Sell | 2021-05-04 00:00:00 | Short | -399.522672 | 78.52 |  | 0 | 0 |  |  |  |  |  |  |  | 4213.47 | Positive value here, not a negative (made profit on the trade)
 | Buy | 2021-05-04 00:00:00 |  | 179.734944 | 84.04 |  | 0 | 0 |  |  |  |  |  |  |  | 19318.39 | Not $15,104.92
 |  | 2021-05-05 00:00:00 |  | 49.622456 | 101.4 |  | 0 | 0 |  |  |  |  |  |  |  | 24350.11 | All of these +4213.47 to factor in previous profit
 |  |  |  | 246.20782 | 102.55 |  | 0 | 0 |  |  |  |  |  |  |  | 49598.72 | All of these +4213.47 to factor in previous profit
 |  |  |  | 165.37101 | 103.4 |  | 0 | 0 |  |  |  |  |  |  |  | 66698.09 | All of these +4213.47 to factor in previous profit
 | Sell | 2021-05-05 00:00:00 | Short | -640.93623 | 104.18 |  | 0 | 0 |  |  |  |  |  |  |  | 74.65 | Positive value here, not a negative (still in the profits here...)
 | Buy | 2021-05-05 00:00:00 |  | 30.301633 | 99.03 |  | 0 | 0 |  |  |  |  |  |  |  | 3075.42 | Not $3,000.77
 |  | 2021-05-06 00:00:00 |  | 276.819944 | 145.41 |  | 0 | 0 |  |  |  |  |  |  |  | 43327.81 | This +74.65 to factor in previous balance
 | Sell | 2021-05-06 00:00:00 | Short | -307.121577 | 168.49 |  | 0 | 0 |  |  |  |  |  |  |  | 8419.1 | Positive value here, not a negative (made profit on the trade and up decently well for every transaction up to this point)
 | Buy | 2021-05-06 00:00:00 |  | 277.1079 | 182.23 |  | 0 | 0 |  |  |  |  |  |  |  | 58916.47 | Not $50,497.37
 |  |  |  | 138.149505 | 182.77 |  | 0 | 0 |  |  |  |  |  |  |  | 84166.06 | This +58,916.47 to factor in previous balance
 | Sell | 2021-05-08 00:00:00 | Short | -107.381606 | 127.43 |  | 0 | 0 |  |  |  |  |  |  |  | 70482.42 | Not $62,063.32 … here is where things get complicated…
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | How do we show in L26 that the average cost is $182.41 after making a partial sale here?  No additional purchases were made after 5/6 that would change this number here, and selling a partial amount out of the total buy-in does not change on average what your cost was per share.  Think of this as whats in the past is the past and you can't change the past.  We wouldn't be facing this problem here if all quantity was sold out on 5/8...
Totals: |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Also, how do we get P26 to show a value that represents a portion of the loss based on the fact that this partial sale was done to minimize some of the losses when the price dropped $55 per share?  That value should equate to somewhere around $5000.00