Volume Weighted Average

[Guest]

My data table looks like this:

Date Price Volume
20070904 17.3 10
20070904 17.3 10
20070904 23.8 5
20070904 23.8 5
20070905 5.7 10
20070905 5.7 10
20070905 1.55 11
20070905 1.55 11

I am looking for a formula for this table

Date WeightedPrice
20070904
20070905

So for each day, calculate the volume weighted average price.

Thank you in advance.

 

[Guest]

Assume source table in A1:C9, as posted

and the dates below are in E2:E3

Date WeightedPrice
20070904
20070905

In F2:
=SUMPRODUCT(--(A$2:A$9=E2),B$2:B$9,C$2:C$9)/SUMPRODUCT(--(A$2:A$9=E2),C$2:C$9)
Copy to F3

---

 

[Guest]

Not sure about the weighted average, but look at:

=SUMPRODUCT(--(A2:A9=20070904),(B2:B9),(C2:C9))/COUNTIF(A2:A9,20070904)

if 146 is o.k.

 

[JE McGimpsey]

One way:

Assuming that your data table is in J:K and your summary table in A:B:

=SUMPRODUCT(--($J$2:$J$1000=A2), $K$2:$K$1000, $L$2:$L$1000) /
SUMIF($J$2:$J$2000, A2, $L$2:$L$1000)