Volume Weighted Average

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

carl - 27 Sep 2007 17:03 GMT

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.

Reply to this Message

Max - 27 Sep 2007 17:16 GMT

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

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

> My data table looks like this:
>
[quoted text clipped - 17 lines]
>
> Thank you in advance.

Reply to this Message

Gary''s Student - 27 Sep 2007 17:23 GMT

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.

Signature

Gary''s Student - gsnu200747

> My data table looks like this:
>
[quoted text clipped - 17 lines]
>
> Thank you in advance.

Reply to this Message

JE McGimpsey - 27 Sep 2007 17:26 GMT

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)

> My data table looks like this:
>
[quoted text clipped - 17 lines]
>
> Thank you in advance.

Reply to this Message