LookUp or Match Formula: MAX/MIN Value

[artiststevens]

Hello,
Can anyone help me with a creating three lookup, match, or index formulas, whichever works best for the situation?

1.) I need a formula that looks for the MAX value in a column between two specific time periods.

2.) I need a formula that looks for the MIN value in a column between two specific time periods.

3.) I need a formula that finds the value of a cell in the same row but "7" spaces back but I'd also like to be able to change the number of spaces if needed.

The first two formulas can't count cells because the time can shift and move, so it needs to look for that specific time.

Please see example:
OPEN HIGH LOW CLOSE
A1: 3:00 B1: 350 C1: 50 D1: 300
A2: 4:00 B2: 250 C2: 70 D2: 200
A3: 5:00 B3: 200 C3: 100 D3: 300
A4: 6:00 B4: 700 C4: 400 D4: 600
A5: 7:00 B5: 800 C5: 300 D5: 500
A6: 8:00 B6: 500 C6: 300 D6: 700
A7: 4:00 B6: 700 C6: 500 D6: 600

E7:=[MAX] Would contain the formula that would search for the MAX value between the current time "A3"/"4:00" and the 1st previous time "A5"/"5:00" appears in the cells above it only. So it would search between A3:A7 and would return "B5" or 500.

F7:=[MIN] Would contain the formula that would search for the MIN value between the current time "A3"/"4:00" and the 1st previous time "A5"/"5:00" appears in the cells above it only. So it would search between A3:A7 and would return "C3" or 100.

G7:=[Close] Would contain the formula that would search for the value of the cell in the same row "X" number of cells back or the close in the same row. So, "G7" would return
cell "D6" or 600.

Please see attached spreadsheet for a better understanding.

Thanking you for taking the time to read this post and for any and all help you can provide on any of these formulas.

[artiststevens]

Okay one down and two to go. I'm using the following formula to get the close, so I don't need help with that anymore. If anyone can provide help with getting the last instance of MAX/MIN values between two time periods, it would be greatly appreciated.

Please Login or Register  to view this content.

[artiststevens]

Any further thoughts? Maybe if I state what I'm trying to do this way. I looking for a formula that would tell me the MAX/MIN value (two formulas) over the course of the previous 24 hour period between the hours of 5:00 am and 4:00 am the next day. Sometimes it's 24 hours exactly and others it's off 1 or 2 hours so the formula needs to find the calculate the values between the two.

I was able to tweek the following formula but it doesn't take time into account. Any help with this would be greatly appreciated.

MAX

Please Login or Register  to view this content.

See revised spreadsheet attached.

[DonkeyOte]

Not immediately clear - the implication is that row numbers will vary etc etc - on that basis you could consider using an Array

Please Login or Register  to view this content.

[artiststevens]

Thank you DonkeyOte! That was it exactly! I made some slight changes was able to get the MIN value also. Thanks again!