I have a spreadsheet that I use to record stock market transactions–buys, sells, dividends. Each row is a transaction of some type. I have a column that compares the current price to the original price I paid for it so I can see the percent of increase or decrease, although I do not show it as a percent. A stock with a current price of $10 that orignally cost $6.50 is shown as 10/6.50 or 1.54 and positive gains are green and losses are red. I can easily determine that for each transaction, but want a value for all transactions. I have categories and can put AVERAGE into the column for the average gain or loss. What I need is to display the average when I only have some of the shares remaining and some rows should not be included in the average. The formula I have used likes to change the ratio of those items no longer held to the value zero and includes them in the category average average. I only want the average of the shares I still own. I have explained this with examples in the attached file. Can you find the error in my spreadsheet or what I must do to make it exclude those for which I no longer own shares and not average them in as zero ratios. Thank you.
Although some of the shares of a stock may have a big gain or loss, I want to know, as an average, is this stock a loser or still have some hope of being a good one in spite of one or two bad purchases.