Weighted Average with Criteria Using VBA

  • Hi,

    I'm trying to calculate the weighted average with some criteria (period, store and product) of the Data file and for each average of each product of each store in a certain period, I wanted to paste it into another Workbook (Report) in the worksheets of the respective products.

    The problem is that the code I am writing, is not calculating based on these criteria (returns 0.00), if someone can tell me where I am wrong (I am a beginner in VBA), I am grateful.

    I already used PivotTables and today I do this calculation through formulas (sumproduct), both take a long time to calculate, because the data file is very large (from 400,000 to 600,000 lines), the computer screen arrives To be dimmed.

    For example:
    The weighted average for product "101" of store "A" in Jan / 17 is:

    (each quantity * each unit price) / quantity sum

    ((13 * 6) + (12 * 5)) / (13 + 12) which will be equal to 5.52

    I'm trying to do for each product per store per month / year.

    And do for each product with all the stores together per month / year.

    My intention is to automate in VBA because the time gain would be better. And since I'm starting in VBA, it would be a learning experience for me.

    Sorry for my english, I'm using google translator.

    Follow the files for better understanding.


    Thank you for your attention.

  • Re: Weighted Average with Criteria Using VBA

    How about this. The code sits in the Data file and you will need to have your Report file open also

  • Re: Weighted Average with Criteria Using VBA

    Hi Bryce,

    Very perfect.

    I just have to thank you, for me, that I am a beginner in VBA was an great learning.

    I took the liberty of adjusting the lines: ws.Cells (x, y) = 0 for ws.Cells (x, y) = "-" and ws.Cells (x, 7) = 0 for ws.Cells (x, 7) = "-".

    Sorry for my english, I use google translator.

    Thank you one more time!

  • Re: Weighted Average with Criteria Using VBA

    Hi Bryce,

    Thanks again.

    I'll mark the thread as solved.

    Health and peace for you.

Participate now!

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