Hi,
Why not use PivotTables? That would be the most natural way. However, there are more than one ways to do it, a macro and/or UDF or even formulae. Pivots: make Party Name, Order No. as Row Fields. And Average Of W. Days as data field. Regards Ajit From: excel-macros@googlegroups.com [mailto:excel-mac...@googlegroups.com] On Behalf Of Abhishek Jain Sent: Monday, April 20, 2009 6:43 PM To: MS EXCEL AND VBA MACROS Subject: $$Excel-Macros$$ Need help in calculating averages based on unique values Hi Friends.... I have a simple worksheet having data as below - A B C D E F G Order No. Item No. P. Date Party Name In Date Out Date W. Days I have all columns filled as following criteria : Order No. - Repetitive...may have one or more unique item nos. Item No. - Unique nos. P. Date - Date corresponding to Item Nos. Party Name - Repetitive....Party name corresponding to Order No. In Date - In Date corresponding Item. Nos. Out Date - Out Date corresponding Item. Nos. W. Days - Difference between In and Out date -- The details are sorted on party names. I have attached a sample worksheet with filled data. Would help to understand better. What I want -- I want to work out Average of W. Days for each party based on their no. of orders - separate average for each order...and then a final average based on no. of orders. Let me tell you how - Reference file attached - > Party ABC LTD. has 41 orders > In the first order - SS0000012096 there are 05 item nos. > The average W. Days for this order is 4.8 (28/5 = 4.8) > In the same way I need to get average of each order no. separately. > When I will have averages of all 41 orders, I would sum them up and divide by 41, giving me the final average. I have to repeat this step for all parties... Now..can there be some automated way to do that...a macro or something....considering : The no. of orders may vary. No. of items against each order varies (from 1 to 100). No. and Names of parties varies every month. I am sorry for being so long....I was just trying to make you people understand. Eagerly awaiting for solution....why I said solution because I know the group's great guys would not disappoint me :-) Best regards, Abhishek Jain --~--~---------~--~----~------------~-------~--~----~ ------------------------------------------------------------------------------------- Some important links for excel users: 1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at http://www.excelitems.com 2. Excel tutorials at http://www.excel-macros.blogspot.com 3. Learn VBA Macros at http://www.vbamacros.blogspot.com 4. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com If you find any spam message in the group, please send an email to: Ayush Jain @ jainayus...@gmail.com or Ashish Jain @ 26may.1...@gmail.com ------------------------------------------------------------------------------------- -~----------~----~----~----~------~----~------~--~---
