Spreadsheet Formulas

css navigation by Css3Menu.com

Max Min Match

I have a spreadsheet where I have been tracking the daily prices of my stocks for over 5 years. I recently decided that I wanted to know when my portfolio had peaked and hit its low.

Using INDEX, MATCH, MIN, and MAX, I get the amounts of the peaks and valleys and the dates.

Max Min   Max Date Min Date
$40,616.50 $35,180.70 Value 11/24/2004 09/20/2001
11,313.51 7,286.27 DJIA 08/02/2000 09/05/2002
4,274.67 1,114.09 NASDAQ 06/08/2000 09/05/2002
1,184.17 993.70 S&P500 10/05/2004 07/02/2003

The numbers (my portfolio values have been altered).

Max Min  
=MAX(B2:B1000) =MIN(B2:B1000) Value
=MAX(C27:C1000) =MIN(C27:C1000) DJIA
=MAX(D27:D1000) =MIN(D27:D1000) NASDAQ
=MAX(E28:E1001) =MIN(E28:E1001) S&P500

MINimum and MAXimum

Max Date
=INDEX($A$1:$A$1065,MATCH(MAX($B$2:$B1065),$B$2:$B$1065,0))
=INDEX($A$1:$A$1065,MATCH(MAX($C$27:$C1066),$C$27:$C$1065,0))
=INDEX($A$1:$A$1065,MATCH(MAX($D$27:$D1067),$D$27:$D$1065,0))
=INDEX($A$1:$A$1065,MATCH(MAX($E$27:$E1068),$E$27:$E$1065,0))

MAX Date

Min Date
=INDEX($A$1:$A$1065,MATCH(MIN($B$2:$B1065),$B$2:$B$1065,0))
=INDEX($A$1:$A$1065,MATCH(MIN($C$27:$C1066),$C$27:$C$1065,0))
=INDEX($A$1:$A$1065,MATCH(MIN($D$27:$D1067),$D$27:$D$1065,0))
=INDEX($A$1:$A$1065,MATCH(MIN($E$27:$E1068),$E$27:$E$1065,0))

MIN Date


INDEX is used to determine what row the MAX or MIN occurs. MATCH is used to

© 2004-2017

Updated:  04/06/2017 20:32
This page added:  26 November 2004