Multi-Condition Volume Weighted Average Calculation

[schpoink]

Hello,

I am trying to figure a way -- preferably in a single-cell formula -- to calculate a conditional volume-weighted average of a series of associated prices and quantities located in two separate columns based on the date (or dates) the transactions occurred.



I can do a simple Vol. Weighted Average (VWA) of all the prices and quantities over the five-day period with the following formula: (I have created named ranges for the price column (A1:A30=price), the quantity column (B1:B30=quantity) and the date column (C1:C30=date):


=SUMPRODUCT(price, quantity)/SUM(quantity)


Row Col. A Col. B Col. C

Price Quant. Date

1 $4.650 5.0 12/11
2 $4.640 5.0 12/11
3 $4.640 5.0 12/11
4 $4.550 5.0 12/11
5 $3.881 10.5 12/12
6 $3.550 5.0 12/12
7 $3.550 5.0 12/12
8 $3.550 5.0 12/12
9 $3.460 5.0 12/12
10 $3.430 5.0 12/12
11 $3.430 5.0 12/12
12 $3.420 5.0 12/12
13 $3.420 5.0 12/12
14 $3.380 5.0 12/12
15 $3.550 1.5 12/12
16 $3.550 1.5 12/12
17 $4.046 1.0 12/12
18 $3.540 5.0 12/13
19 $3.540 5.0 12/13
20 $3.530 5.0 12/13
21 $3.450 5.0 12/13
22 $3.640 10.0 12/14
23 $3.640 5.0 12/14
24 $3.615 5.0 12/14
25 $3.585 5.0 12/14
26 $3.570 5.0 12/14
27 $3.720 4.0 12/14
28 $3.720 4.0 12/15
29 $3.615 2.3 12/15
30 $3.615 2.3 12/15
31
32
33 12/11
34 12/12
35 12/13
36 12/14
37 12/15


But, I need to calculate volume-weighted averages separately for the transactions on just one, two and three of the days in various combinations.

I've been able to do the calculation for ONE of the days with an array formula that checks the date value adjacent to the price and volume columns using an offset reference like this:

{=SUMPRODUCT((IF(OFFSET(Price,,2)=$A$33,Price,"no")),(IF(OFFSET(Quantity,,1)=$A$33,Quantity,"no")))/SUM(IF(OFFSET(Quantity,,1)=$A$33,Quantity,"no"))}

But I can't get it to work using multiple date conditions to calculate the VWA for two and three or more of the days. For example, if I try to calculate a VWA for only the first three days of the five-day period by testing whether a date is greater than or equal to 12/11 and less than or equal to 12/13, I get a #VALUE! error with the following:

{=SUMPRODUCT((IF(AND((OFFSET(Price,,2)>=$A33),(OFFSET(Price,,2)<=$A35)),Price,"no")),(IF(AND((OFFSET(Quantity,,1)>=$A33),(OFFSET(Quantity,,1)<=$A35)),Quantity,"no")))/SUM(IF(AND((OFFSET(Quantity,,1)>=$A33),(OFFSET(Quantity,,1)<=$A35)),Quantity,"no"))}

Can anyone tell me what I am doing wrong, or suggest another way to do this calculation using a single cell or two? Am I approaching this entirely wrong? Is there a simpler way?

This is my first post, please forgive my lame formatting.

[Mark Wyld]

in general {=SUM(IF(AND(...)))} is going to fail. Array formulae don't like multiple criteria. The alternative you seek is

{=SUM((date<=condition1)*(date>=condition2)*(<other criterion>)*value)}


Hope that makes sense to you.

[Krishnakumar]

Hi schpoink,

Welcome to OzGrid!!

Try,

=SUMPRODUCT(SUBTOTAL(9,OFFSET(Quantity,ROW(Quantity)-ROW(A1),,1)),SUBTOTAL(9,OFFSET(Price,ROW(Price)-ROW(A1),,1)),--(Date>=A33),--(Date<=A35))/SUMPRODUCT(SUBTOTAL(9,OFFSET(Quantity,ROW(Quantity)-ROW(A1),,1)),--(Date>=A33),--(Date<=A35))

HTH

[Dave Hawley]

Hey Kris, thought were on holidays?

[schpoink]

in general {=SUM(IF(AND(...)))} is going to fail. Array formulae don't like multiple criteria. The alternative you seek is

{=SUM((date<=condition1)*(date>=condition2)*(<other criterion>)*value)}


Hope that makes sense to you.

Thanks so much Mark. Structuring the SumProduct like this does the trick perfectly!

I'm scratching my head trying to figure out where I would have been able to figure this out reading the manual.

[Krishnakumar]

Hi,

I tried your suggestion but only got #VALUE! error.

See the attachment.

HTH

[Dave Hawley]

schpoink, please read the rules you are bound by in regards to new questions being asked.

[Dave Hawley]

New question split off to here

[Fin Fang Foom]

Based on Krishnakumar example workbook. This might work.



=SUMPRODUCT(((C2:C31>=A34)*(C2:C31<=A36)*(A2:A31)),B2:B31)/(SUMPRODUCT((C2:C31>=A34)*(C2:C31<=A36)*(B2:B31)))


Hope it helps.

[schpoink]

schpoink, please read the rules you are bound by in regards to new questions being asked.

My sincere and deepest apologies

Thanks for your help