+ Reply to Thread
Results 1 to 16 of 16

Average of multiple vlookup values from another sheet

  1. #1
    Registered User
    Join Date
    01-17-2014
    Location
    Manila, Philippines
    MS-Off Ver
    Excel 2010
    Posts
    9

    Post Average of multiple vlookup values from another sheet

    I am trying to get the average TAT of reports. There are three sheets in this workbook: January, Admin and Summary. January is what the historians manually fill up, Admin is for evaluation purposes (I've used Vlookup to fill in the data) and the Summary is where I wish to place the formula I am having issues with.

    This is my formula =AVERAGEIFS(Admin!M:M,Admin!N:N,"AHMG",Admin!O:O,">= 01/01/2014",Admin!O:O,"<=01/31/2014")

    However, since the values in Admin sheet are all vlookup values and not actual data, I can't make it work. Any idea on how to make this work?

    Thank you so much!
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor Naveed Raza's Avatar
    Join Date
    11-04-2012
    Location
    India, Hyderabad
    MS-Off Ver
    Excel, Access 2007/2010
    Posts
    1,338

    Re: Average of multiple vlookup values from another sheet

    in G4 cell in SUmmary Tab insert this function

    =AVERAGEIFS(Admin!$M:$M,Admin!$D:$D,Summary!B4,Admin!$O:$O,">="&"01/01/2014",Admin!$O:$O,"<="&"31/01/2014")
    Thanks - Naveed
    -----------------------------
    If the suggestion helps you, then Click * to Add Reputation
    To Attach File: Go Advanced>>Manage Attachments>>Add Files (In Top Right Corner)>>SelectFiles>>.........Locate Your File(s)>>Upload Files>>Done (In Bottom Right)
    1. Use [code] code tags [\code]. It keeps posts clean, easy-to-read, and maintains VBA formatting.
    2. If you are happy with a solution to your problem, mark the thread as [SOLVED] using the tools at the top.

  3. #3
    Registered User
    Join Date
    01-17-2014
    Location
    Manila, Philippines
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: Average of multiple vlookup values from another sheet

    Hi Naveed,

    Thanks for your response. I tried your suggestion but it still returns #DIV/0!

  4. #4
    Valued Forum Contributor Naveed Raza's Avatar
    Join Date
    11-04-2012
    Location
    India, Hyderabad
    MS-Off Ver
    Excel, Access 2007/2010
    Posts
    1,338

    Re: Average of multiple vlookup values from another sheet

    its working fine here on my machine wait i will provide you the sample workbook

  5. #5
    Valued Forum Contributor Naveed Raza's Avatar
    Join Date
    11-04-2012
    Location
    India, Hyderabad
    MS-Off Ver
    Excel, Access 2007/2010
    Posts
    1,338

    Re: Average of multiple vlookup values from another sheet

    now check it out and let me know

  6. #6
    Registered User
    Join Date
    01-17-2014
    Location
    Manila, Philippines
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: Average of multiple vlookup values from another sheet

    Oh! okay..let me try this again then..

  7. #7
    Registered User
    Join Date
    01-17-2014
    Location
    Manila, Philippines
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: Average of multiple vlookup values from another sheet

    That's odd..I downloaded the file you uploaded and it also shows #DIV/0! on G4 in Summary tab. Does it have something to do with the different versions we are using? But then, MS 2007 and 2010 are almost the same.

  8. #8
    Valued Forum Contributor Naveed Raza's Avatar
    Join Date
    11-04-2012
    Location
    India, Hyderabad
    MS-Off Ver
    Excel, Access 2007/2010
    Posts
    1,338

    Re: Average of multiple vlookup values from another sheet

    ohh!, it perfectly working here , but still try this version and let me know still if its not work then we will see

    hope this work

  9. #9
    Registered User
    Join Date
    01-17-2014
    Location
    Manila, Philippines
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: Average of multiple vlookup values from another sheet

    I tried the xlsm version and it did show the average TAT however when I clicked enable editing, the cells went to #DIV/0! again. Now I wonder if the problem is on my end.

  10. #10
    Valued Forum Contributor Naveed Raza's Avatar
    Join Date
    11-04-2012
    Location
    India, Hyderabad
    MS-Off Ver
    Excel, Access 2007/2010
    Posts
    1,338

    Re: Average of multiple vlookup values from another sheet

    might be, anyway can you tell me what is average value of first one

  11. #11
    Registered User
    Join Date
    01-17-2014
    Location
    Manila, Philippines
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: Average of multiple vlookup values from another sheet

    Yep..before I clicked enable editing, value showed .77

  12. #12
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Average of multiple vlookup values from another sheet

    Try this for the Average TAT. Enter this on the Summary worksheet in G4 and copy down.

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Change the date for the February table.
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

  13. #13
    Valued Forum Contributor Naveed Raza's Avatar
    Join Date
    11-04-2012
    Location
    India, Hyderabad
    MS-Off Ver
    Excel, Access 2007/2010
    Posts
    1,338

    Re: Average of multiple vlookup values from another sheet

    exactly that value only showing to me

  14. #14
    Registered User
    Join Date
    01-17-2014
    Location
    Manila, Philippines
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: Average of multiple vlookup values from another sheet

    Yes! It worked! Thanks so much newdoverman and naveed! This is my second night working on this. Good thing I had the sense to search the forums.

  15. #15
    Valued Forum Contributor Naveed Raza's Avatar
    Join Date
    11-04-2012
    Location
    India, Hyderabad
    MS-Off Ver
    Excel, Access 2007/2010
    Posts
    1,338

    Re: Average of multiple vlookup values from another sheet

    Glad we could help you

    -If you like someone's answer, click the star to give them a reputation point for that answer & mark thread as solved

  16. #16
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Average of multiple vlookup values from another sheet

    Glad to have been of service.

    Good luck with the rest of your project. Thanks for the feedback.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. want to average a bunch of values found with vlookup
    By EBos in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 07-14-2013, 10:06 PM
  2. VLOOKUP multiple cells and Average
    By lewgill in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 05-17-2013, 08:25 AM
  3. How to get the average of multiple VLOOKUP findings
    By jur78 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-27-2013, 01:17 AM
  4. Average VLookup over multiple sheets
    By pjsilber in forum Excel General
    Replies: 3
    Last Post: 10-05-2011, 07:15 PM
  5. Average for range of values (VLOOKUP?)
    By Poolio in forum Excel General
    Replies: 1
    Last Post: 01-09-2007, 08:20 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1