+ Reply to Thread
Results 1 to 4 of 4

Use VBA to generate an Average formula on a worksheet

  1. #1
    Forum Contributor
    Join Date
    09-21-2014
    Location
    Midwest USA
    MS-Off Ver
    2010
    Posts
    321

    Use VBA to generate an Average formula on a worksheet

    I want to create VBA code which will create an average formula on a worksheet for items incrementally purchased or sold during a day. I thought I had this solved, but discovered an error in my design that I’m not sure how to fix.

    In the attached workbook, Col A-L represents imported data.
    Col N is a helper column used to create a unique identifier for the items.
    Col O is a helper column for an array formula that removes duplicates from Col N
    Col P is an informational column – sums the total items of all the identical identifiers in Col O
    S2, S3 is the erroneous average formulas generated by my existing macro
    Cols T-V finds the price associated with each individual occurrence in col N
    W2 is the correct average price manually calculated (This is what S2 should calculate)
    Col Y calculates the total number of occurrences of the Col O identifier in Col N. Originally this was intended to identify how many total columns starting with Col T would contain the array formula for price increments – so part of the solution will require a new way to determine the number of columns needed.

    The problem I’m having trouble solving is that the second increment in U2 has a quantity of two at 2.83, so the actual average formula should be “=AVERAGE(2.88,2.83,2.83)” vs. “=AVERAGE(2.88,2.83)”

    Any time that any increment has a quantity greater than one, I need to populate that price in an equivalent number of columns starting with Col T.

    Sorry for the long post – hopefully the detail is easier to understand than trying to digest the various formulas directly. Thanks for reading and I appreciate any suggestions.

  2. #2
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    8,625

    Re: Use VBA to generate an Average formula on a worksheet

    This will return a weighted average of the column N items that match O2

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Surround your VBA code with CODE tags e.g.;
    [CODE]your VBA code here[/CODE]
    The # button in the forum editor will apply CODE tags around your selected text.

  3. #3
    Forum Contributor
    Join Date
    09-21-2014
    Location
    Midwest USA
    MS-Off Ver
    2010
    Posts
    321

    Re: Use VBA to generate an Average formula on a worksheet

    Indeed it does – I didn’t even think of that approach and that will solve my problem for new entries since I group them by the opening date. Unfortunately, it also creates a new problem relative to positions which close over more than one day. Actually two problems but the first is relatively simple to solve.

    The first, easy problem is that I use the average formula in a different workbook because I don’t retain all the daily imported information in the example workbook. I could solve this buy copying the resulting average price rather than the average formula to that workbook, but unfortunately that wouldn’t work for something that closes over multiple days. For example if I closed two of the three items from O2 for 3.00 and 3.10 on one day and the last one for 2.60 on a different day, the average would be 2.90 (“=AVERAGE(3,3.1,2.6)”)

    Entering it manually it’s a simple matter to edit the formula manually from “=AVERAGE(3,3.1)” to “=AVERAGE(3,3.1,2.6)” but don’t know how I could easily average-in the final price of 2.60 to get the true average of $2.90 using the SumProduct solution. Any ideas?

    Thanks for your assistance.

  4. #4
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    8,625

    Re: Use VBA to generate an Average formula on a worksheet

    Quote Originally Posted by aquixano View Post

    The first, easy problem is that I use the average formula in a different workbook because I don’t retain all the daily imported information in the example workbook. I could solve this buy copying the resulting average price rather than the average formula to that workbook, but unfortunately that wouldn’t work for something that closes over multiple days. For example if I closed two of the three items from O2 for 3.00 and 3.10 on one day and the last one for 2.60 on a different day, the average would be 2.90 (“=AVERAGE(3,3.1,2.6)”)
    .
    Record the daily weighted average and the daily quantity =Average(3,3.1)=3.05 Qnty (2), =Average(2.6) qnty (1).
    Then do another weighted average of the daily weighted averages
    =((3.05*2)+(2.6*1))/(2+1) = 2.9
    This is what the SUMPRODUCT formula does

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1