Average number of days, with missing data

[petersw]

Hi,

I've got two columns of dates, and want to find the average number of days between them. Column A is the start date, and Column B is the end date. However, some start dates are missing and marked as "UNK":

Column A | Column B
1/1/2010 | 3/4/2010
2/1/2010 | 5/19/2010
UNK | 4/1/2010


If no dates were missing, I'd use =SUM(INDEX(B1:B3-A1:A3,0,1))/COUNT(A1:A3). However, I'm not sure how to skip lines that have "UNK" in Column A. As I found out, SUMIF doesn't seem to work with an Index statement.

This seems like it should be fairly easy...but not for me! Thanks in advance...

 

[T. Valko]

Hi,

I've got two columns of dates, and want to find the average number of days between them. Column A is the start date, and Column B is the end date. However, some start dates are missing and marked as "UNK":

Column A | Column B
1/1/2010 | 3/4/2010
2/1/2010 | 5/19/2010
UNK | 4/1/2010


If no dates were missing, I'd use =SUM(INDEX(B1:B3-A1:A3,0,1))/COUNT(A1:A3). However, I'm not sure how to skip lines that have "UNK" in Column A. As I found out, SUMIF doesn't seem to work with an Index statement.

This seems like it should be fairly easy...but not for me! Thanks in advance...

Try this array formula**:

=AVERAGE(IF(ISNUMBER(B2:B4-A2:A4),B2:B4-A2:A4))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

 

[barry houdini]

You could use an array formula like this

=AVERAGE(IF(ISNUMBER(A1:A3),B1:B3-A1:A3)))

or a non-array approach

=(SUMIF(A1:A3,"<>UNK",B1:B3)-SUMIF(A1:A3,"<>UNK"))/COUNT(A1:A3)

 

[petersw]

See, I knew it would be easy for someone!

Thanks to you both!

 

[T. Valko]

See, I knew it would be easy for someone!

Thanks to you both!

You're welcome. Thanks for the feedback!