Excel VLOOKUP AVG function? Plant Metrics

**controlfreak** · 05-25-2009, 09:11 AM

Hi guys,

Hope someone could help me out with this.

Basically I'm trying to streamline the way my company does it's metrics. I have a sample excel sheet attached. This data is copied and pasted into excel every week from our documentation operating system.
The numbers in red as you scroll down are time averages for that particular product stream which have to manually inputted which can be time consuming.

Would there be a way i could paste this info into excel and for it to insert these averages for each product stream automatically? It doesn't matter where these averages are placed, they don't have to be directly under the respective product stream.

Thanks for any help offered!

**NBVC** · 05-25-2009, 09:26 AM

What determines the break points... i assume column C... but there is some inconsistencies.. eg.. in Quality Systems.. most start with QS, but one does not...

If you can clean that up.. perhaps with the help of a helper column.. you can use Pivot tables..

**controlfreak** · 05-25-2009, 09:35 AM

Thanks for the quick reply!

Cleaned up that table and yes it is column C we use as a break point. I need to find averages across the range for each of the product streams (in other words by grouping the item in CAPS together as a whole group) All the DES's together, all the ALAB's together and so on.

i'm not too familiar with pivot tables. how would they be an answer to my problem

**NBVC** · 05-25-2009, 09:58 AM

See attached...

Sheet2 contains the Pivot Table

Here are some basic tutorials on Pivot tables...

http://www.techonthenet.com/excel/pivottbls/

http://datapigtechnologies.com/ExcelMain.htm

http://peltiertech.com/Excel/Pivots/pivotstart.htm

What I did was add a helper column at the end that took everything left of the first dash (you will note that I had to hard code some because you had no dashes.. you will need to ensure this consistency in column C). You also need to give a slightly different column title.

Then

Data|Pivot Tables and Pivot Charts
Click Next
Make sure range covers your whole range.. click Next
Select where you want the Pivot Table.

Click Layout
Drag Product Grouping to the Row Area
Drag and drop each averaging item to the Data area..
You may need to double-click each item after and select Average...

Then click Finish.

To get the columns side by side... simply click and drag the grey box with "Data" in it..to the right one cell.. all the columns will now be beside each other.

You can format the columns to 2 decimals, etc...Right click >> Field Setting >> Number

To refresh the table once new data comes in.. you will need to right-click and hit Refresh on the table itself.