# Max or min only if matches date

#### desoriente

##### New Member
How do you get a maximum or minimum number based on two criteria (MAX or MIN if within date specified)?

DATE (Column A) ---- NUMBERS (Column B) ---- MAX OR MIN OF SPECIFIC DATE (Column C)
12/1/2010 -------- 30.00 ------ Maximum for 12/1/10 is: ?
12/1/2010 -------- 30.10 ------ Minimum for 12/1/10 is: ?
12/2/2010 -------- 29.99 ------ Maximum for 12/2/10 is: ?
12/2/2010 -------- 29.50 ------ Minimum for 12/2/10 is: ?
etc., etc.

Basically, I need a formula that produces a "Max" or "Min" for Column C, using the range of numbers in Column B AND only IF that range of numbers falls within a specific date in Column A, such as 12/1/10, 12/2/10, etc.

I can't get VLOOKUP, SUMPRODUCT, or DSUM to work. Any suggestions? By the way, I have Excel 2007. Thank you.

### Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

#### MrKowz

##### Well-known Member
Try:

=MAX(IF(\$A\$1:\$A\$100=C1,\$B\$1:\$B\$100,""))

Confirm entry with CTRL+SHIFT+ENTER to get {brackets} around the formula so it is entered as an array.

#### desoriente

##### New Member
Tried it and it doesn't work. It gives me the max of ALL the values, not only for 12/1/10. Any other formula? Thanks again.

#### desoriente

##### New Member
Disregard last posting. I forgot the control, shift, enter part. Thanks a million! You guys are awesome!!

#### MrKowz

##### Well-known Member

Edit: Awesome! Glad you got it working!

Leaving this example here for future reference.

Excel Workbook
ABCDE
1DateNumbers
212/1/20103012/1/201030.1
312/1/201030.112/2/201030.01
412/1/201030.05
512/1/201029.9
612/2/201029.99
712/2/201029.5
812/2/201030.01
912/2/201027.88
Sheet2

Excel 2003
#VALUE!
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter these manually yourself

#### desoriente

##### New Member
Thanks for the chart. One more question on the chart you provided. Let's add one more variable. Next to Column A (Date), the new variable (Column B) has different times (e.g., 12:30 P.M., 1:30 A.M., etc.).

How can you obtain the specific time that max or min occurred on that specific date in Column E? So, in the example you gave below, on 12/1/10, the max was 30.10. But, it occurred at 1:30 A.M. How can we obtain that time?

#### MrKowz

##### Well-known Member

Using that MAX result, we can do a dual-condition INDEX/MATCH to return the time:
Excel Workbook
ABCDEFG
212/1/20103:42 AM3012/1/20107:11 PM30.1
312/1/20107:11 PM30.112/2/20109:52 AM30.01
412/1/20102:44 PM30.1
512/1/20104:58 PM29.9
612/2/20108:16 AM29.99
712/2/201010:35 PM29.5
812/2/20109:52 AM30.01
912/2/20104:38 PM27.88
Sheet2
Excel 2003
#VALUE!
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.

Note, that if you have MORE THAN ONE of the MAX value (for example you have two 30.10 entries for 12/1/2010, like in the above example), it will return the time of the first occurance it comes across, not the earliest/latest time.

#### desoriente

##### New Member
You just don't understand how much work you just saved me!!!!! All of your formulas worked and it has produced an entire MONTH worth of data in a matter of minutes!!

I guess my only complaint is that it takes 2.5 minutes to open. Any suggestions on improving the speed it calculates (it says 8 processors calculating)?

#### desoriente

##### New Member
Re: Max or min only if matches date & Time

The formulas above ALL work but makes it difficult to open the worksheet. It takes 2.5 minutes to load. Any suggestions to make it load quicker?

#### MrRajKumar

##### Active Member
Create the range names & use it in the formulas. Your data should be huge.

Try

