Good grief!

This *MONSTER* formula does what you want (I think!). If there are duplicate
max values with multiple dates that fall within the date range the formula
will return the date that is *closest* to the given date. If there are
multiple dates that are of equal absolute difference then the formula will
return the *first instance* of the closest date.

Array entered** :

=INDEX(A1:A21,MATCH(1,(A1:A21=C1-D1)*(A1:A21<=C1+D1)*(B1:B21=MAX(IF((A1:A21=C1-D1)*(A1:A21<=C1+D1),B1:B21)))*(ABS(IF((A1:A21=C1-D1)*(A1:A21<=C1+D1)*(B1:B21=MAX(IF((A1:A21=C1-D1)*(A1:A21<=C1+D1),B1:B21))),A1:A21-C1))=MIN(ABS(IF((A1:A21=C1-D1)*(A1:A21<=C1+D1)*(B1:B21=MAX(IF((A1:A21=C1-D1)*(A1:A21<=C1+D1),B1:B21))),A1:A21)-C1))),0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"Eric" wrote in message
...
When there are duplicates that meet the conditions, I would like to select
based on following conditions
1) the date which is closer to the given date
2) the date which is greater than the given date
I would select the date, which meet most of the conditions, but it does
not
have to meet both.

Based on the last example, the given date is 1-Apr-07, there are 2 dates
with the same values in column B - 6-Mar-07 and 3-Apr-07, then I would
like
to select 3-Apr-07 based on the given conditions.

Do you have any suggestions?
Thank everyone very much for any suggestions
Eric

"T. Valko" wrote:

With the given date of 1 Apr 2007 and the range error being 30, the date
range is:

2 Mar 2007
1 May 2007

Based on your sample data the result I get is: 6 Mar 2007 which
corresponds
to 62%. I do notice that there are duplicate max values within the date
range. The formula will return the *first instance* that meets the
conditions. So, what do you want to happen when there are duplicates that
meet the conditions?

--
Biff
Microsoft Excel MVP


"Eric" wrote in message
...
I get an incorrect result on following example,
Do you have any suggestions on how to fix it?

The given date is on 1 -Apr-07 with range error 30, therefore, the
period
is
between 02-Mar-07 and 1-May-07, the expected result should return
03-Apr-07,
which is closest to the given date 01-Apr-07, but it returns 30-Jan-07
instead.
Do you have any suggestions on how to fix it?
Thank everyone for any suggestions
Eric

02-Jan-07 52% 01-Apr-07 30
09-Jan-07 21%
16-Jan-07 38%
23-Jan-07 64%
30-Jan-07 62%
06-Feb-07 81%
13-Feb-07 100%
20-Feb-07 77%
27-Feb-07 28%

06-Mar-07 62%
13-Mar-07 54%
20-Mar-07 15%
27-Mar-07 31%
03-Apr-07 62%
10-Apr-07 34%
17-Apr-07 15%
24-Apr-07 61%
01-May-07 49%

08-May-07 67%


"T. Valko" wrote:

Ooops!

I see you wanted the DATE that corresponds to the max value. My
formulas
returned that max value.

Try this. Still an array formula** :

=INDEX(A1:A11,MATCH(MAX((A1:A11=C1-3)*(A1:A11<=C1+3)*B1:B11-ROW(B1:B11)/10^10),B1:B11-ROW(B1:B11)/10^10,0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
Using SUMPRODUCT:

=SUMPRODUCT(MAX((A1:A11=C1-3)*(A1:A11<=C1+3)*B1:B11))

Or, this array formula** :

=MAX(IF((A1:A11=C1-3)*(A1:A11<=C1+3),B1:B11))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"Eric" wrote in message
...
Does anyone have any suggestions on how to select value by using
SUMPRODUCT?

In column A, there is a list of date, and in column B, there is a
list
of
value, and
There is a given date in cell C1, and a given error range in cell
D1.
I would like to select the date in column A with the highest value
in
column
B based on the range from the date C1-D1 to the date C1+D1.

For example,
6 Oct 07 65 1 Oct 07 3
5 Oct 07 35
4 Oct 07 44
3 Oct 07 88
2 Oct 07 65
1 Oct 07 17
30 Sep 07 53
29 Sep 07 25
28 Sep 07 44
27 Sep 07 96
26 Sep 07 76

The given range is from 1 Oct 07 - 3 = [28 Sep 07] to 1 Oct 07 +3 =
[4
Oct
07].
The highest value in column B within this range is 88, then return
3
Oct,
07
in cell E1.
Does anyone have any suggesitons?
Thanks in advance for any suggestions
Eric