Home >> Support Board

Support Board

Date/Time: Sat, 04 May 2024 03:21:48 +0000

How to Post a Message for Support

[Programming Help] - If Condition is met, then enter position if other condition is met

View Count: 2297

[2018-04-29 18:29:17] #1 [Go To First Post]
User172040 - Posts: 70
Hi, I'm learning the spreadsheet study and trying to refine my testing. In essence I want the K3 column (Buy Entry) to trigger a 1 when the following condition is met: =AND(ABS($J$8)=0,AH3>AF3,AL3>AJ3,D3<=AC3) (This part is already working) But in order to refine it, I want only to trigger a buy order when the above condition is true and then after this condition has been met the system should trigger a long when the 5MA cross the 9MA of my entry chart. I tried to make use of a IF statement but get a ARGS! error. Here's the code I used: =IF(AND(ABS($J$8)=0,AH3>AF3,AL3>AJ3,D3<=AC3),CROSSFROMBELOW(AN3:AN4,AO3:AO4)) -- (AN = 5MA & AO = 9MA) That's obviously not right. Can you kindly guide me in the right direction?

[2018-04-29 23:33:16] #2 [Go To First Post]
Sawtooth - Posts: 3993
Try this: In a spare Formula Column, e.g., P3, create a persistent variable: =IF(AND(AH3>AF3,AL3>AJ3,D3<=AC3),1,IF(AND(AH3<AF3,AL3<AJ3,D3>=AC3),0,P4)) (You can use any viable condition to clear the persistent variable. In the above example, I reversed the operators.) Then in K3: =AND(CROSSFROMBELOW(AN3:AN4,AO3:AO4),P3) (You don't need to reference J8=0 if 'Allow Multiple Entries In Same Direction' is set to No.) Here are more formula examples of persistent variables: Spreadsheet Example Formulas and Usage: Persistent and Incrementing Variables Date Time Of Last Edit: 2018-04-30 13:40:22

[2018-05-01 13:22:53] #3 [Go To First Post]
User172040 - Posts: 70
Hi tomgilb, thanks for the reply. With the above formula I get entries with every MA crossover (unless I'm already in a position). So when I'm not in a position, the system triggers a long / short with every crossfrombelow / crossfromabove signal. I only want to enter when the MA's have crossed AFTER the 1st condition has been met. Can you recommend additional sources or videos to study the SC spreadsheet functions?

[2018-05-01 14:33:43] #4 [Go To First Post]
Sawtooth - Posts: 3993
The K3 formula above can only go TRUE after the P3 formula is persistent TRUE, so it's already doing what you want. It seems you have another entry condition not mentioned, or you need to use another condition to clear the P3 persistent variable. I don't know of any additional resources that give examples at this more advanced level. A persistent variable formula is very useful, but there are so many permutations that more examples become very specific and therefore not of general use. To further complicate things, as more variations are used, potential circular reference errors will need to be worked around.

[2018-05-06 04:03:40] #5 [Go To First Post]
User172040 - Posts: 70
Hey @tomgilb, your input is invaluable - so thank you. I've gone through most of the spreadsheet study documentation now. I guess, I'll still post here regularly :), but I am learning as I am doing the spreadsheets. I think my header is misleading. I'm going to rephrase: Let's say, for test purposes, I have 2 buying conditions BA1 (price under volume value area low) and BA2 (5MA crossing 9MA). IF BA1 = TRUE, then I only want to enter a position if BA2 = TRUE. The problem is, by the time BA2 = TRUE, BA1 might not be TRUE anymore (price was under volume value area low but now with the MA crossover, price is above the volume value area low). But I don't care about that. BA1 WAS true just a few bars ago and now that BA2 is true, I want to enter. I only want to enter on the 1st crossover of MA's after BA1 was true - not subsequent crossovers thereafter. Does that make it a bit more clear? If I use the =AND() function, the formula will look for conditions where BOTH are TRUE at the same time. But as mentioned, that might not be the case by the time BA2 = TRUE. Thanks for your guidance Date Time Of Last Edit: 2018-05-06 04:04:00

[2018-05-06 16:34:03] #6 [Go To First Post]
Sawtooth - Posts: 3993
You'll need to use a condition to release the BA1 persistent variable that won't occur before BA2 becomes TRUE. Then you may also need to create a BA2 persistent variable that stays TRUE so that you don't get multiple signals, e.g. in R3. Then in K3, find where BA2 first goes TRUE with a formula like this: =AND(R4=0,R3) You'll need to find the correct conditions to release BA1 and BA2 so that they are ready to set up the next entry. This is a specific as I can be without knowing the exact details of your system.

To post a message in this thread, you need to log in with your Sierra Chart account: