Weighted Average from a sum value of a range

[rickgg]

A B
[TABLE="width: 640"]
<colgroup><col width="64" span="10" style="width:48pt"></colgroup><tbody>[TR]
[TD][TABLE="width: 320"]
<colgroup><col width="64" span="5" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl65, width: 64, align: right"]60,000 [/TD]
[TD="class: xl66, width: 64"]$5.15[/TD]
[TD="width: 64"][/TD]
[TD="width: 64, align: right"]20000[/TD]
[TD="width: 64, align: right"]5.6[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]15,271[/TD]
[TD="class: xl66"] $5.25[/TD]
[TD][/TD]
[TD="align: right"]30000[/TD]
[TD="align: right"]5.6[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]4,880[/TD]
[TD="class: xl66"] $5.25[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]18,500[/TD]
[TD="class: xl66"] $5.35[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]44,497[/TD]
[TD="class: xl66"]$5.50[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]21,56 [/TD]
[TD="class: xl66"]$5.45[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]433[/TD]
[TD="class: xl66"] $5.20[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]600 [/TD]
[TD="class: xl66"]$5.25[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]7,200[/TD]
[TD="class: xl66"] $5.21[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]6,000 [/TD]
[TD="class: xl66"]$5.33[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]21,345 [/TD]
[TD="class: xl66"]$5.23[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]200,287[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]50000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]150,287[/TD]
[TD="class: xl67, colspan: 4"] Weighted Avg Formula [/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"][/TD]
[TD="class: xl67, colspan: 4"][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="class: xl64, align: right"][/TD]
[TD="class: xl66, colspan: 7"]

[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
I basically need the most simple excel beginner formula that will find the weighted average price of columns A and B based on the latest 150,287 of column A.

Any guidance or help in accomplishing this would be appreciated!! Thanks!

 

[SpillerBD]

See Microsoft suggestion.

Mike Girvin has a cool method using MMULT. Check out his book CTRL+SHIFT+ENTER

 

[rickgg]

Is there a formula that would find the weighted average of the most recent cells in columns A and B or the quantity and price where the average price was calculated off of 150,287 of the most recent quantity or column A?
Essentially what is the average price of the last 150,287 quantity in column A??

 

[Eric W]

I must confess to being a bit confused by your description. Let's see if I understand you correctly.

	A	B	C
weighted average

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FAFAFA, align: right"]60,000[/TD]
[TD="bgcolor: #FAFAFA, align: right"]$5.15 [/TD]
[TD="bgcolor: #FAFAFA, align: right"]200,287[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #FAFAFA, align: right"]15,271[/TD]
[TD="bgcolor: #FAFAFA, align: right"]$5.25 [/TD]
[TD="bgcolor: #FAFAFA, align: right"]140,287[/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #FAFAFA, align: right"]4,880[/TD]
[TD="bgcolor: #FAFAFA, align: right"]$5.25 [/TD]
[TD="bgcolor: #FAFAFA, align: right"]125,016[/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: #FAFAFA, align: right"]18,500[/TD]
[TD="bgcolor: #FAFAFA, align: right"]$5.35 [/TD]
[TD="bgcolor: #FAFAFA, align: right"]120,136[/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: #FAFAFA, align: right"]44,497[/TD]
[TD="bgcolor: #FAFAFA, align: right"]$5.50 [/TD]
[TD="bgcolor: #FAFAFA, align: right"]101,636[/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: #FAFAFA, align: right"]21561[/TD]
[TD="bgcolor: #FAFAFA, align: right"]$5.45 [/TD]
[TD="bgcolor: #FAFAFA, align: right"]57,139[/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: #FAFAFA, align: right"]433[/TD]
[TD="bgcolor: #FAFAFA, align: right"]$5.20 [/TD]
[TD="bgcolor: #FAFAFA, align: right"]35,578[/TD]

[TD="align: center"]8[/TD]
[TD="bgcolor: #FAFAFA, align: right"]600[/TD]
[TD="bgcolor: #FAFAFA, align: right"]$5.25 [/TD]
[TD="bgcolor: #FAFAFA, align: right"]35,145[/TD]

[TD="align: center"]9[/TD]
[TD="bgcolor: #FAFAFA, align: right"]7,200[/TD]
[TD="bgcolor: #FAFAFA, align: right"]$5.21 [/TD]
[TD="bgcolor: #FAFAFA, align: right"]34,545[/TD]

[TD="align: center"]10[/TD]
[TD="bgcolor: #FAFAFA, align: right"]6,000[/TD]
[TD="bgcolor: #FAFAFA, align: right"]$5.33 [/TD]
[TD="bgcolor: #FAFAFA, align: right"]27,345[/TD]

[TD="align: center"]11[/TD]
[TD="bgcolor: #FAFAFA, align: right"]21,345[/TD]
[TD="bgcolor: #FAFAFA, align: right"]$5.23 [/TD]
[TD="bgcolor: #FAFAFA, align: right"]21,345[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: right"]200,287[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="align: right"]150,287[/TD]

[TD="align: right"][/TD]

[TD="align: center"]16[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]
[TD="align: right"][/TD]
[TD="align: right"] $ 5.37 [/TD]
[TD="align: right"] $ 5.36 [/TD]

</tbody>

Sheet10​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]C1[/TH]
[TD="align: left"]=SUM(A1:$A$11)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]B16[/TH]
[TD="align: left"]=MATCH(A15,C1:C11,-1)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]B17[/TH]
[TD="align: left"]{=SUM(IF(ROW(A1:A11)>B16,A1:A11*B1:B11))/SUM(IF(ROW(A1:A11)>B16,A1:A11))}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]C17[/TH]
[TD="align: left"]{=(SUM(IF(ROW(A1:A11)>B16,A1:A11*B1:B11))+(A15-INDEX(C:C,B16+1))*INDEX(B:B,B16))/A15}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



I changed A6 to 21561 so that I get the same total as you. I left off the 20000 and 30000 on the right since they didn't see to pertain to the question.

In C1 I put the formula
=SUM(A1:$A$11)
and copied it down. It creates a subtotal per line of how many items are included in the list from this point until the end.

Then the B16 formula
=MATCH(A15,C1:C11,-1)
looks down the C column and finds the first number greater than or equal to A15, and returns the row number.

The B17 formula calculates the weighted average of all the items in rows greater than that row.

Since that includes only 140,287 items, that might not be exactly what you want. The C17 formula includes everything from row 2 down, plus 10,000 items from row 1.

These formulas can be simplified/combined some, but first I need to know if this is the actual process that you want. Let me know.

 

[SpillerBD]

The Key is figuring out that only 10,000 of the 60,000 should be considered part of the weighted average.
Using just the A & B columns of the sample data, use the following formula in cell D11 and fill UP.

=MIN(A11,$A$16-(SUM(A$11:$A12)))

Then use that array for the quantity part in the SUMPRODUCT part of the weighted average formula to get that specific weighted avg.

 

[rickgg]

Thank you both very much for the help. I believe I understand what you all are saying, but I did not form the question in an easy manner. I will try again if you all will bear with me, I do appreciate the guidance.


1 A B C D E
2 Bought Sold
3 60,000 $5.15 20,000 $5.60
4 15,271 $5.25 30,000 $5.60
5 4,880 $5.25 10,000 $ 5.55
6 18,500 $5.35
7 44,497 $5.50
8 21,561 $5.45
9 433 $5.20
10 600 $5.25
11 7,200 $5.21
12 6,000 $5.33
13 21,345 $5.23
14
15 200,287 Sum of Purchases 60,000 Sum of Sales
16
17
18 140,287 Net of Purchases and Sales (A18)
19
20
21
22 Initially
23 SUMPRODUCT(A2:A13,B2:B13)/SUM(A2:A13) $5.31
24
25 After Selling 60,000
26 SUMPRODUCT(A3:A13,B3:B13)/SUM(A3:A13) $5.37







So, essentially I am hunting for a formula that would automatically tell me what I own the last or remaining 140,287 (or net of bought and sold) of purchases or row A at on a weighted average basis.

Currently I have to manually adjust the range to much what the net number (140,287) is and Im just looking for a way to automate the process.

 

[rickgg]

[TABLE="width: 509"]
<tbody>[TR]
[TD]1
[/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD="colspan: 2"]Bought
[/TD]
[TD][/TD]
[TD="colspan: 2"]Sold
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD] 60,000
[/TD]
[TD]$5.15
[/TD]
[TD][/TD]
[TD] 20,000
[/TD]
[TD] $ 5.60
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD] 15,271
[/TD]
[TD]$5.25
[/TD]
[TD][/TD]
[TD] 30,000
[/TD]
[TD] $ 5.60
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD] 4,880
[/TD]
[TD]$5.25
[/TD]
[TD][/TD]
[TD] 10,000
[/TD]
[TD]5.55
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD] 18,500
[/TD]
[TD]$5.35
[/TD]
[TD][/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD] 44,497
[/TD]
[TD]$5.50
[/TD]
[TD][/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD] 21,561
[/TD]
[TD]$5.45
[/TD]
[TD][/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD] 433
[/TD]
[TD]$5.20
[/TD]
[TD][/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]10
[/TD]
[TD] 600
[/TD]
[TD]$5.25
[/TD]
[TD][/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]11
[/TD]
[TD] 7,200
[/TD]
[TD]$5.21
[/TD]
[TD][/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]12
[/TD]
[TD] 6,000
[/TD]
[TD]$5.33
[/TD]
[TD][/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]13
[/TD]
[TD] 21,345
[/TD]
[TD]$5.23
[/TD]
[TD][/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]14
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD][/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]15
[/TD]
[TD]200,287
[/TD]
[TD]Sum of Purchases
[/TD]
[TD][/TD]
[TD] 60,000
[/TD]
[TD]Sum of Sales
[/TD]
[/TR]
[TR]
[TD]16
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]17
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]18
[/TD]
[TD]140,287
[/TD]
[TD="colspan: 3"]Net of Purchases and Sales (A18)
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]19
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]20
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]21
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]22
[/TD]
[TD]Initially
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]23
[/TD]
[TD="colspan: 3"]SUMPRODUCT(A2:A13,B2:B13)/SUM(A2:A13)
[/TD]
[TD] $ 5.31
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]24
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]25
[/TD]
[TD="colspan: 2"]After Selling 60,000
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]26
[/TD]
[TD="colspan: 3"] SUMPRODUCT(A3:A13,B3:B13)/SUM(A3:A13)
[/TD]
[TD] $ 5.37
[/TD]
[TD]

[/TD]
[/TR]
</tbody>[/TABLE]


Shoot I have a hard time even posting the table on here.

 

[SpillerBD]

Inventory Valuation.
There's FIFO, LIFO and Moving Average (AVCO).
See the various topic at Accounting Explained.
(This topic may be answered here but if the end use ties to any tax purpose you may need to verify with your CPA.)

 

[Eric W]

SpillerBD's comment is certainly valid. My accounting training is far past. But if it turns out that the method you asked for is valid, then my formulas from post #4 (specifically the C1, B16, and C17 formulas) should do what you want, after adjusting the ranges.