Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old May 4th 06, 06:23 PM 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:

DATE TYPE BUY/SELL QTY PRICE
AVG BUY PX
--------------------------------------------------------------------------------------------
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   Report Post  
Old May 4th 06, 06:44 PM 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

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:

DATE TYPE BUY/SELL QTY PRICE
AVG BUY PX
--------------------------------------------------------------------------------------------
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.



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Can't find formula for largest value in multiple cells telewats New Users to Excel 5 January 9th 06 08:26 PM
How do I group multiple data entries in sub-groups wendyrose1034 Excel Worksheet Functions 1 July 27th 05 04:46 PM
Find Multiple Maximums cdavidson Excel Discussion (Misc queries) 1 July 26th 05 11:55 PM
Subtotal of Subtotal displays Grand Total in wrong row Thomas Born Excel Worksheet Functions 5 January 6th 05 02:46 PM
How can I find the max in each group? LRATLARSON Excel Worksheet Functions 4 November 23rd 04 05:27 PM


All times are GMT +1. The time now is 09:58 PM.

Powered by vBulletin® Copyright ©2000 - 2022, Jelsoft Enterprises Ltd.
Copyright 2004-2022 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017