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.

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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"carl" wrote:
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.

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.
--
Gary''s Student - gsnu200747


"carl" wrote:

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.

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)

In article ,
carl wrote:

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.