Forum Discussion
max values for changing cell references / segments in long list
Hello,
Assume
- Column A contains a stock price, so: randomly increasing and decreasing numbers
- Column B and C contains the date and time of the respective stock price, with 200 to 400 different times every day.
The issue: How to find the max value between - example - 16:30 and 17:00 every day? Evidently without entering into every day and adjusting Max formula manually.
Any brillant ideas? Anybody can help?
Thank you,
Torsten
6 Replies
Torsten,
try a pivot table.
"Date" and "Time" in the rows area. Filter "Time" >=16:30 and <=17:00.
"Stock price" in the values area and change the function to "Average".
Thank you, Detlef. Good and interesting workaround for some cases. Yet, doesn't really do the trick as the triggering times are changing, so I'd need to set the filter per day - almost same as changing the formula, or - likely better - blowing up the database such that each day starts and ends in predefined cells and then building workarounds.
Thanks again, T
"Yet, doesn't really do the trick as the triggering times are changing, so I'd need to set the filter per day"
Be more specific.
On what basis are the triggering times changing and why do you have to filter per day?
And it would be nice if you could provide a sample workbook.
- ... and just adding: I don't need how to identify the times, I know how to identify the respective cell and price for the times I'm looking for. Just don't know how to pull out the max / min between those two event.
Thank you, T
