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

dallenk

New Member
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

Last edited:

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

bobsan42

Well-known Member
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

New Member
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.

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.

Last edited:

dallenk

New Member
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
7END OF DAY AVERAGE PRICE
8
9 \$ 1.00
10 \$ 1.50
11 \$ 2.67

<tbody>
</tbody>
--dev-- (6)

Worksheet Formulas
CellFormula
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

Last edited:

Replies
36
Views
1K
Replies
0
Views
126
Replies
18
Views
113
Replies
1
Views
345
Replies
14
Views
215

1,127,567
Messages
5,625,550
Members
416,116
Latest member
Joemamasuka

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back