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

Assuming real dates in A1:A17 as posted
In B1: 3
Then in C1:
=SUMPRODUCT((A1:A17>="11-Jan-09"-B1)*(A1:A17<="11-Jan-09"+B1))
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,500 Files:363 Subscribers:64
xdemechanik
---
"Eric" wrote:
> 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

"Eric" wrote:

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

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

"Eric" wrote:

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

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


"Shane Devenshire" wrote:

> 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
>
> "Eric" wrote:
>
> > 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
> > ...
> >