Averaging Time Difference

Can I average a column of times (that were determined as a difference from two other columns)? The times are being formatted with a MOD function due to the times could be on two different days.

Now to make it more complicated, I would like to only averge the time difference that corresponds to another column that is represented by a constant.

Ex:
Col A Col B Col C Col D
2 12:14 12:15 0:01
1 23:56 00:02 0.06

I am trying to determine the time difference between Col B and Col C with the result in Col D. Again I am using a MOD due to date change. I then want to only pick out all "1" or "2" in Col A and average the time difference in Col D that corresponds to that.

Do I need to format the cells differently than times?

Please help!

Try:

=SUMPRODUCT(--((A1:A50=1)+(A1:A50=2))*(D1:D50))/SUMPRODUCT(--((A1:A50=1)+(A1:A50=2)))

If you require answer in minutes/seconds format as mm:ss

HTH

"vldavis809" wrote:

>
> Can I average a column of times (that were determined as a difference
> from two other columns)? The times are being formatted with a MOD
> function due to the times could be on two different days.
>
> Now to make it more complicated, I would like to only averge the time
> difference that corresponds to another column that is represented by a
> constant.
>
> Ex:
> Col A Col B Col C Col D
> 2 12:14 12:15 0:01
> 1 23:56 00:02 0.06
>
> I am trying to determine the time difference between Col B and Col C
> with the result in Col D. Again I am using a MOD due to date change. I
> then want to only pick out all "1" or "2" in Col A and average the time
> difference in Col D that corresponds to that.
>
> Do I need to format the cells differently than times?
>
> Please help!
>
>
> --
> vldavis809
> ------------------------------------------------------------------------
> vldavis809's Profile: http://www.excelforum.com/member.php...o&userid=36146
> View this thread: http://www.excelforum.com/showthread...hreadid=559536
>
>