1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Weighted Average with Criteria Using VBA

Discussion in 'VBA Macros' started by Squat, Mar 21, 2017.

  1. Squat

    Squat New Member

    Messages:
    14
    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.
    Detail, I do this via formula, the problem is that they are large files (600,000 lines) and have been too time consuming.
    Follow the files for better understanding.
    Sorry, I do not speak English, and I'm using google translator.
    Thank you for your attention.

    Attached Files:

  2. vletm

    vletm Well-Known Member

    Messages:
    2,544
    @Squat
    How do You get:
    Product 101 | Jan-17 | 5,52 | or | Feb-17 | 6,00 |
    Which values gives those Your results?
    Your data values are as below...
    Screen Shot 2017-03-21 at 20.02.43.png
  3. Squat

    Squat New Member

    Messages:
    14
    Hi vletm,

    So for example:
    For product "101" of store "A" in the month of January / 17 the weighted average should be the sum of (each quantity x each unit price) / sum of the quantity.
    In the Report folder the result for the product "101" in Jan / 17 should be equal to 5.52 and in Feb / 17 should be equal to 6.00
    I am trying to do this for each product (101,102,103,104,105) of each store (A, B, C, D and E) for each month / year.
    And calculate the weighted average for each product including all stores together.
    And paste each result into the Report folder in the worksheet of each product.
    I do not know where I'm going wrong, whether it's in the declaration of variables or in the calculation conditions.
    I am a beginner in VBA.

    Thank you for your attention.
  4. vletm

    vletm Well-Known Member

    Messages:
    2,544
    @Squat
    Okay!
    Are You looking something like below?
    Screen Shot 2017-03-22 at 09.53.34.png
    That no need VBA!
  5. Squat

    Squat New Member

    Messages:
    14
    Hi vletm,

    So, with said before, 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.
    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.
    Thank you for your attention.
    Sorry for my english, I'm using google translator.
    Last edited: Mar 22, 2017
  6. vletm

    vletm Well-Known Member

    Messages:
    2,544
    @Squat ... You have written that You have used formulas - no marks of Pivot?
    Anyway with formulas ... not good at all!
    I'm not so sure how do this work with 'VBA', but it is possible to with it too.
    Can You send large data file? I could test it first.
    ... I tested it with ~850'000 rows and it takes about 6sec?
    Is it too long time?
    With 'VBA', it will take much much longer!
  7. vletm

    vletm Well-Known Member

    Messages:
    2,544
    @Squat ... continue with formulas:
    1) 'Advance Filter' to get Periodo','Store'&'Product' ('Q','R','S')
    2) Solve 'Amount's sums per row ('T')
    3) Solve 'AmountxUnit Price' per row ('U')
    4) ('U')/('T') will be Your answer ('V')
    Screen Shot 2017-03-22 at 20.20.06.png
    of course previous would do with VBA too.
    >> About Your code ...
    it is challenge to solve like Your steps!
    1) If You try to solve 'weighted averages' row by row ... it is possible BUT
    how do You add 2nd 'Periodo-Store-Product'-combination to same combination?
    Jan-17|Store|Product: 1st gives 6 and 2nd gives 5 but Your answer is 5,52!
    2) You used variables which didn't have value (=0)...

    >> Ideas ... Questions?
  8. Squat

    Squat New Member

    Messages:
    14
    Hi Vletm,

    Sorry for the delay in answering, my internet is in trouble
    I use excel 2007 e the computer I use has an Intel Core i3 processor and 4GB of RAM.
    I calculate through formula sumproduct, happens that is very slow, because in the report folder are almost 500 worksheets (one per product), and in each worksheet are almost a thousand cells with this formula.
    They are data of almost 15 years, I will divide by year to see if it gets faster.
    I am not able to attach the file with my formula, so as I improve my internet I attach the file for you to see.
    The calculation of the weighted average is as follows:
    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

    Once again grateful for the attention.
  9. vletm

    vletm Well-Known Member

    Messages:
    2,544
    @Squat
    1) If You have ... 500sheets and so on then this case would be different.
    But from Your sent data and my previous snapshot
    here are formulas:
    for T2 =SUMIFS(D:D;$A:$A;$Q2;$B:$B;$R2;$C:$C;$S2)
    for U2 =SUMIFS(F:F;$A:$A;$Q2;$B:$B;$R2;$C:$C;$S2)
    for V2 =U2/T2
    2) that massive sheets ... maybe could do other way too
    ... those formulas makes s - l - o - w and
    hmm... why do You need those formulas for many years?
    ... why ~500 sheets in one file? or is there ~500files?
    Anyway, no matter, even both cases it would make quicker (with Excel too).
    I need more details to try to make this easier for You (and Your PC).
    3) Yes, ((13 * 6) + (12 * 5)) / (13 + 12) which will be equal to 5.52
    but then there could be 3rd row of data ... how to continue?
    Like with Jan-17 | A | 102 ...?
    Screen Shot 2017-03-23 at 10.00.28.png
    Here is my samples for You 'data'-file.
    > Both 'answers' can move to other file is needs.
  10. Squat

    Squat New Member

    Messages:
    14
    Hi Vletm,

    Sorry for the delay in answering.


    There are some limitations in Excel regarding links. There are certain functions that can not maintain the value when linked to workbooks that are closed (DataBase.xlsx). They are: SUMIF, SUMIFS, COUNTIF, COUTIFS, AVERAGEIF, AVERAGEIFS and INDIRECT are not updated when linked to other closed workbooks (DataBase.xlsx) and still lose the value when the file is opened (Report).

    So I make use of sumproduct, follow the DataBase and Report files.

    Save the two files to the same folder.

    Open the Report file and see that the link remains as the DataBase file (even closed).

    Thanks for listening.

    Attached Files:

    Last edited: Mar 23, 2017
  11. vletm

    vletm Well-Known Member

    Messages:
    2,544
    @Squat
    hmmm ... Do You change case?
    Your original case has ready done that 'Data.xlsm'-file - okay?
    And You asked 'how to get those values I did as above' - okay?
    I checked Your new 'Report.xlsx' ... Oh Yeah!

    Why You need those links?
    What would happen,
    if someone delete ... links no help! .. or just cut links.
    for example You should show 'Report.xlsx' somewhere
    without 'Data.xlsm or DataBase.xlsx'.
    It's Okay (for You) if You would like to use massive formulas ...
    but I don't do so!

    If You would like to get 'lighter version' then
    1st solve 'Data.xlsm' once + update added data then needed
    (older raw data NO NEED formulas after update 'Data.xlsm')
    2nd Pivot-table
    3rd move data to 'Report.xlsx'-file
  12. Squat

    Squat New Member

    Messages:
    14
    Hi Vletm,

    So I did not change the case, I actually just changed the name of the Data.xlsm file to DataBase.xlsx, sorry for the confusion.

    The DataBase file is in a directory and in that file I can not use formulas, it only serves to be the same database. This file that has data from 2002 to 2017 with almost 600,000 lines, I ended up dividing one file per year, 2002, 2003 ... successively, each file being with some 40,000 lines.

    The Report file is in another directory, for now I'm using the formula of sumproduct. With the division made in DataBase, the calculation was very fast.

    I'll keep trying to make a VBA code, when I can send the code to you.

    Again, grateful for the attention.
  13. vletm

    vletm Well-Known Member

    Messages:
    2,544
    @Squat
    Why You need those links?
    ... or someone will update 2003 year data? No way!

    You didn't translate:
    If You would like to get 'lighter version'
    .. and 'my 6sec' is too slow?


    Code ... I saw 'code' from Your original file and
    You didn't check any of my ideas.
  14. Squat

    Squat New Member

    Messages:
    14
    Hi Vletm,

    Yes, no one will change the old data, so I ended up deciding to split the data by year (each file was about 40,000 lines).

    As for speed, I had already tried to use the PivotTable, but it still slowed down, the screen faded, excel did not respond, so I started to use formula sumproduct.

    Actually, what I'm trying to do is not to be called a code, as I said before, I'm a beginner in VBA, that's why I insist on the code, I want to learn.

    As for his ideas, I had already tended but the process still took a lot.

    Again, grateful for your attention.
  15. vletm

    vletm Well-Known Member

    Messages:
    2,544
    @Squat .. try to translate
    1) As I tested Your data with ~850'000 rows with Pivot, It took 6 secs .. slow?
    2) I tested also other way ... as #9 Reply ... with formulas heavier
    3) and with both ways, it is possible to copy those results 'anywhere'
  16. Squat

    Squat New Member

    Messages:
    14
    Hi Vletm,

    So as I said before, in the Report workbook I have almost 500 worksheets, I think that's why it slows down.

    In the Data workbook are 600,000 rows with 55 columns, all filled in, it's a heavy file, so I ended up dividing the data into files per year (with about 40,000 rows and 55 columns.) So I did as you did using the PivotTable, It was fast.

    As for copying, I prefer it to be automated either by formula or by vba. It's just a matter of taste, I try to avoid copying, I try to do everything via formula.

    Since I still do not know vba, I'm using the same formula.

    Of course your ideas were good yes.

    Thank you for your attention.
  17. vletm

    vletm Well-Known Member

    Messages:
    2,544
    @Squat
    Report:
    Why have to have ~500 sheets? How many sheets can You see in one time?
    Data:
    You have shown just 'really' sample of data! Oh! That has 'only' few columns, just for to get data for 'report'.
    > It's challenge to try to help is You cannot show/tell more real sample! <
    > Have You think that everything is important in data-file? <
    To 'move' information from 'data' to report:
    Do not use 'only' formulas! Those makes just heavier... heavier...
    If You could send better file for me, then I could try to figure something.
  18. Squat

    Squat New Member

    Messages:
    14
    Hi Vletm,

    In Workbook Report are 500 worksheets (one for each product).

    The files I've attached are examples with dummy data, because I can not show my company data. The solution given here in the forum I reply to the 500 worksheets in my work without problems.

    Actually the columns that I need for the weighted average calculation are 5 out of 55 in the Data workbook.

    By formula or PivotTable, the problem is already solved and, as I told you before, for now I use formula, my intention is to pass everything to vba. That's why I created a post in the vba forum. I'm looking for a solution in vba.

    Thank you for your attention.
  19. vletm

    vletm Well-Known Member

    Messages:
    2,544
    @Squat
    Report: I did two questions. Why..? and How..?
    Data: Are those sample file columns as same positions as in real data?
    And as I wrote too:
    If You could send better file for me, then I could try to figure something.
  20. vletm

    vletm Well-Known Member

    Messages:
    2,544
    @Squat
    Here is sample's for You.
    If You have 'automatic calculation' on then it will make many things s-l-o-w!
    1) You have to have somewhere that 'Report.xlsx'-file.
    2) Press one of those buttons form 'Data'-sheet
    ( both has own way to calculate 'Report'-file )
    3) Select that 'Report.xlsx'-file (... better to use that file You sent here!)
    4) After Msg with time, Your 'Report'-file has 'updated' and 'saved'
    5) Still wondering about my previous questions!

    Attached Files:

  21. vletm

    vletm Well-Known Member

    Messages:
    2,544
    @Squat
    I noticed few things which should edit ...
    (= use only this file! )

    Attached Files:

  22. Squat

    Squat New Member

    Messages:
    14
    Hi Vletm,

    Sorry for the delay in answering (health problems).

    You are a VBA expert, great, yeah !!!

    As soon as I get back to work I'm going to test the file with the 500 worksheets and give it a return.

    Thank you very much.
  23. vletm

    vletm Well-Known Member

    Messages:
    2,544
    ... remember backups before do something!
    ... and still I'm waiting answers
  24. Squat

    Squat New Member

    Messages:
    14
    Hi Vletm,

    I went back to work, your vba code ran with great perfection and very, very fast !!!

    How I used your VBA code:

    I imported the database here from the company, just the six columns that I use to calculate the weighted average for your Data file (with VBA) and rolled the code.

    As for your questions (about links, PivotTable, use of formulas, data temporality ...), I think I've answered, but feel free to ask about something.

    Thank you for your attention.
  25. vletm

    vletm Well-Known Member

    Messages:
    2,544
    You wrote: I think I've answered, but feel free to ask about something.
    I have asked:
    Why have to have ~500 sheets?
    How many sheets can You see in one time? (...If one sheet, then why 500 sheets? )

    > If You import that database - no! - why?
    What would happen next year? Every year own 500-sheets ... I hope no!

Share This Page