Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default How do I count the number of events in a 90 day period?

I have a list of 6000 events and the date each occurred.
For each date, I need to count the number of events since 90 days prior.
How can I count backwards until I reach the first occurrence of a date that
is more than 90 days earlier (and then stop)?

Exit Date 90 Days Ago # Exits in Between
1-Jan-1978 3-Oct-1977
12-Apr-78 12-Jan-1978 0
12-May-78 11-Feb-1978 1
1-Jul-78 2-Apr-1978 2
1-Jul-78 2-Apr-1978 2
20-Jul-78 21-Apr-1978 3
2-Aug-78 4-May-1978 4
What formula can I use to calculate the last column?
Many thanks.


  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default How do I count the number of events in a 90 day period?

I guess by your example the result is *exclusive* of the dates?

Try this:

=SUMPRODUCT(--(A$2:A$8B2),--(A$2:A$8<A2))

Copy down as needed.

The results based on your sample a 0,0,1,2,2,3,4

Biff

"ericball" wrote in message
...
I have a list of 6000 events and the date each occurred.
For each date, I need to count the number of events since 90 days prior.
How can I count backwards until I reach the first occurrence of a date
that
is more than 90 days earlier (and then stop)?

Exit Date 90 Days Ago # Exits in Between
1-Jan-1978 3-Oct-1977
12-Apr-78 12-Jan-1978 0
12-May-78 11-Feb-1978 1
1-Jul-78 2-Apr-1978 2
1-Jul-78 2-Apr-1978 2
20-Jul-78 21-Apr-1978 3
2-Aug-78 4-May-1978 4
What formula can I use to calculate the last column?
Many thanks.




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
how do I count the number of times text in column A matches text i Sheila Excel Worksheet Functions 2 November 16th 05 11:20 PM
Number count - postcodes and need to count the numbers of tim... Mark - Aust Excel Discussion (Misc queries) 1 October 24th 05 10:00 AM
How to count the number of times something occurs within a certain month Joyce Excel Worksheet Functions 2 October 18th 05 08:38 PM
Count Number of Characters in a cell? AHJuncti Excel Discussion (Misc queries) 2 June 16th 05 07:39 PM
How do i count the number of conditional formatted cells? kate Excel Discussion (Misc queries) 2 May 16th 05 10:47 PM


All times are GMT +1. The time now is 08:50 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"