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.

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.