Support Board
Date/Time: Sun, 02 Feb 2025 09:45:11 +0000
[Programming Help] - Question on Formula based on average of last 5 bars to be more than 1.2 times of usual
View Count: 1879
| [2018-12-22 08:29:30] |
| User164231 - Posts: 278 |
|
Hi Tom et al, Say if I would like to detect a surge of ATR by 20 percent within the average of last 5 bars with a look back of 20 bars, is this the correct formula; =AND(AVERAGE(ID5.SG1[0:-5]*1.2>AVERAGE(ID5.SG1[-6:-20]) Thanks. |
| [2018-12-22 15:16:59] |
| Sawtooth - Posts: 4164 |
|
The formula would need to look like this: =AVERAGE(ID5.SG1[0]:ID5.SG1[-5])>AVERAGE(ID5.SG1[-6]:ID5.SG1[-20])*1.2 However, Simple Alert Formulas do not correctly support ranges so, even though you won't get a syntax error, the results will not be accurate. You would need to use a spreadsheet study with this formula in row 3 of one of the Formula Columns (K-Z): =AVERAGE(ID5.SG1@3:ID5.SG1@8)>AVERAGE(ID5.SG1@9:ID5.SG1@23)*1.2 Note: You can't use the Spreadsheet Formula study either because it also uses Simple Alert Formulas, despite its name. |
| [2018-12-26 20:44:05] |
| John - SC Support - Posts: 37656 |
|
Actually, what the user wrote originally will work. The support for ranges in Simple Alerts has been in the software for at least a year now. You will find the documentation on this here: Study/Chart Alerts And Scanning: Referencing A Range of Data I also want to point out that using a 0 in the range for the first average is using the current bar's value, which is constantly changing as data updates. If that is intended then that is fine, but if you really want to reference the previous 5 bars, then you would want to use a range from -1 to -5. For the most reliable, advanced, and zero cost futures order routing, use the Teton service: Sierra Chart Teton Futures Order Routing |
| [2018-12-27 02:23:22] |
| Sawtooth - Posts: 4164 |
|
Hi John, The attached pic shows some differences between a Simple Alert Formula and a Spreadsheet formula, referencing the same study in the same bars, using these respective formulas from post #2: =AVERAGE(ID5.SG1[0]:ID5.SG1[-5])>AVERAGE(ID5.SG1[-6]:ID5.SG1[-20])*1.2 =AVERAGE(ID5.SG1@3:ID5.SG1@8)>AVERAGE(ID5.SG1@9:ID5.SG1@23)*1.2 The magenta dots are from the Simple Alert formula and the cyan dots are from the spreadsheet formula. As you know, the spreadsheet's row 3 is the current bar, and rows below are previous bars, so the row# is always 3 greater than the absolute value of the [#]: [0] = @3 [-1] = @4 [-2] = @5 [-3] = @6 [-4] = @7 [-5] = @8 [-6] = @9 ...etc BTW, the formula in post #1 returns a syntax error. -Tom |
 Simple Alert vs Spreadsheet.PNG / V - Attached On 2018-12-27 02:22:22 UTC - Size: 33.58 KB - 451 views |
| [2018-12-27 17:49:15] |
| John - SC Support - Posts: 37656 |
|
Hi Tom, Yes, the formulas you are using would give different results, as the AVERAGE function in the Simple Alerts does not use the range in the same way as the Spreadsheet. In particular, the equation: =AVERAGE(ID5.SG1[0]:ID5.SG1[-5]) only returns the average of the 0th and -5th bar (just the two bars), not the range of the bars between them. Using this formula: =AVERAGE(ID5.SG1[0:-5]) will return the average of the range, or the average of all 6 bars (0, -1, -2, -3, -4, and -5). Therefore, it is important to consider which method is being used to determine how to write the formula. Also, with regards to the original formula giving a Syntax Error, that is due to the fact that there are some missing parentheses. I did not look that closely at the original, I was simply stating that the use of the range in the original post is the correct way to do it. In addition, there is no need to use the AND in the original, as there is no comparison between two (or more) items. Therefore the following would work: =AVERAGE(ID5.SG1[0:-5])*1.2 > AVERAGE(ID5.SG1[-6:-20]) For the most reliable, advanced, and zero cost futures order routing, use the Teton service: Sierra Chart Teton Futures Order Routing |
| [2019-08-24 08:00:41] |
| User164231 - Posts: 278 |
|
Dear Tom, Dear John, Say if I want to add another condition which is one or more of the 5 bars to exceed certain value (say *3) . What should I add on to the existing code; =AND(ID3.SG1[0:-5])>(ID3.SG1[-6:-20]),BARTIME>=TIME(9,00,0),BARTIME<=TIME(16,30,0)) Thanks Date Time Of Last Edit: 2019-08-24 08:16:24
|
| [2019-08-26 12:23:33] |
| John - SC Support - Posts: 37656 |
|
You would need to add the test for each bar individually to the AND. For example, here is the above formula with a test for the bar at -5: =AND(ID3.SG1[0:-5])>(ID3.SG1[-6:-20]),BARTIME>=TIME(9,00,0),BARTIME<=TIME(16,30,0), ID3.SG1[-5] > 3) That you can have more than 2 test items in AND function. Refer to the documentation for AND here: Study/Chart Alerts And Scanning: OR and AND Functions Also note on that page the statement that if the alert is getting too long and/or complex you may want to use the Spreadsheet Alert study instead. 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: