# Use VBA to generate an Average formula on a worksheet

1. ## 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.  Register To Reply

2. ## 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:  `Please Login or Register  to view this content.`  Register To Reply

3. ## 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?  Register To Reply

4. ## Re: Use VBA to generate an Average formula on a worksheet Originally Posted by aquixano 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  Register To Reply