Averaging the Difference of Two Columns

**mgaworecki** · 09-16-2008, 08:25 AM

Hello,

I would have been looking and cannot find a solution. But I am sure one of you can do it.

I have 2 columns (B and C) with times listed from Row 2 and will continue down several hundred rows. In a single cell, I would like to put a formula that will average the difference in the times in each row (the value will be in seconds... maybe minutes and seconds).

I tried to use this, but it didn't seem to work (Entered as an array):

Please Login or Register  to view this content.

Any help would be appreciated.

Thanks,

Mark

**NBVC** · 09-16-2008, 08:32 AM

You need to probably eliminate all the 0 entries....

try:

=AVERAGE(IF(C$2:C$65536+B$2:B$65536<>0,(C$2:C$65536-B$2:B$65536)*24*60*60))

**mgaworecki** · 09-16-2008, 08:35 AM

Thank you for yoru quick reply,it worked great. I didn't really worry about that part because I don't have actual zeros and I thought that AVERAGE ignored text and blanks.

Thanks again

Mark

**NBVC** · 09-16-2008, 08:40 AM

Because you are subtracting a blank from a blank, this results in the array containing 0's and therefore get factored into the Averaging...

If you were straight averaging a range containing blanks, then the Average would ignore the blanks...

**mgaworecki** · 09-16-2008, 08:55 AM

That makes sense since you put it that way. Thanks

**mikerickson** · 09-16-2008, 09:12 AM

If every row has either dates in both columns or (blanks or text) in both columns,
this non-CSE formula will work

=(AVERAGE(A2:A65536)-AVERAGE(B2:B65536)) *60*60*24