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

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

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

Here is a non-array entered formula (simply use the Enter Key to commit it)
that will do what you asked...

=SUMPRODUCT((B1:B1000=SUMPRODUCT(MAX((A1:A1000=C1-D1)*(A1:A1000<=C1+D1)*B1:B1000)))*(A1:A1000=C1-D1)*(A1:A1000<=C1+D1)*A1:A1000)

Here I have provided for 1000 rows (rather than the 11 you showed), but you
can change the 1000s in each range to whatever limit you will ultimately
need to cover.

Rick


"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

One minor point... where you have C1-3 and C1+3 in your formula, you should
have C1-D1 and C1+D1 to match the OP's given location for the "error range"
value.

Rick


"T. Valko" wrote in message
...
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

We can simplify my previously posted formula, as well as make it more
efficient, by removing the inner (unnecessary) SUMPRODUCT function call. Try
this non-array (normally entered) formula instead...

=SUMPRODUCT((B1:B1000=MAX((A1:A1000=C1-D1)*(A1:A1000<=C1+D1)*B1:B1000))*(A1:A1000=C1-D1)*(A1:A1000<=C1+D1)*A1:A1000)

Rick


"Rick Rothstein (MVP - VB)" wrote in
message ...
Here is a non-array entered formula (simply use the Enter Key to commit
it) that will do what you asked...

=SUMPRODUCT((B1:B1000=SUMPRODUCT(MAX((A1:A1000=C1-D1)*(A1:A1000<=C1+D1)*B1:B1000)))*(A1:A1000=C1-D1)*(A1:A1000<=C1+D1)*A1:A1000)

Here I have provided for 1000 rows (rather than the 11 you showed), but
you can change the 1000s in each range to whatever limit you will
ultimately need to cover.

Rick


"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

If there are duplicate max values that fall within the date range you'll get
an incorrect result.

--
Biff
Microsoft Excel MVP


"Rick Rothstein (MVP - VB)" wrote in
message ...
We can simplify my previously posted formula, as well as make it more
efficient, by removing the inner (unnecessary) SUMPRODUCT function call.
Try this non-array (normally entered) formula instead...

=SUMPRODUCT((B1:B1000=MAX((A1:A1000=C1-D1)*(A1:A1000<=C1+D1)*B1:B1000))*(A1:A1000=C1-D1)*(A1:A1000<=C1+D1)*A1:A1000)

Rick


"Rick Rothstein (MVP - VB)" wrote in
message ...
Here is a non-array entered formula (simply use the Enter Key to commit
it) that will do what you asked...

=SUMPRODUCT((B1:B1000=SUMPRODUCT(MAX((A1:A1000=C1-D1)*(A1:A1000<=C1+D1)*B1:B1000)))*(A1:A1000=C1-D1)*(A1:A1000<=C1+D1)*A1:A1000)

Here I have provided for 1000 rows (rather than the 11 you showed), but
you can change the 1000s in each range to whatever limit you will
ultimately need to cover.

Rick


"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

When I tested my formula, I was only worried about duplicate max values
outside of the range... I forgot about the SUM in the name SUMPRODUCT and
how it would affect duplicates within the range. Thanks for catching that.

Rick


"T. Valko" wrote in message
...
If there are duplicate max values that fall within the date range you'll
get an incorrect result.

--
Biff
Microsoft Excel MVP


"Rick Rothstein (MVP - VB)" wrote in
message ...
We can simplify my previously posted formula, as well as make it more
efficient, by removing the inner (unnecessary) SUMPRODUCT function call.
Try this non-array (normally entered) formula instead...

=SUMPRODUCT((B1:B1000=MAX((A1:A1000=C1-D1)*(A1:A1000<=C1+D1)*B1:B1000))*(A1:A1000=C1-D1)*(A1:A1000<=C1+D1)*A1:A1000)

Rick


"Rick Rothstein (MVP - VB)" wrote in
message ...
Here is a non-array entered formula (simply use the Enter Key to commit
it) that will do what you asked...

=SUMPRODUCT((B1:B1000=SUMPRODUCT(MAX((A1:A1000=C1-D1)*(A1:A1000<=C1+D1)*B1:B1000)))*(A1:A1000=C1-D1)*(A1:A1000<=C1+D1)*A1:A1000)

Here I have provided for 1000 rows (rather than the 11 you showed), but
you can change the 1000s in each range to whatever limit you will
ultimately need to cover.

Rick


"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

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

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

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

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