Forum Discussion
Need help with formula for average time between two diferent sets of dates.
Hi David
=SUMPRODUCT((B1:B100-A1:A100)*1)*24
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,
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?
I am calculating average time to load barges. My first calculation was average time to load all barges at a particular terminal in one month. Now I need to make separate calculations for all the 10K barrel barges and for all the 20K barrel barges. So the rows I am selecting are determined by the value I have in Column A (barge capacity).
When instead of my criteria in formula on screenshot ($J$3:$J$23<>1) you may use ($A$3:$A$23="10K") and similar in COUNTIF
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:
