Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 10 of 10

Thread: Multi-Condition Volume Weighted Average Calculation

  1. #1
    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. #2
    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. #3
    Join Date
    18th November 2004
    Location
    God's Own Country
    Posts
    4,353

    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. #4
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,716

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

    Hey Kris, thought were on holidays?

  5. #5
    Join Date
    5th February 2005
    Posts
    7

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

    Quote 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. #6
    Join Date
    18th November 2004
    Location
    God's Own Country
    Posts
    4,353

    Re: Multi-Condition Volume Weighted Average Calculation

    Hi,

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

    HTH
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

  7. #7
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,716

    Re: Multi-Condition Volume Weighted Average Calculation

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

  8. #8
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,716

  9. #9
    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. #10
    Join Date
    5th February 2005
    Posts
    7

    Re: Multi-Condition Volume Weighted Average Calculation

    Quote Originally Posted by Dave Hawley
    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

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Weighted Average Calculation
    By bigmattiet in forum EXCEL HELP
    Replies: 6
    Last Post: September 29th, 2007, 07:05
  2. Weighted Average...
    By jemagnussen in forum EXCEL HELP
    Replies: 15
    Last Post: February 16th, 2006, 06:19
  3. Weighted Average
    By Kim1978 in forum EXCEL HELP
    Replies: 2
    Last Post: April 8th, 2005, 05:52
  4. Weighted Average
    By Pranob in forum EXCEL HELP
    Replies: 2
    Last Post: October 28th, 2004, 14:19
  5. Volume calculation
    By Kurt in forum Excel and/or Access Help
    Replies: 1
    Last Post: February 21st, 2003, 07:56

Bookmarks

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