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

dallenk

New Member
Joined
Jan 20, 2017
Messages
16
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




2019-02-21.png
 
Last edited:

Some videos you may like

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

bobsan42

Well-known Member
Joined
Jul 14, 2010
Messages
1,364
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
Joined
Jan 20, 2017
Messages
16
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.

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>I</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">END OF DAY AVERAGE PRICE</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #F2F2F2;;"> $ 1.00 </td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #F2F2F2;;"> $ 1.50 </td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #F2F2F2;;"> $ 2.67 </td></tr></tbody></table><p style="width:8.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">--dev-- (6)</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">I9</th><td style="text-align:left">=(<font color="Blue">(<font color="Red">(<font color="Green">C9*D9</font>)</font>)/G9</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">I10</th><td style="text-align:left">=(<font color="Blue">(<font color="Red">(<font color="Green">C10*D10</font>)+(<font color="Green">C9*D9</font>)</font>)/G10</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">I11</th><td style="text-align:left">=(<font color="Blue">(<font color="Red">(<font color="Green">C11*D11</font>)+(<font color="Green">C10*D10</font>)+(<font color="Green">C9*D9</font>)</font>)/G11</font>)</td></tr></tbody></table></td></tr></table><br />


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
Joined
Jan 20, 2017
Messages
16
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:

Watch MrExcel Video

Forum statistics

Threads
1,127,167
Messages
5,623,130
Members
415,956
Latest member
Footballtend

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top