Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

# Thread: Multi-Condition Volume Weighted Average Calculation

1. I agreed to these rules
Join Date
5th February 2005
Posts
7

## Multi-Condition Volume Weighted Average Calculation

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.

Excel Video Tutorials / Excel Dashboards Reports

2. Member
Join Date
11th July 2006
Posts
36

## Re: multi-condition volume-weighted average calculation with sumproduct

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.

Excel Video Tutorials / Excel Dashboards Reports

3. Super Moderator
Join Date
18th November 2004
Location
God's Own Country
Posts
4,478

## Re: multi-condition volume-weighted average calculation with sumproduct

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

4. ## Re: multi-condition volume-weighted average calculation with sumproduct

Hey Kris, thought were on holidays?

5. I agreed to these rules
Join Date
5th February 2005
Posts
7

## Re: multi-condition volume-weighted average calculation with sumproduct

Originally Posted by 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.
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.

Excel Video Tutorials / Excel Dashboards Reports

6. Super Moderator
Join Date
18th November 2004
Location
God's Own Country
Posts
4,478

## Re: Multi-Condition Volume Weighted Average Calculation

Hi,

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

HTH

8. ## Re: Multi-Condition Volume Weighted Average Calculation

New question split off to here

9. Established Member
Join Date
24th March 2005
Posts
507

## Re: Multi-Condition Volume Weighted Average Calculation

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.

Excel Video Tutorials / Excel Dashboards Reports

10. I agreed to these rules
Join Date
5th February 2005
Posts
7

## Re: Multi-Condition Volume Weighted Average Calculation

Originally Posted by Dave Hawley
My sincere and deepest apologies

Excel Video Tutorials / Excel Dashboards Reports

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•
porno