Support Board
Support Board
Search Board
Control Panel
View My Posts / Threads
Direct MessagesDate/Time: Sun, 05 May 2024 01:08:07 +0000
Post From: Find Max/Min within specific time range [SPREADSHEET]
| [2021-10-19 21:34:07] |
| Sawtooth - Posts: 3993 |
|
It will require 4 Formula Columns. Try this: 1. Put this in a Formula Column, e.g. O3: =MROUND(A3,1/86400000) This rounds the timestamp to the millisecond to remove any floating point imprecision. 2. Put this in another Formula Column, e.g. P3: =IF(OR(FRACTIME(O3)>=TIMEVALUE("20:00:00"),FRACTIME(O3)<TIMEVALUE("02:00:00")),E3,0) where the start time is 20:00:00 and the end time is 02:00:00. This will return the Close price between the times, else 0. (If the start time is less than the end time, replace OR with AND.) 3. Find the max in P3, e.g. in Q3: =IF(P3>0,MAX(Q4,P3),0) This will return the max Close price between the times. 4. Find the min in P3, e.g. in R3: =IF(AND(P4=0,P3>0),P3,MIN(R4,P3)) This will return the min Close price between the times. This example assumes the chart bars are time based and have bars timestamped with the chosen start and end times. |