Max or min only if matches date

desoriente

New Member
Joined
Dec 31, 2010
Messages
14
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.
 

Some videos you may like

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
Joined
Jun 30, 2008
Messages
6,653
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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
Joined
Dec 31, 2010
Messages
14
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
Joined
Dec 31, 2010
Messages
14
Disregard last posting. I forgot the control, shift, enter part. Thanks a million! You guys are awesome!! :biggrin:
 

MrKowz

Well-known Member
Joined
Jun 30, 2008
Messages
6,653
Office Version
  1. 365
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Dec 31, 2010
Messages
14
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
Joined
Jun 30, 2008
Messages
6,653
Office Version
  1. 365
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

Using that MAX result, we can do a dual-condition INDEX/MATCH to return the time:
Excel Workbook
ABCDEFG
1DateTimeNumbersDateTimeNumbers
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
Joined
Dec 31, 2010
Messages
14
:biggrin::biggrin: 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!! :ROFLMAO:

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)? :confused:
 

desoriente

New Member
Joined
Dec 31, 2010
Messages
14
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
Joined
Jan 29, 2008
Messages
260
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
 

Watch MrExcel Video

Forum statistics

Threads
1,109,084
Messages
5,526,757
Members
409,718
Latest member
koppahollic

This Week's Hot Topics

Top