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
|