locksmith55
New Member
- Joined
- Mar 6, 2022
- Messages
- 22
- Office Version
- 365
- Platform
- Windows
Hi,
I am trying to find the local max and min values and their corresponding time in the included spreadsheet. The "trade high" needs to be used to find the local max and the "trade low" needs to be used to find the local min. I have earlier tried to do this by comparing the current price with the previous price and the next price, however, this gives too many values in my case.
The solution can use multiple formulas or multiple helper columns, as long as it is dynamical.
I have attached a picture of the stock chart that corresponds to this data. As you can see I am trying to capture the data that is circled in the picture. A few things to know. Almost all local min to local max have at least 10 minutes pasted between them. Also the local min to local max should have atleast 5% price increase between them.
I dont need to be able to capture every local max and local min as long as I can capture the general move like in shown in the picture.
Note that not all the values in the picture are 100% perfect, but they are very close.
I am trying to find the local max and min values and their corresponding time in the included spreadsheet. The "trade high" needs to be used to find the local max and the "trade low" needs to be used to find the local min. I have earlier tried to do this by comparing the current price with the previous price and the next price, however, this gives too many values in my case.
The solution can use multiple formulas or multiple helper columns, as long as it is dynamical.
I have attached a picture of the stock chart that corresponds to this data. As you can see I am trying to capture the data that is circled in the picture. A few things to know. Almost all local min to local max have at least 10 minutes pasted between them. Also the local min to local max should have atleast 5% price increase between them.
I dont need to be able to capture every local max and local min as long as I can capture the general move like in shown in the picture.
Note that not all the values in the picture are 100% perfect, but they are very close.
Cell Formulas | ||
---|---|---|
Range | Formula | |
R2 | R2 | =INDEX($G:$G,$K$13) |
S2 | S2 | =INDEX($B:$B,$K$13) |
T2 | T2 | =INDEX($C:$C,$K$13) |
R3:R209 | R3 | =IF($K$13-ROW(1:1)<$K$16,"",INDEX($G:$G,$K$13-ROW(1:1))) |
S3:S209 | S3 | =IF($K$13-ROW(1:1)<$K$16,"",INDEX($B:$B,$K$13-ROW(1:1))) |
T3:T209 | T3 | =IF($K$13-ROW(1:1)<$K$16,"",INDEX($C:$C,$K$13-ROW(1:1))) |