+ Reply to Thread
Results 1 to 6 of 6

Average values if the associated time of occurence falls within a certain time range

  1. #1
    Registered User
    Join Date
    05-02-2014
    Location
    Wisconsin
    MS-Off Ver
    Excel 2007
    Posts
    4

    Average values if the associated time of occurence falls within a certain time range

    Please refer to the attachment. I am trying to average the data in the Y column, if the times fall into the range between column R and S. I am having trouble with the averageif function. Is there a better way to parse through column W, check if the values fall between the ranges of S and R, and if they do, average the associated values in column Y?

    Thanks!
    Attached Images Attached Images

  2. #2
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    2012 for Mac at home
    Posts
    7,236

    Re: Average values if the associated time of occurence falls within a certain time range

    it isn't ideal to work from jpgs, but I tried to enter some points to see what you are attempting. I guess I don't understand what it is you are saying should be greater than R2 and less than S2.
    However, have you tried to use an If(and(whatever it is you want to compare>R2,same<S2),Average(W:W,Y:Y)?
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  3. #3
    Registered User
    Join Date
    05-02-2014
    Location
    Wisconsin
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Average values if the associated time of occurence falls within a certain time range

    If the times in column W are in the range of R2 and S2, I would like to average the values in column Y that are associated with the times in W. Then the averaged value should be returned in T2.

  4. #4
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    2012 for Mac at home
    Posts
    7,236

    Re: Average values if the associated time of occurence falls within a certain time range

    Well, i'm done, got me. But maybe for the next person and to help you define it a little more, you essentially want only the times in column W that are greater than R2 and less than S2 to be averaged and any times that are outside of those parameters to be excluded from the average, right?

  5. #5
    Registered User
    Join Date
    05-02-2014
    Location
    Wisconsin
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Average values if the associated time of occurence falls within a certain time range

    You're right on. But I don't want to average the times that fall within the range, I would like to average the values in column Y that are associated with the times in column W. Thanks for the help.

  6. #6
    Registered User
    Join Date
    05-02-2014
    Location
    Wisconsin
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Average values if the associated time of occurence falls within a certain time range

    =AVERAGEIF(INDEX(B3:B27,MATCH($H$3,B3:B27,0)):INDEX(B3:B27,MATCH($I$3,B3:B27,0)),">0",D3:D27)

    my latest attempt, to no avail

+ 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. [SOLVED] Find row where time value falls between start and end time values
    By wraithlet in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-12-2013, 12:35 PM
  2. If time falls in a certain range
    By slegendre in forum Excel General
    Replies: 4
    Last Post: 01-19-2013, 09:26 AM
  3. Replies: 3
    Last Post: 05-25-2012, 03:41 PM
  4. Verifying if a time falls within a range
    By kckid816 in forum Excel General
    Replies: 3
    Last Post: 02-27-2012, 11:07 PM
  5. Replies: 1
    Last Post: 03-19-2007, 07:53 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