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