Calculate avg in a range in column C row 9 and current row...

[dallenk]

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

 

[bobsan42]

Use a mix of absolute and relative reference in the range. for example in the formula in I14 you have to use a range like: D$9:D14 (or $D$9:D14)
In this way when you fill the formula down only the second cell of the range will change.

 

[dallenk]

Hi bobsan, this was one of the first things I tried but will not work. ( i will try to include the MrExcelHTML maker)


each row needs to be calculated as a separate piece of the formula so only the current row + above rows are used.



for example.

Book1
	I
7	END OF DAY AVERAGE PRICE
8
9	$ 1.00
10	$ 1.50
11	$ 2.67
--dev-- (6)

Cell Formulas
Range	Formula
I9	=(((C9*D9))/G9)
I10	=(((C10*D10)+(C9*D9))/G10)
I11	=(((C11*D11)+(C10*D10)+(C9*D9))/G11)

so just adding the range in absolute and variable combinations doesn't give the correct results. Each row will need to include previous rows as a separate piece..

If i'm not understanding please let me know, I'm sure it can be done. I just don't understand how.

 

[dallenk]

oh man.. this just clicked about 15 mins ago..

=(((C11*D11)+(C10*D10)+(C9*D9))/G11)
=((50+20+10))/30)
=(80/30)
=2.666666

I already calculated each row in the gross to/from cash field.. so the absolute and relative reference works.

	I
7	END OF DAY AVERAGE PRICE
8
9	$ 1.00
10	$ 1.50
11	$ 2.67

<tbody>
</tbody>

--dev-- (6)​

Worksheet Formulas Cell Formula I9 =IFERROR(((C9*D9)/G9),) I10 =IFERROR((SUM($H$9:H10)/G10)*-1,) I11 =IFERROR((SUM($H$9:H11)/G11)*-1,) <tbody> </tbody>

<tbody>
</tbody>

Thanks.. that's a big help.. now to clean it up