Support Board
Date/Time: Fri, 07 Feb 2025 18:15:49 +0000
Spreadsheet study complex formula
View Count: 547
| [2022-06-20 10:24:01] |
| oliebol03 - Posts: 4 |
|
I'm using a custom study that looks for imbalances based on specific inputs. The study has 15 sg's and when there is an imbalance sg1 will output the price level. So there could be an instance where 15 price levels are output to the tick. When there are no imbalances the sg's will output 0. I would like to return a value of 1 when there are either 3 imbalances right on top of each other (so XXX), or when there are 2 imbalances on top of each other and 1 imbalance above/below with only 1 tick difference (so XX X or X XX), or when there are 3 imbalances each with one tick in between (so X X X). First I used this function for every of the 15 columns; =IF(BT3=0,NOVALUE,BT3) This replaces the 0 output when there is no imbalance to a novalue. I then tried to use SMALL(2)-SMALL(1) <= 0.5 and SMALL(3)-SMALL(2) <= 0.5. If both are <= 0.5 then it would output 1 which works but because SMALL() is used it will only find imbalances at the lower end of bars so it would give a false negative when there is 1 imbalance at the bottom of the bar and there are 3 at the top at the bar. In this case a 1 output is wanted but obviously it is not given. Is there a way to get this right? I've been on the documentation and support board for hours but just can't figure it out. Maybe I can look for 3 values in a row that are the closest to each other and then see if the differences are bigger or smaller than desired? I looked for that but it seems that to look for values 'closest' to another value you'd have to input a reference value to match and can't reference a row itself without reference to a value to compare to. Hope it all makes sense if not let me know and I can provide all additional info needed. Thanks in advance! edit: I want a 1 output if conditions are met so I can use a color bar based on alert condition to highlight that specific bar but if it is easier to do this in another way that's is fine too. Date Time Of Last Edit: 2022-06-20 10:26:42
|
| [2022-06-20 15:00:54] |
| John - SC Support - Posts: 37828 |
|
The only way we can think to do this with the Spreadsheet is to use the Volume at Price Threshold Alert V2, as the alert subgraphs contain the price levels, so you can check the price levels of the alerts to see if they fit into the pattern you want. You can also use the Highlight Adjacent Alerts Minimum Group Size to find your groupings of 3 or more by just testing against the subgraphs that contain the Adjacent Alerts Highlight Top. Refer to the information for the Volume at Price Threshold Alert V2 here: Volume at Price Threshold Alert V2 For the most reliable, advanced, and zero cost futures order routing, use the Teton service: Sierra Chart Teton Futures Order Routing |
To post a message in this thread, you need to log in with your Sierra Chart account: