How to determine the frequency?
On Sun, 23 Nov 2008 05:12:05 -0800, Eric
wrote:
[Re-word my post]
Does anyone have any suggestions on how to determine the frequency?
There is a list of date under column A, and a given number 3 in cell B1
I would like to determine the frequency with +/- 3 days acceptable range
04-Jan-09, 09-Jan-09, 11-Jan-09, 18-Jan-09, 20-Jan-09, 22-Jan-09,
23-Jan-09, 24-Jan-09, 24-Jan-09, 24-Jan-09, 24-Jan-09, 26-Jan-09,
27-Jan-09, 05-Feb-09, 06-Feb-09, 12-Feb-09, 17-Feb-09 ...
On 4-Jan-08, it should return 1 in cell C1 for range between 1-Jan-09 and
7-Jan-09, which include 04-Jan-09 only.
On 09-Jan-08, it should return 2 in cell C3 for range between 5-Jan-09 and
12-Jan-09, which include 09-Jan-09 and 11-Jan-09.
On 11-Jan-08, it should return 2 in cell C3 for range between 8-Jan-09 and
14-Jan-09, which include 09-Jan-09 and 11-Jan-09.
...
On 24-Jan-08, it should return 8 in cell C8 for range between 21-Jan-09 and
27-Jan-09, which include 22-Jan-09, 23-Jan-09, 24-Jan-09, 24-Jan-09,
24-Jan-09, 24-Jan-09, 26-Jan-09, 27-Jan-09
Does anyone have any suggestions on how to determine the occurrence?
Thanks in advance for any suggestions
Eric
In your second example i think that C3 should be C2 and that 5-Jan
should be 6-Jan.
Try the following formula in cell C1:
=SUMPRODUCT((A$1:A$100=A1-B$1)*(A$1:A$100<=A1+B$1))
Change the 100 to be at least the number of dates that you have in
column A.
Copy the formula down as many rows as you need.
Hope this helps / Lars-Åke
|