Remember Me?

#1
Posted to microsoft.public.excel.worksheet.functions
 John Robbins Posts: n/a
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
?1
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
?2
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.
#2
Posted to microsoft.public.excel.worksheet.functions
 John Robbins Posts: n/a
how do i find unique avg buy prices in multiple group of buys/sell

Important to note that the number of buys/sells for each product type (rows
per product type) can be anywhere from 2 to upwards of 50.
Thanks

"John Robbins" wrote:

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
?1
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
?2
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.

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post LRATLARSON Excel Worksheet Functions 5 April 29th 23 11:43 AM telewats New Users to Excel 5 January 9th 06 08:26 PM wendyrose1034 Excel Worksheet Functions 1 July 27th 05 04:46 PM cdavidson Excel Discussion (Misc queries) 1 July 26th 05 11:55 PM Thomas Born Excel Worksheet Functions 5 January 6th 05 02:46 PM

All times are GMT +1. The time now is 01:18 PM.