Announcement

Collapse
No announcement yet.

Unconfigured Ad Widget

Collapse

Multi-Condition Volume Weighted Average Calculation

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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.

  • #2
    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.

    Comment


    • #3
      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
      Kris

      ExcelFox

      Comment


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

        Hey Kris, thought were on holidays?

        Comment


        • #5
          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.

          Comment


          • #6
            Re: Multi-Condition Volume Weighted Average Calculation

            Hi,

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

            HTH
            Attached Files
            Kris

            ExcelFox

            Comment


            • #7
              Re: Multi-Condition Volume Weighted Average Calculation

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

              Comment


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

                New question split off to here

                Comment


                • #9
                  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.

                  Comment


                  • #10
                    Re: Multi-Condition Volume Weighted Average Calculation

                    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

                    Comment

                    Trending

                    Collapse

                    There are no results that meet this criteria.

                    Working...
                    X