Vlookup and average

Associate
Joined
8 Dec 2004
Posts
1,970
Location
Paignton, Devon
Hi all.

I have 3 sheets. In Column a on each sheet is a name, in column B on each sheet is there time each month that they have been working which varies.

On sheet 4 I want to match the name then average the times they have been working over the 3 other sheets.

What formula should I use?
 
Associate
Joined
27 Jan 2007
Posts
247
Location
Bucks
I assume that
(A) each name only appears once on each sheet
(B) each name appears on every sheet.
(C) the time is in a suitable numeric/time format
If those assumptions are not right then these solutions will need tweaking.

Option1 Restructure your data and use a pivot table:
This will be the quickest and require least maintenance.
Merge the info in the three sheets by:
Adding a new column containing the sheetname/nonthname or whatever else distinguishes the three sheets. Then copy the data from the sheets one below the other so that you have a single table with all the data.
Then use a pivot table to create an average for each name. (Row field = name, datafield =time ....

Option2 Live with the data in the format you have it.
With the names in column A of sheet4, with titles in row 1
This formula in BB2 will give the average time:
=AVERAGE(VLOOKUP(A2,Sheet1!$A$1:$C$14,0),VLOOKUP(A2,Sheet2!$A$1:$C$14,0),.......)
 
Soldato
Joined
18 Oct 2012
Posts
8,332
Do the sheets have a standard naming convention eg: month1, month2....?

If so and its numeric you can use an indirect and column number to pull the data into one sheet and average it.

I used a similar system for recording machine logs, i just named a standard sheet template by the batch number and it was setup to pull the column averages through for each batch. Its a pita to set up but once working you can just add sheets by the naming convention and itll automatically filter through.

The pivot table would be better as a quick dynamic display, or if you can restructure your spreadsheet to keep all the data in one table then its just a case of adding columns
 
Soldato
Joined
18 Oct 2002
Posts
3,506
Location
UK
As long as each name is unique then on sheet 4 if you have:

Column A - Name
Column B - Average

In B2 you can put:

=AVERAGE(VLOOKUP(A1,Sheet1!A:B,2,FALSE),VLOOKUP(A1,Sheet2!A:B,2,FALSE),VLOOKUP(A1,Sheet3!A:B,2,FALSE))
 
Back
Top Bottom