Hello everyone, I've been fighting with trying to create a formula for my sheet.. long story short. I need to calculate an average of all positive number in a range, but only in column C, and between row 9 and the current row..
Long story short this is for tracking my stock buy/sell transactions..I've read probably 500 posts on various sites and still can't seem to figure it out. I can easily calculate the average of the entire range, but I also need to calculate the average price at any row in the sheet in order to figure out the current book/market price difference.
linked is a screenshot of my sheet. (please ignore the obvious math errors in some cells, it's a work in progress)
So, in column I, I need to calculate the average price of all the previous transactions in order to calculate other numbers..
for instance, I know:
I10 average price would be: 1.50 = (10*1 + 10*2) / 20
I11 average price would be: 2.66 = (10*1 + 10*2 + 10*5 ) / 30
but is it possible to create a formula that can dynamically know the range will be between D9:D10 when the formula is in I10?? or D9:D14 when the formula is in I14..and without having to enter a complete formula in each cell.. I have been trying with combinations of MATCH and ROW() but can't seem to figure it out.
Thanks for any help
Long story short this is for tracking my stock buy/sell transactions..I've read probably 500 posts on various sites and still can't seem to figure it out. I can easily calculate the average of the entire range, but I also need to calculate the average price at any row in the sheet in order to figure out the current book/market price difference.
linked is a screenshot of my sheet. (please ignore the obvious math errors in some cells, it's a work in progress)
So, in column I, I need to calculate the average price of all the previous transactions in order to calculate other numbers..
for instance, I know:
I10 average price would be: 1.50 = (10*1 + 10*2) / 20
I11 average price would be: 2.66 = (10*1 + 10*2 + 10*5 ) / 30
but is it possible to create a formula that can dynamically know the range will be between D9:D10 when the formula is in I10?? or D9:D14 when the formula is in I14..and without having to enter a complete formula in each cell.. I have been trying with combinations of MATCH and ROW() but can't seem to figure it out.
Thanks for any help
Last edited: