Prev: Missing values from a pivot table field
Next: Worksheet relative reference
From: towem3 on 27 Apr 2010 11:15
I have a large database of ~10,000 dated and timed entries. Each entry
represents the date and time of a hospital admission. I would like to know
for each entry, how many other events took place within 24 hours. I am trying
to figure out how many hospital admission happened in the 12 hours prior to
and after each admission to estimate the daily admission rate more accurately
than just by calendar day.

I have tried
=COUNTIFS(AV:AV,"<="&(AV2+0.5),AV:AV,">="&(AV2-0.5))
Where AV is the column of all dates, and AV2 is the date in question. This
formula is not accurate, and I can't figure out why

I'm looking to make a data sheet such as below (I've figured out the
calendar day column), but I can't figure out how to do the 24h count.

Id# Date Calendar day count 24h count
1 1/1/10 12:00 2 2
2 1/1/10 23:00 2 4
3 1/2/10 01:30 3 3
4 1/2/10 11:00 3 3
5 1/2/10 14:00 3 2

Thank you for your help!
From: Bob Phillips on 27 Apr 2010 12:23
Try

=SUMPRODUCT(--(AV:AV<AV2+0.5),--(AV:AV>=AV2-0.5))

--

HTH

Bob

"towem3" <towem3(a)discussions.microsoft.com> wrote in message
news:B44FE3D2-9488-41AB-BE36-2D711EFDE972(a)microsoft.com...
>I have a large database of ~10,000 dated and timed entries. Each entry
> represents the date and time of a hospital admission. I would like to know
> for each entry, how many other events took place within 24 hours. I am
> trying
> to figure out how many hospital admission happened in the 12 hours prior
> to
> and after each admission to estimate the daily admission rate more
> accurately
> than just by calendar day.
>
> I have tried
> =COUNTIFS(AV:AV,"<="&(AV2+0.5),AV:AV,">="&(AV2-0.5))
> Where AV is the column of all dates, and AV2 is the date in question. This
> formula is not accurate, and I can't figure out why
>
> I'm looking to make a data sheet such as below (I've figured out the
> calendar day column), but I can't figure out how to do the 24h count.
>
> Id# Date Calendar day count 24h count
> 1 1/1/10 12:00 2 2
> 2 1/1/10 23:00 2 4
> 3 1/2/10 01:30 3 3
> 4 1/2/10 11:00 3 3
> 5 1/2/10 14:00 3 2
>
> Thank you for your help!


 | 
Pages: 1
Prev: Missing values from a pivot table field
Next: Worksheet relative reference