Sheet3

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 75px"><COL style="WIDTH: 69px"><COL style="WIDTH: 63px"><COL style="WIDTH: 8px"><COL style="WIDTH: 75px"><COL style="WIDTH: 70px"><COL style="WIDTH: 63px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Date</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Time</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Numbers</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Date</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Time</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Numbers</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">12/1/2010</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">3:42 AM</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">30</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"> </TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">12/1/2010</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">7:11 PM</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">30.1</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">12/1/2010</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">7:11 PM</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">30.1</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"> </TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">12/2/2010</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">9:52 AM</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">30.01</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">12/1/2010</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">2:44 PM</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">30.1</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">12/1/2010</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">4:58 PM</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">29.9</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">12/2/2010</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">8:16 AM</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">29.99</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">12/2/2010</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">10:35 PM</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">29.5</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">12/2/2010</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">9:52 AM</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">30.01</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">12/2/2010</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">4:38 PM</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">27.88</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"> </TD><TD> </TD><TD> </TD><TD> </TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>F2</TD><TD>=INDEX(Time,MATCH(1,INDEX((Dates=E2)*(Numbers=G2),),0))</TD></TR><TR><TD>G2</TD><TD>=MAX(INDEX((Dates=E2)*Numbers,))</TD></TR><TR><TD>F3</TD><TD>=INDEX(Time,MATCH(1,INDEX((Dates=E3)*(Numbers=G3),),0))</TD></TR><TR><TD>G3</TD><TD>=MAX(INDEX((Dates=E3)*Numbers,))</TD></TR></TBODY></TABLE></TD></TR><TR><TD><TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Names in Formulas </TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Name</TD><TD>Applies to</TD></TR><TR><TD>F2</TD><TD>Dates</TD><TD>=Sheet3!\$A\$2:INDEX(Sheet3!\$A:\$A,MATCH(1E+307,Sheet3!\$A:\$A))</TD></TR><TR><TD>F2</TD><TD>Numbers</TD><TD>=Sheet3!\$C\$2:INDEX(Sheet3!\$C:\$C,MATCH(1E+307,Sheet3!\$A:\$A))</TD></TR><TR><TD>F2</TD><TD>Time</TD><TD>=Sheet3!\$B\$2:INDEX(Sheet3!\$B:\$B,MATCH(1E+307,Sheet3!\$A:\$A))</TD></TR><TR><TD>G2</TD><TD>Dates</TD><TD>=Sheet3!\$A\$2:INDEX(Sheet3!\$A:\$A,MATCH(1E+307,Sheet3!\$A:\$A))</TD></TR><TR><TD>G2</TD><TD>Numbers</TD><TD>=Sheet3!\$C\$2:INDEX(Sheet3!\$C:\$C,MATCH(1E+307,Sheet3!\$A:\$A))</TD></TR><TR><TD>F3</TD><TD>Dates</TD><TD>=Sheet3!\$A\$2:INDEX(Sheet3!\$A:\$A,MATCH(1E+307,Sheet3!\$A:\$A))</TD></TR><TR><TD>F3</TD><TD>Numbers</TD><TD>=Sheet3!\$C\$2:INDEX(Sheet3!\$C:\$C,MATCH(1E+307,Sheet3!\$A:\$A))</TD></TR><TR><TD>F3</TD><TD>Time</TD><TD>=Sheet3!\$B\$2:INDEX(Sheet3!\$B:\$B,MATCH(1E+307,Sheet3!\$A:\$A))</TD></TR><TR><TD>G3</TD><TD>Dates</TD><TD>=Sheet3!\$A\$2:INDEX(Sheet3!\$A:\$A,MATCH(1E+307,Sheet3!\$A:\$A))</TD></TR><TR><TD>G3</TD><TD>Numbers</TD><TD>=Sheet3!\$C\$2:INDEX(Sheet3!\$C:\$C,MATCH(1E+307,Sheet3!\$A:\$A))</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

Replies
5
Views
71
Replies
4
Views
74
Replies
3
Views
33
Replies
3
Views
100
Replies
3
Views
52