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 said:
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 said:
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%
:
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
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
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
