+ Reply to Thread
Results 1 to 14 of 14

average over vlookup range?

  1. 11-12-2012, 07:20 AM #1
    erk_raven
    erk_raven is offline
    Registered User
    Join Date
    11-12-2012
    Location
    hamburg
    MS-Off Ver
    Excel 2003
    Posts
    4

    average over vlookup range?

    tanya.PNG

    Hi all,

    I always use this forum to find out for a solution, but I can't help my self this time.
    I want to create an average speed within a certain range of date. I never use a range of vlookup and I am very new in using that function, could you guys help me?
    The result that I wanted is the average speed of a certain date which I input between column D and E. The master data itself is located on the column A and B. I was using averaging manually, but it tooks time when the data is thousands or millions.
    I hope you can help me.
    Thanks in advance!

    -Erick
    Reply With QuoteRegister To Reply
  2. 11-12-2012, 08:57 AM #2
    jake.masters
    jake.masters is offline
    Forum Contributor
    Join Date
    09-13-2012
    Location
    Atlanta, USA
    MS-Off Ver
    Office 2003, 2010
    Posts
    230

    Re: average over vlookup range?

    Are you using excel 2003? The picture looks more like 2007/2010.
    Reply With QuoteRegister To Reply
  3. 11-12-2012, 09:01 AM #3
    erk_raven
    erk_raven is offline
    Registered User
    Join Date
    11-12-2012
    Location
    hamburg
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: average over vlookup range?

    Sorry I forgot to mention it previously, I am using excel 2010.
    Reply With QuoteRegister To Reply
  4. 11-12-2012, 09:10 AM #4
    kbkumar
    kbkumar is offline
    Forum Contributor
    Join Date
    09-27-2012
    Location
    London, England
    MS-Off Ver
    2003, 2010
    Posts
    344

    Re: average over vlookup range?

    Hi -Use the below formula in cell F4

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


    I have used data range till 172 rows but change it as per your requirements.

    Hope this works.
    Last edited by kbkumar; 11-12-2012 at 09:15 AM.
    If solved kindly remember to mark Thread as solved.
    Click the small star icon at the bottom left of my post if this was useful.
    Reply With QuoteRegister To Reply
  5. 11-12-2012, 09:12 AM #5
    Fotis1991
    Fotis1991 is offline
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: average over vlookup range?

    Try this Array(control+shift+enter-not just enter) formula

    =AVERAGE(IF(A5:A1000>=E4,IF(A5:A1000<=F4,B5:B1000)))
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.
    Reply With QuoteRegister To Reply
  6. 11-12-2012, 09:12 AM #6
    jake.masters
    jake.masters is offline
    Forum Contributor
    Join Date
    09-13-2012
    Location
    Atlanta, USA
    MS-Off Ver
    Office 2003, 2010
    Posts
    230

    Re: average over vlookup range?

    Do you want it to average the 0s also?
    Reply With QuoteRegister To Reply
  7. 11-12-2012, 09:21 AM #7
    erk_raven
    erk_raven is offline
    Registered User
    Join Date
    11-12-2012
    Location
    hamburg
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: average over vlookup range?

    WOWWW..that is amazing!!
    it works, thanks so much for your help guys

    btw do you mind explaining to me about the bold part below?
    =AVERAGEIFS($B$5:$B$6172,A7:A6174,">="&F7,A7:A6174,"<="&G7)
    Reply With QuoteRegister To Reply
  8. 11-12-2012, 09:22 AM #8
    Jacc
    Jacc is offline
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,844

    Re: average over vlookup range?

    Does this work for you?
    Formula: copy to clipboard 
    Please Login or Register  to view this content.



    Edit: He he... Too late!
    <----- If you were helped by my posts you can say "Thank you" by clicking the star symbol down to the left

    If the problem is solved, finish of the thread by clicking SOLVED under Thread Tools
    I don't wish to leave you with no answer, yet I sometimes miss posts. If you feel I forgot you, remind me with a PM or just bump the thread.
    Reply With QuoteRegister To Reply
  9. 11-12-2012, 09:22 AM #9
    erk_raven
    erk_raven is offline
    Registered User
    Join Date
    11-12-2012
    Location
    hamburg
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: average over vlookup range?

    Quote Originally Posted by jake.masters View Post
    Do you want it to average the 0s also?
    yeah...
    I want to average everything including the 0
    Reply With QuoteRegister To Reply
  10. 11-12-2012, 09:30 AM #10
    kbkumar
    kbkumar is offline
    Forum Contributor
    Join Date
    09-27-2012
    Location
    London, England
    MS-Off Ver
    2003, 2010
    Posts
    344

    Re: average over vlookup range?

    Averageifs is a new feature of Excel 2010. It allows you to compute averages in a given rane across multiple conditions / criterias.

    In your case:

    ">="&F7: This looks for your first criteria in column A i.e. Dates in column A which are greater than or equal to the date mentioned in cell F7

    and,

    "<="&G7: This looks for your second criteria in column A i.e. Dates in column A which are less than or equal to the date mentioned in cell G7

    You may add more such layers of criterias.

    Hope this helps.
    Reply With QuoteRegister To Reply
  11. 11-12-2012, 09:35 AM #11
    Fotis1991
    Fotis1991 is offline
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: average over vlookup range?

    @ kbkumar

    AVERAGEIFS, exist also in Excel2007

    @ erk_raven

    Please change the version that you use in your profile. I haven't see your post#3, that why i suggested an Array formula.

    And thank you for the reb*.
    Reply With QuoteRegister To Reply
  12. 11-12-2012, 09:38 AM #12
    kbkumar
    kbkumar is offline
    Forum Contributor
    Join Date
    09-27-2012
    Location
    London, England
    MS-Off Ver
    2003, 2010
    Posts
    344

    Re: average over vlookup range?

    Oh yes....thanks for correcting Fortis

    I started using 2010 after 2003 so never had any hands on exp in playing with 2007 except for a couple of days, so could not recall real time.

    Thanks again!!!
    Reply With QuoteRegister To Reply
  13. 11-12-2012, 09:40 AM #13
    Fotis1991
    Fotis1991 is offline
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: average over vlookup range?

    Fotis. Not Fortis!!!
    Reply With QuoteRegister To Reply
  14. 11-12-2012, 10:02 AM #14
    kbkumar
    kbkumar is offline
    Forum Contributor
    Join Date
    09-27-2012
    Location
    London, England
    MS-Off Ver
    2003, 2010
    Posts
    344

    Re: average over vlookup range?

    oops...fat fingers
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1