Weighted Average with ReDim preserve

    Hi,


    A friend here in the forum helped me with a weighted average code.
    Now I'm trying to calculate the weighted average monthly with the following criteria:
    When there are 3 or more suppliers for each product in each store per month.
    For example:
    The weighted average for StoreA Apple product on Jan / 17 when there are 3 or more suppliers:



    (each quantity * each unit price) / sum quantity



    ((33 * 5,00) + (23 * 3,00) + (25 * 3,00) + (27 * 5,00)) / (5,00 + 3,00 + 3,00 + 5,00) must be equal to 4.111111



    Then paste this price into the Report worksheet
    It includes some lines to make this new criterion, however the code is not calculating, would like to know where I'm going wrong?
    I am using the ReDim preserve statement.
    Thank you for your attention.
    Follow the files for better understanding

    Re: Weighted Average with ReDim preserve


    I can't access the worksheets just now as I'm at work.


    But for starters I'd be more inclined to use a combination of =sumproduct and =sum to do the calculation.


    Assuming the data is actually on the sheet somewhere:



    [TABLE="class: grid, width: 500"]

    [tr]


    [td]

    Row/Columns

    [/td]


    [td]

    A

    [/td]


    [td]

    B

    [/td]


    [/tr]


    [tr]


    [td]

    1

    [/td]


    [td]

    33

    [/td]


    [td]

    5.00

    [/td]


    [/tr]


    [tr]


    [td]

    2

    [/td]


    [td]

    23

    [/td]


    [td]

    3.00

    [/td]


    [/tr]


    [tr]


    [td]

    3

    [/td]


    [td]

    25

    [/td]


    [td]

    3.00

    [/td]


    [/tr]


    [tr]


    [td]

    4

    [/td]


    [td]

    27

    [/td]


    [td]

    5.00

    [/td]


    [/tr]


    [/TABLE]


    =SUMPRODUCT(A1:A4,B1:B4)/SUM(B1:B4)


    Will return the same as:


    =((33 * 5,00) + (23 * 3,00) + (25 * 3,00) + (27 * 5,00)) / (5,00 + 3,00 + 3,00 + 5,00)


    Or in a parameterised format:


    =((A1* B1) + (A2 * B2) + (A3 * B3) + (A4 * B4)) / (B1 + B2 + B3 + B4)

    Re: Weighted Average with ReDim preserve


    Hi TheGlovner,


    Thanks for the feedback,



    So using "sumproduct" works, but I intend to do this calculation when the number of suppliers per store and per product is equal to or greater than 3.



    As in the table below:


    [TABLE="width: 434"]

    [tr]


    [td]

    Period

    [/td]


    [td]

    Supplier

    [/td]


    [td]

    Store

    [/td]


    [td]

    Region

    [/td]


    [td]

    Product

    [/td]


    [td]

    Quantity

    [/td]


    [td]

    Price

    [/td]


    [/tr]


    [tr]


    [td]

    jan/17

    [/td]


    [td]

    105

    [/td]


    [td]

    StoreA

    [/td]


    [td]

    South

    [/td]


    [td]

    Apple

    [/td]


    [td]

    33

    [/td]


    [td]

    5,00

    [/td]


    [/tr]


    [tr]


    [td]

    jan/17

    [/td]


    [td]

    103

    [/td]


    [td]

    StoreA

    [/td]


    [td]

    South

    [/td]


    [td]

    Apple

    [/td]


    [td]

    23

    [/td]


    [td]

    3,00

    [/td]


    [/tr]


    [tr]


    [td]

    jan/17

    [/td]


    [td]

    103

    [/td]


    [td]

    StoreA

    [/td]


    [td]

    South

    [/td]


    [td]

    Apple

    [/td]


    [td]

    25

    [/td]


    [td]

    3,00

    [/td]


    [/tr]


    [tr]


    [td]

    jan/17

    [/td]


    [td]

    101

    [/td]


    [td]

    StoreA

    [/td]


    [td]

    South

    [/td]


    [td]

    Apple

    [/td]


    [td]

    27

    [/td]


    [td]

    5,00

    [/td]


    [/tr]


    [/TABLE]

    Condition: When the number of suppliers per store and per product is >=3
    In this case we had 3 suppliers: 101, 103 and 105


    Average the Product "Apple" in StoreA in Jan/17 equal 4,11111


    Thank you for your attention.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login