+ Reply to Thread
Results 1 to 4 of 4

Weighted Average of Sequential Time Based Data Series Using Start/End Time Input

  1. #1
    Registered User
    Join Date
    09-15-2017
    Location
    Montreal
    MS-Off Ver
    2016
    Posts
    2

    Weighted Average of Sequential Time Based Data Series Using Start/End Time Input

    Hi everyone! I am new to this forum. I would consider myself intermediate level excel.

    I am looking to calculate a weighted average of sequential time based data based on an input start and end time. The excel formula should use a start and end time as input, find the relative data in the sequential time sorted data array, and return the weighted average of the "rate" based on the appropriate data.

    The data contains a sumproduct row based on the "rate" and "t2-t1" (delta time). I have included a sample data sheet which has the Input, the Expected Answer, a sample Data Base, and a manual calculation of the answer.

    I originally did this using a macro, but I am looking for something simpler using functions. Pretty sure this could be done with MATCH or VLOOKUP but can't find the right fit yet.

    Any help is appreciated!

    See attached excel example!
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    05-20-2015
    Location
    Chicago, Illinois
    MS-Off Ver
    2016
    Posts
    2,103

    Re: Weighted Average of Sequential Time Based Data Series Using Start/End Time Input

    Welcome to the forum!

    Is there an error in your manually calculated example? There appears to be a "19.29" where I think there should be a "9.50". Assuming that I've got that right, try the following formula:

    =((INDEX(E4:E13,MATCH(B4,E4:E13,1)+1)-B4)*1440/INDEX(I4:I13,MATCH(B4,E4:E13,1)+1)*INDEX(K4:K13,MATCH(B4,E4:E13,1)+1)+SUMIFS(K4:K13,D4:D13,">"&B4,E4:E13,"<"&B5)+(B5-INDEX(D4:D13,MATCH(B5,D4:D13,1)))*1440/INDEX(I4:I13,MATCH(B5,D4:D13,1))*INDEX(K4:K13,MATCH(B5,D4:D13,1)))/((INDEX(E4:E13,MATCH(B4,E4:E13,1)+1)-B4)*1440+SUMIFS(I4:I13,D4:D13,">"&B4,E4:E13,"<"&B5)+(B5-INDEX(D4:D13,MATCH(B5,D4:D13,1)))*1440)

    It essentially mimics the formula you demonstrated and returns the expected value (17.45). Test it against a few other intervals to see if it holds up, but I think it should do the trick.
    Attached Files Attached Files
    If your problem has been solved, please use "Thread Tools" to mark the thread as "Solved".

    If you're grateful for this site's existence and would like to contribute to the community, please consider posting something hilarious in our joke thread:
    https://www.excelforum.com/the-water...ke-thread.html

  3. #3
    Registered User
    Join Date
    09-15-2017
    Location
    Montreal
    MS-Off Ver
    2016
    Posts
    2

    Re: Weighted Average of Sequential Time Based Data Series Using Start/End Time Input

    Thanks for the help! That was awesome!

    I started testing and noticed a bug when I would input a start time less than the first T2 value (13:13:25). I looked over your formula and it was configured to look at the time after column, therefore it would never find a value smaller the first end time, so I made a few tweeks and it seems to be working great now! The revised formula is below:

    =((INDEX(E4:E13,MATCH(B4,D4:D13,1))-B4)*1440/INDEX(I4:I13,MATCH(B4,D4:D13,1))*INDEX(K4:K13,MATCH(B4,D4:D13,1))+SUMIFS(K4:K13,D4:D13,">"&B4,E4:E13,"<"&B5)+(B5-INDEX(D4:D13,MATCH(B5,D4:D13,1)))*1440/INDEX(I4:I13,MATCH(B5,D4:D13,1))*INDEX(K4:K13,MATCH(B5,D4:D13,1)))/((INDEX(E4:E13,MATCH(B4,D4:D13,1))-B4)*1440+SUMIFS(I4:I13,D4:D13,">"&B4,E4:E13,"<"&B5)+(B5-INDEX(D4:D13,MATCH(B5,D4:D13,1)))*1440)

    See attached revised excel sheet v2.

    I will continue testing, but I will mark this as solved!

    Thanks a bunch!
    Attached Files Attached Files

  4. #4
    Forum Expert
    Join Date
    05-20-2015
    Location
    Chicago, Illinois
    MS-Off Ver
    2016
    Posts
    2,103

    Re: Weighted Average of Sequential Time Based Data Series Using Start/End Time Input

    Great, glad I could help!

+ 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. How to calculate the weighted average of duration or time
    By BlueCollarCritic in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-03-2016, 04:58 PM
  2. Time-Weighted Average
    By andyXL in forum Excel General
    Replies: 1
    Last Post: 09-04-2013, 07:54 AM
  3. [SOLVED] Creating an average from a set of data based on an initial start and end date and time
    By MKF2013 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-09-2013, 02:01 PM
  4. Replies: 3
    Last Post: 03-27-2012, 01:07 PM
  5. Calculating END time based on start time, breaks, and hours to complete
    By sweedey in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 02-01-2012, 11:29 AM
  6. Weighted Average of Time
    By ExcelAteMyHomework in forum Excel General
    Replies: 5
    Last Post: 02-11-2011, 08:41 PM
  7. custom date and time weighted average!!
    By jfzaki in forum Excel General
    Replies: 3
    Last Post: 09-24-2009, 04:01 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