Home >> Support Board

Support Board

Date/Time: Thu, 25 Apr 2024 00:30:56 +0000

How to Post a Message for Support

How to syntax PERCENTILE using built in spreadsheet formula indicator

View Count: 429

[2022-12-07 07:51:42] #1 [Go To First Post]
VolTrader73 - Posts: 131
Hello. I would like to plot the 90th percentile of the last 100 values of an indicator (ID16.SG1) on a chart. Using the built-in Spreadsheet Formula indicator on the same chart what is the formula exactly ? =PERCENTILE(last 100 bars of ID16.SG1 , 0.9) ??? Please advise

[2022-12-07 16:14:56] #2 [Go To First Post]
John - SC Support - Posts: 31098
It would be the following in the Spreadsheet Study: =PERCENTILE(ID16.SG1@3:ID16.SG1@102, .9) For the most reliable, advanced, and zero cost futures order routing, use the Teton service: Sierra Chart Teton Futures Order Routing

[2022-12-07 17:40:45] #3 [Go To First Post]
VolTrader73 - Posts: 131
Thank you And for educational purposes, can you explain this part @3:ID16.SG1@102? Is this how you describe an Array ? if so why 3 and 102 to reference the last 100 bars?

[2022-12-07 17:49:38] #4 [Go To First Post]
VolTrader73 - Posts: 131
=PERCENTILE(ID16.SG1@3:ID16.SG1@102,0.9) with auto scaling and value format same as for ID16 Returns no value. ID16 returns any thing between 0.5 to -0.5 pls advise

[2022-12-07 21:32:02] #5 [Go To First Post]
John - SC Support - Posts: 31098
And for educational purposes, can you explain this part @3:ID16.SG1@102? Within the Spreadsheet Study you have a few ways to reference the data for a subgraph from a study on the chart. Keep in mind that the Spreadsheet Study will include other studies' data starting in the first column after the Calculation columns (Column AA by default). As such, you can reference the data by standard spreadsheet reference such as AA3:AA102, but the issue is that if you add a study, or change the order, then the data in column AA may not be for the same study any more. The syntax of IDx.SGy@3 gives a direct reference to the study (by ID) and then the subgraph (by SG) and @3 is the row number. In the Spreadsheet Study the rows for data start at row #3. That is why the reference to starting at row 3 and going to 102 for 100 pieces of data. Refer to the following section for more on the above: Working with Spreadsheets: References to Study Subgraph Columns when using the Spreadsheet Study =PERCENTILE(ID16.SG1@3:ID16.SG1@102,0.9) with auto scaling and value format same as for ID16 Returns no value. ID16 returns any thing between 0.5 to -0.5 pls advise Is this being entered on a Spreadsheet Study? If so, what are you seeing in the cells where you entered this formula? For the most reliable, advanced, and zero cost futures order routing, use the Teton service: Sierra Chart Teton Futures Order Routing

[2022-12-08 14:39:13] #6 [Go To First Post]
VolTrader73 - Posts: 131
As per my original post here, Im using the spreadsheet formula study please see attached screen


PERCENTILE.jpg / V - Attached On 2022-12-08 14:38:44 UTC - Size: 101.55 KB - 68 views

[2022-12-08 14:51:57] #7 [Go To First Post]
John - SC Support - Posts: 31098
We just tested the Spreadsheet Formula for the Percentile function and although it is not giving a syntax error, it is giving a different result from what the Spreadsheet Study gives. Therefore, we can only say that you should only use the function with the Spreadsheet Study, which is where it was developed for use. When using it in the Spreadsheet Study, you will use the syntax that we gave you previously. Keep in mind, even though the Study is called "Spreadsheet Formula" it actually uses the syntax for the Alerts and not for the Spreadsheets. For the most reliable, advanced, and zero cost futures order routing, use the Teton service: Sierra Chart Teton Futures Order Routing

[2022-12-12 08:34:41] #8 [Go To First Post]
VolTrader73 - Posts: 131
Can you please share the syntax for the spreadsheet formula you use to calc percentile so I can copy paste. Im aware it will give a diff result than by using the (excel-like) spreadsheet. Thank you

[2022-12-12 14:29:17] #9 [Go To First Post]
Sawtooth - Posts: 3976
This seems to work for me in the Spreadsheet Formula study: =PERCENTILE(ID16.SG1[0:-100],0.9) using this Alert syntax for ranges: Study/Chart Alerts And Scanning: Referencing A Range of Data It produces values that are very close (but not exactly the same) when using this spreadsheet syntax in the Spreadsheet Study study: =PERCENTILE(ID16.SG1@3:ID16.SG1@102,0.9)

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