Dates and Cell Values

  • Thread starter Thread starter beto
  • Start date Start date
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
 
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.
 
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))
 
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.
 
Back
Top