How to select the value based on given condition?
 

Does anyone have any suggestions on how to select the highest value for
specific date within specific month?
In A column, there is a list of date, and in B column, there is a list of
value, and
in cell C1, there is a given date.

I would like to select the date with the highest value within the given
month in cell C1, for example

6 Oct 07 65 30 Oct 07
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

Given date is 30 Oct 07, I would select 3 Oct 07, because it is the highest
value in column within the given month Oct, and the date 3 Oct 07 returns in
cell D1.
Does anyone have any suggestions?
Thanks in advance for any suggestions
Eric

How to select the value based on given condition?
 

Try this array* formula in D1:

=MAX(IF((MONTH(A$1:A$100)=MONTH($C$1))*(YEAR(A$1:A $100)=YEAR($C$1)),B$1:B$100,0))

to return the maximum value, and then use an INDEX/MATCH formula in E1 to
return the matching date.

* As this is an array formula, then once you have typed it in (or
subsequently edit it) you must use CTRL-SHIFT_ENTER to commit it, rather than
the usual ENTER. If you do this correctly, then Excel will wrap curly braces
{ } around the formula when viewed in the formula bar - you must not type
these yourself.

Hope this helps.

Pete



"Eric" wrote:

Does anyone have any suggestions on how to select the highest value for
specific date within specific month?
In A column, there is a list of date, and in B column, there is a list of
value, and
in cell C1, there is a given date.

I would like to select the date with the highest value within the given
month in cell C1, for example

6 Oct 07 65 30 Oct 07
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

Given date is 30 Oct 07, I would select 3 Oct 07, because it is the highest
value in column within the given month Oct, and the date 3 Oct 07 returns in
cell D1.
Does anyone have any suggestions?
Thanks in advance for any suggestions
Eric

How to select the value based on given condition?
 

Hi Eric

Enter the following array formula into cell D1
{=INDEX(A1:A11,MATCH(MAX(IF(TEXT(A1:A11,"yymm")
=TEXT(C1,"yymm"),B1:B11,"")),B1:B11,0))}

To enter or amend array formulae, use Control+Shift+Enter (CSE) not just
Enter.
When you use CSE, Excel will insert the curly braces { } around the
formula. Do not enter them yourself.
--
Regards
Roger Govier



"Eric" wrote in message
...
Does anyone have any suggestions on how to select the highest value for
specific date within specific month?
In A column, there is a list of date, and in B column, there is a list of
value, and
in cell C1, there is a given date.

I would like to select the date with the highest value within the given
month in cell C1, for example

6 Oct 07 65 30 Oct 07
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

Given date is 30 Oct 07, I would select 3 Oct 07, because it is the
highest
value in column within the given month Oct, and the date 3 Oct 07 returns
in
cell D1.
Does anyone have any suggestions?
Thanks in advance for any suggestions
Eric