View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
John Robbins
Posts: n/a
Default how do i find unique avg buy prices in multiple group of buys/sell

Thanks for looking... (trying to create a macro to go through a report and
create weighted avg for unique groups of data)

I have a report listing all the buys and sells for multiple products. The
report is sorted first by item name, then by transaction type (buy or sell,
so all buys are contiguous). There is a blank row separating each unique
product name.

I want to create a macro to go through all the rows and calculate the
weighted average cost of each product's buys and insert that value into a
column. if product JR has 12 transactions (3 buys 9 sells) I want the
weighted average of the 3 buys inserted into all 12 rows.

Here's my problem, i know how to calculate the weighted avg, but i do not
know how to AUTOMATICALLY create multiple unique weighted avgs for unique
data sets. The blank row between product types (each group of buys and
sells) can be used as an indicator in the iterations, but coding the solution
is beyond my grasp right now.

Check out my a snapshot of my dataset below:

4-May Product X B 10 98.75
4-May Product X B 70 98.50 ?1
4-May Product X S (150) 98.95 ?1

4-May Product y B 20 98.75
4-May Product y B 50 98.65 ?2
4-May Product y S (50) 99.05 ?2

"?1" and "?2" should be distinct, unique values.