1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Vlookup and average

Discussion in 'Windows & Other Software' started by Colbaker, 21 Oct 2016.

  1. Colbaker

    Wise Guy

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

    Gangster

    Joined: 27 Jan 2007

    Posts: 245

    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),.......)
     
  3. adolf hamster

    Sgarrista

    Joined: 18 Oct 2012

    Posts: 8,101

    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
     
  4. BigT

    Mobster

    Joined: 18 Oct 2002

    Posts: 3,312

    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))