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.
—–
Dennis
You didn't include a link to the example, but I can give you a general answer.
Let's say that you want to get the average of column B, but some of the cells in column B are blank.
An average is just the sum of value divided by the number of values. To get the sum of column B, use SUM(B). That doesn't care that some cells are blank as those just count as zero.
Then to count the number of cells, you can use ROWS(B). That gives you the total number of rows. For example 22.
Then use COUNTBLANK(B) to get the total number of cells that are empty.
So the formula is simply:
SUM(B) / (ROWS(B) - COUNTBLANK(B))
Sorry, I did not see the place where to attach my file. I looked after I sent the message, but could not find a way to go back and attach it. I do not think I can use the formula you gave me. I need the blank cells for each issue, not the entire file. I have maybe 200 different stocks in my chart and over 1000 rows and that would be terrible. If you look at my file you will see that I did make it work, but I do not know how. Can you find my error(s)? How do I get the file to you?
Dennis: It doesn't matter how many rows. In my example, you can have 50,000 rows and only 400 of them have values it the same basic idea will work. Try it.
As for attaching the file, share it somewhere public (iCloud or any file sharing service) and share the link.