If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Average days between dates
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. |
#2
|
|||
|
|||
Average days between dates
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. |
#3
|
|||
|
|||
Average days between dates
=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. |
#4
|
|||
|
|||
Average days between dates
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. |
Thread Tools | |
Display Modes | |
|
|