Dates and Cell Values

B

beto

How can I get the minimum value from column C for all 8/24/2009 dates in
Column A?

I tried some index and match functions but still doesn't work.

Column A Column B Column C Column D
8/24/2009 12:12:56 AM 113 904
8/24/2009 12:52:56 AM 114 908
8/24/2009 1:32:56 AM 114 907
8/24/2009 2:12:56 AM 112 897
8/24/2009 2:52:56 AM 113 902
8/25/2009 12:13:57 AM 82 654
8/25/2009 12:53:57 AM 81 650
8/25/2009 1:33:57 AM 81 650
8/25/2009 2:13:57 AM 81 650
8/25/2009 2:53:57 AM 81 649
8/25/2009 3:33:57 AM 81 647
8/25/2009 4:13:57 AM 81 646
8/25/2009 4:53:57 AM 81 643
8/25/2009 5:33:57 AM 81 643
8/25/2009 6:13:57 AM 81 647
 
T

T. Valko

Try this array formula** :

E2 = lookup date = 8/24/2009

=MIN(IF(A2:A16=E2,C2:C16))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.
 
B

beto

Thanks it helped me a lot!,
Another question, What if I have the same data but, instead of finding just
8/24/2009, I want to find the maximum value in the following range of dates:
N1=8/25/2009
N2=8/26/2009
N3=8/27/2009
N4=8/28/2009?

I tried this:
MAX(IF(AND($C$5:$C$45000=N1,$C$5:$C$45000=N2,$C$5:$C$45000=N3,$C$5:$C$45000=N4,$C$5:$C$45000=N5,$C$5:$C$45000=N6),$F$5:$F$45000))
 
T

T. Valko

Try this array formula** :

=MAX(IF(ISNUMBER(MATCH(C5:C45000,N1:N4,0)),F5:F45000))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top