Support Board
Date/Time: Tue, 26 Nov 2024 10:24:30 +0000
[User Discussion] - Compare range of individual values
View Count: 485
| [2023-12-11 00:27:12] |
| User273277 - Posts: 58 |
|
I want to find the first swing low in the most recent 100 bars that is above the current bar low. Something like this where it returns the first occurrence: L<ID1.SG2[0:-100] But it looks like spreadsheets can not iterate over a range of values: Study/Chart Alerts And Scanning: Referencing A Range of Data "... this is not valid: =SG1[0:-10] > 100 ... This requires an iteration which is not possible with Spreadsheet formulas." The documenation page does not specify how this iteration would be done. Any help would be appreciated. |
| [2023-12-11 17:00:51] |
| John - SC Support - Posts: 36286 |
|
Spreadsheets do not iterate, that is correct. But you can use the "EARLIESTNONZEROVALUE" to find the first instance of a Swing Low within a range of data. Refer to the following: https://www.sierrachart.com/index.php?page=doc/SpreadsheetFunctions.html#EARLIESTNONZEROVALUE_Function You would have to use multiple columns to get consecutive values to test against your condition, then have a column that gives you the answer you want. For the most reliable, advanced, and zero cost futures order routing, use the Teton service: Sierra Chart Teton Futures Order Routing |
| [2023-12-12 04:13:40] |
| User273277 - Posts: 58 |
|
Thank you. That helped get the code most of the way there on the first try. For the multiple columns, I have tried to see how to use things like vlookup, search, nmatch, but I don't see how any of these can select the x value in the swing low list. Ie. it looks like I need to do something like =MOSTRECENTNONZEROVALUE(ID1.SG2@4:ID1.SG2@54,2) to select the second value in its own column. Date Time Of Last Edit: 2023-12-12 04:15:42
|
| [2023-12-12 06:07:58] |
| User273277 - Posts: 58 |
|
I found another post from a while ago with a similar question. There was no final solution posted. I put into column P to remove the zeros: =IF(ID1.SG2@3=0,NOVALUE,ID1.SG2@3) Then I am trying to use index in columns q-z for now to pull the next sequential non-novalue number: =INDEX(P4:P54,NMATCH(2,1,P4:P54,0),1) But the index is not working. I haven't used index here before, and looking for working examples. |
| [2023-12-12 07:18:09] |
| User273277 - Posts: 58 |
|
I got index with nmatch to work with a static value. It looks like nmatch requires a static value paramater and does not accept a cell reference? ie. =INDEX(O4:O54,NMATCH(1,4678.1875,P4:P54,0),1) I need to do something like: =INDEX(O4:O54,NMATCH(1,O4,P4:P54,0),1) I need to enter a cell reference of O4 instead of the static 4678.1875. Is there another way to do this? Date Time Of Last Edit: 2023-12-12 07:20:12
|
To post a message in this thread, you need to log in with your Sierra Chart account: