Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I count the number of events in a 90 day period?
try this idea for -90. Substitute today() for the date in a list
=SUMPRODUCT(($A$3:$A$23TODAY()-90)*($A$3:$A$23<TODAY())) -- Don Guillett SalesAid Software "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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do I count the number of times text in column A matches text i | Excel Worksheet Functions | |||
Number count - postcodes and need to count the numbers of tim... | Excel Discussion (Misc queries) | |||
How to count the number of times something occurs within a certain month | Excel Worksheet Functions | |||
Count Number of Characters in a cell? | Excel Discussion (Misc queries) | |||
How do i count the number of conditional formatted cells? | Excel Discussion (Misc queries) |