Forum Discussion
Need help with formula for average time between two diferent sets of dates.
I am trying to calculate average time between two different date columns. I have been typing the following formula out for over 20 different rows: =((b1-a1)*24+(b2-a2)*24+(b3-a3)*24...). How can I simplify this so I don't have to type out every cell?
Hi David
=SUMPRODUCT((B1:B100-A1:A100)*1)*24
Sergei--
I hope this goes to you. I'm trying to average some time intervals calculated from subtracting an earlier time from a later time. They are MM:SS, like for instance 11:32.
Thanks,
John
Hi John,
That is separate topic, better if you start new conversation with it. In general AVERAGE() shall work.
Thank you so much Sergei! In this case I had 21 rows and the right formula I needed turned out to be =SUMPRODUCT((H3:H23-E3:E23)*24)/21
What should I do if I need to skip over a few rows within the range? For instance, now I need to skip rows 4, 5, 6, 11, 13, 14, 15, 16, 18, 19, 21, and 23
Then, my next calculation after that needs to skip rows 3, 7, 8, 9, 10, 12, 17, 20, and 22.
David,
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:
