Need help with formula for average time between two diferent sets of dates.

Question

I am trying to calculate average time between two different date columns.&nbsp; I have been typing the following formula out for over 20 different rows: =((b1-a1)*24+(b2-a2)*24+(b3-a3)*24...).&nbsp; How can I simplify this so I don't have to type out every cell?

sergeibaklan · Answer

Hi David&nbsp;=SUMPRODUCT((B1:B100-A1:A100)*1)*24

david ramseur · Answer

Thank you so much Sergei!&nbsp; In this case I had 21 rows and the right formula I needed turned out to be =SUMPRODUCT((H3:H23-E3:E23)*24)/21&nbsp;What should I do if I need to skip over a few rows within the range?&nbsp; For instance, now I need to skip rows 4, 5, 6, 11, 13, 14, 15, 16, 18, 19, 21, and 23&nbsp;Then, my next calculation after that needs to skip rows 3, 7, 8, 9, 10, 12, 17, 20, and 22.

sergeibaklan · Answer

David,&nbsp;Depends on how do you define which rows to skip. Are that rows empty (or have some other value based on which to skip) or you define rows to skip by their numbers?&nbsp;&nbsp;

sergeibaklan · Answer

David,&nbsp;The idea is - if you have any criteria based on which skip the rows you may add formula for this criteria as one more multiplier in SUMPRODUCT and calculate number of rows with COUNTIF using same criteria. For example, here in column J we have 1 for rows which to skip:&nbsp;

david ramseur · Answer

I am calculating average time to load barges.&nbsp; My first calculation was average time to load all barges at a particular terminal in one month.&nbsp; Now I need to make separate calculations for all the 10K barrel barges and for all the 20K barrel barges.&nbsp; So the rows I am selecting are determined by the value I have in Column A (barge capacity).

Forum Discussion

Need help with formula for average time between two diferent sets of dates.

Share

Resources