Max or min only if matches date

[desoriente]

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.

 

[MrKowz]

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]

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]

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

 

[MrKowz]

Edit: Awesome! Glad you got it working!

Leaving this example here for future reference.

Excel Workbook
	A	B	C	D	E
1	Date	Numbers
2	12/1/2010	30		12/1/2010	30.1
3	12/1/2010	30.1		12/2/2010	30.01
4	12/1/2010	30.05
5	12/1/2010	29.9
6	12/2/2010	29.99
7	12/2/2010	29.5
8	12/2/2010	30.01
9	12/2/2010	27.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]

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]

Using that MAX result, we can do a dual-condition INDEX/MATCH to return the time:

Excel Workbook
	A	B	C	D	E	F	G
1	Date	Time	Numbers		Date	Time	Numbers
2	12/1/2010	3:42 AM	30		12/1/2010	7:11 PM	30.1
3	12/1/2010	7:11 PM	30.1		12/2/2010	9:52 AM	30.01
4	12/1/2010	2:44 PM	30.1
5	12/1/2010	4:58 PM	29.9
6	12/2/2010	8:16 AM	29.99
7	12/2/2010	10:35 PM	29.5
8	12/2/2010	9:52 AM	30.01
9	12/2/2010	4:38 PM	27.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]

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]

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]

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