Home >> Support Board

Support Board

Date/Time: Sun, 28 Apr 2024 07:15:42 +0000

How to Post a Message for Support

Spreadsheet, formula including all session bars

View Count: 242

[2024-02-18 13:09:47] #1 [Go To First Post]
User440951 - Posts: 34
Hi, I would like to create a formula in a spreadsheet that gives me an alert when in the session, 10:00:00 p.m. to 9:59:59 p.m. for me, the price has touched two very specific levels. To keep it simple in this request, 1 will be true and 0 false. I tried this : =IF(AND(CROSSOVER(ID0.SG4, ID2.SG1), CROSSOVER(ID0.SG4, ID2.SG2)), 1, 0) Or this =IF(AND(AND(FRACTIME(J41)>=TIME(22, 00, 00, 750), FRACTIME(J41)<TIME(21, 59, 59, 250)), CROSSOVER(ID0.SG4, ID2.SG1), CROSSOVER(ID0.SG4, ID2.SG2)), 1, 0) But it always returns false to me even though the conditions are met in the sessions. I can't find the solution, could you help me?

[2024-02-18 14:20:37] #2 [Go To First Post]
Sawtooth - Posts: 3980
In spreadsheet syntax, the CROSSOVER function requires a range, and a reference to the row#: =CROSSOVER(ID0.SG4@3:ID0.SG4@4, ID2.SG1@3:ID2.SG1@4) https://www.sierrachart.com/index.php?page=doc/SpreadsheetFunctions.html#CROSSOVER_Function Working with Spreadsheets: References to Study Subgraph Columns when using the Spreadsheet Study

[2024-02-18 14:34:24] #3 [Go To First Post]
User440951 - Posts: 34
Thanks for your return. But in my case how can I integrate all the bars of the session? Those past and those to come?

[2024-02-18 14:56:49] #5 [Go To First Post]
User440951 - Posts: 34
Thanks for your feedback. In fact, it is a spreadsheet formula but not just an alert and therefore it seems to me possible to use the IF function. I took 1 and 0 as true and false values to simplify my request, but in fact what I want to do is when the price has not touched two pivot points R1 and R2 (only one is possible) for example, it draws me a line at X% of point R1, but if the price touched R1 AND R2 it draws me a line at Y% of R1 and this in the session only. For the next session everything is reset to 0.

[2024-02-18 21:20:24] #6 [Go To First Post]
Sawtooth - Posts: 3980
=IF() is not a supported function The IF function is supported in both Alert syntax and spreadsheet syntax. what I want to do is when the price has not touched two pivot points R1 and R2 (only one is possible) for example, it draws me a line at X% of point R1, but if the price touched R1 AND R2 it draws me a line at Y% of R1 and this in the session only. - You would need to create a persistent variable when price has not yet touched R1 and R2. - Then release it and create another persistent variable when price has touched both R1 and R2. - Once the formulas return values, use another spreadsheet column to limit their display to the session times. Here are some formula examples of persistent variables: Spreadsheet Example Formulas and Usage: Persistent and Incrementing Variables Notice that one IF starts the persistence, and a second IF releases it.

[2024-03-24 18:15:54] #7 [Go To First Post]
User440951 - Posts: 34
I worked on the subject, it was complicated but I think something happened. I was able to create my persistent variables in columns in this form : =IF(CROSSFROMABOVE(ID0.SG3@3:ID0.SG3@4,ID2.SG3@3:ID2.SG3@4),1,AQ4) And I was able to modify my plots when the conditions are true with this formula in another column : =IF(AQ3=1,(ID2.SG13@3-((ID2.SG13@3-ID2.SG3@3)0.236)),(ID2.SG13@3-((ID2.SG13@3-L3)0.236))) On the other hand, I have concerns about persistent variables which set me to true when it should be false. I think it comes from the fact that it doesn't take into account the session only. You say "- Once the formulas return values, use another spreadsheet column to limit their display to the session times" How can I do this ? Is this a formula that will impact all persistent variables ?

[2024-03-24 20:11:35] #8 [Go To First Post]
Sawtooth - Posts: 3980
This is the basic idea to limit display to session times: =AND(FRACTIME(A3)>=TIME(9,30,0),FRACTIME(A3)<TIME(16,00,0)) You could put this in a spare Formula Column, e.g. AR3, and reference that column in each of your line formulas, like this: =IF(AND(AQ3=1,AR3=1),(ID2.SG13@3-((ID2.SG13@3-ID2.SG3@3)0.236)),(ID2.SG13@3-((ID2.SG13@3-L3)0.236)))

[2024-03-24 21:05:34] #9 [Go To First Post]
User440951 - Posts: 34
I'm going to test this. Thank you so much !

[2024-03-24 21:30:33] #10 [Go To First Post]
User440951 - Posts: 34
One more little thing, I hear my session from 10 p.m. until 9:59 p.m. the next day. should I put this into a formula ? : =AND(FRACTIME(A3)>=TIME(22,00,0),FRACTIME(A3)<TIME(21,59,59))

[2024-03-24 22:59:03] #11 [Go To First Post]
Sawtooth - Posts: 3980
If you cross Midnight, use OR instead of AND: =OR(FRACTIME(A3)>=TIME(22,00,0),FRACTIME(A3)<TIME(21,59,59))

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