Average of Datedifs

[Alper Takci]

Hello,

I have a table where a have dates in two columns running down about 400 dates each but they have to skip one cell so assuming I have A and B, the dates are in A1, A3, A5... and B1, B3, B5... What I would like to do is to get an average of the difference. For two rows it would be AVERAGE(DATEDIF(A1,B1,"D");DATEDIF(A3,B3,"D")) but for 400 rows, is there an easier way instead of manually entering all the data?

Thanks,

Alper

 

[Peter_SSs]

Try this array formula. It should be confirmed with Ctrl+Shift+Enter, not just Enter. If confirmed correctly, Excel will insert {} around the formula.

=AVERAGE(IF(MOD(ROW(A1:A1000)-ROW(A1)+1,2)=1,IF(A1:A1000<>0,B1:B1000-A1:A1000)))

 

[Alper Takci]

I apologize for getting back so late. Thank you for the response. The formula worked like a charm. The only issue I have is, for any given row, when there is a date in column A but no date in column B, the calculation (naturally) comes up with an negative number in the tens of thousands. I was wondering if there is a way to avoid that?

 

[Peter_SSs]

I apologize for getting back so late. Thank you for the response. The formula worked like a charm. The only issue I have is, for any given row, when there is a date in column A but no date in column B, the calculation (naturally) comes up with an negative number in the tens of thousands. I was wondering if there is a way to avoid that?

Presumably you just want to eliminate such rows from the calculation? If so, try making this small change to my previous formula.

Try this array formula. It should be confirmed with Ctrl+Shift+Enter, not just Enter. If confirmed correctly, Excel will insert {} around the formula.

=AVERAGE(IF(MOD(ROW(A1:A1000)-ROW(A1)+1,2)=1,IF(B1:B1000<>0,B1:B1000-A1:A1000)))

 

[Aladin Akyurek]

Control+shift+enter, not just enter:

=AVERAGE(IF(MOD(ROW(A1:A15)-ROW(A1),2)=0,
  IF(ISNUMBER(1/(A1:A15*B1:B15)),B1:B15-A1:A15)))