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

1. ## 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!  Register To Reply

2. ## 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.  Register To Reply

3. ## 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!  Register To Reply

4. ## Re: Weighted Average of Sequential Time Based Data Series Using Start/End Time Input  Register To Reply