[mckzach]

Greetings all,

I am working with two columns (ranges) of dates, although there are many
blank rows interspersed in both ranges. I simply need to calculate the
overall average days elapsed between the two columns. In other words, I'm
looking for a single result here; Col. B (End date) - Col. A (Start date) for
as many rows of dates that exist.

COL A (Start) COL B (End)
01/05/09 02/20/09
03/12/09 03/09/09

01/15/09 01/30/09
02/19/09 02/22/09

The array formula I'm using (below) works, but is factoring in the blank
cells and is giving me a lower inaccurate result.

{=AVERAGE((B$2:B$100)-(A$2:A$100))}

Any help at all would be appreciated.

[Jarek Kujawa[_2_]]

presuming blanks are always in neighbouring A and B

=AVERAGE(IF(ISBLANK(B1:B4),"",B1:B4-A1:A4))

CTRL+SHIFT+ENTER it as it is an array formula



On 22 Maj, 14:56, mckzach wrote:
Greetings all,

I am working with two columns (ranges) of dates, although there are many
blank rows interspersed in both ranges. *I simply need to calculate the
overall average days elapsed between the two columns. *In other words, I'm
looking for a single result here; Col. B (End date) - Col. A (Start date) for
as many rows of dates that exist.

COL A (Start) *COL B (End)
01/05/09 * * * *02/20/09
03/12/09 * * * *03/09/09

01/15/09 * * * *01/30/09
02/19/09 * * * *02/22/09

The array formula I'm using (below) works, but is factoring in the blank
cells and is giving me a lower inaccurate result.

*{=AVERAGE((B$2:B$100)-(A$2:A$100))}

Any help at all would be appreciated.

[Sean Timmons]

=SUMPRODUCT(B2:B100-A2:A100)/SUMPRODUCT(--(B2:B100<""),--(A2:A100<""))

should get it. If it still account for the blank cells, they are not blank.
If the cell is, perhaps, " ", then you can either delete these or change the
sumproduct to < " "

"mckzach" wrote:

Greetings all,

I am working with two columns (ranges) of dates, although there are many
blank rows interspersed in both ranges. I simply need to calculate the
overall average days elapsed between the two columns. In other words, I'm
looking for a single result here; Col. B (End date) - Col. A (Start date) for
as many rows of dates that exist.

COL A (Start) COL B (End)
01/05/09 02/20/09
03/12/09 03/09/09

01/15/09 01/30/09
02/19/09 02/22/09

The array formula I'm using (below) works, but is factoring in the blank
cells and is giving me a lower inaccurate result.

{=AVERAGE((B$2:B$100)-(A$2:A$100))}

Any help at all would be appreciated.

[Domenic[_2_]]

Here's another way...

=AVERAGE(IF(B2:B6<"",B2:B6-A2:A6))

....confirmed with CONTROL+SHIFT+ENTER. Note that this formula allows
formula blanks ("").

--
Domenic
http://www.xl-central.com

In article ,
mckzach wrote:

Greetings all,

I am working with two columns (ranges) of dates, although there are many
blank rows interspersed in both ranges. I simply need to calculate the
overall average days elapsed between the two columns. In other words, I'm
looking for a single result here; Col. B (End date) - Col. A (Start date) for
as many rows of dates that exist.

COL A (Start) COL B (End)
01/05/09 02/20/09
03/12/09 03/09/09

01/15/09 01/30/09
02/19/09 02/22/09

The array formula I'm using (below) works, but is factoring in the blank
cells and is giving me a lower inaccurate result.

{=AVERAGE((B$2:B$100)-(A$2:A$100))}

Any help at all would be appreciated.