Support Board
Date/Time: Fri, 07 Feb 2025 14:46:38 +0000
Post From: Spreadsheet study complex formula
| [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
|