How to determine the highest occurrence?

  • Thread starter Thread starter Eric
  • Start date Start date
E

Eric

Does anyone have any suggestions on how to determine the date?
There is a list of date under column A, and a given number 3 in cell B1
I would like to determine the highest occurrence +/- 3 days

If I select the date 11-Jan-08, the range will be between 8-Jan-08 and
14-Jan-08.
For the occurrence on 11--Jan-08, it should return 2 in cell C1.
Does anyone have any suggestions on how to determine the highest occurrence?
Thanks in advance for any suggestions
Eric

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
....
 
Hi,

This question is unclear, what do you mean by highest occurance? what do
2009 dates have to do with the 2008 dates?

Cheers,
Shane Devenshire
 
Hi,

To add to my previous questions, do you want Excel to calculate when you
select a cell? To get this to happen you will need to add code. If instead
you enter a date in a cell, in the following example, cell A1, I think the
following formula will work in most cases. It assumes the the date run from
A2:A18:

=LARGE(FREQUENCY(A2:A18,ROW(INDIRECT(A1-3&":"&A1+3))),2)

But I still don't know what 2008/2009 dates have to do with this.

Nevertheless, if this helps click the Yes button

Cheers,
Shane Devenshire
 
I need to reword my statement,

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
 
Back
Top