Support Board
Date/Time: Sat, 28 Dec 2024 07:13:02 +0000
[User Discussion] - SPREADSHEET - Tracking a Cell
View Count: 2778
| [2015-10-16 19:57:45] |
| bekitz3 - Posts: 83 |
|
Any ideas of how to track or continually reference a cell as it moves down the sheet? Ex: I determine at some point that K10 is an important value and want to use it for formulas/calculations etc. What function(s) or strategies would I need to use to do this? I'm aware of all of the available functions here: http://www.sierrachart.com/index.php?page=doc/doc_SpreadsheetFunctions.html#SerialDateTimeValues |
| [2015-10-16 21:00:02] |
| Sawtooth - Posts: 4143 |
|
A persistent variable can be created in a spreadsheet array by using something like this: =IF(YourConditions,AA3,K4) This will bring your example K10's value to K3 to be able to be used in real time, assuming you condition hasn't changed in between. If your conditions change in between, another way to grab a value in 'K10' is to use another formula column, e.g. column P, with this: =IF(YourConditions,1,0) Then use something like this in yet another formula column: =INDEX(K3:K1002,MATCH(1,P3:P1002,0),1) This will grab the value in column K where the most recent '1' occurred in column P. |
| [2015-10-19 15:03:13] |
| bekitz3 - Posts: 83 |
|
Thx tomgilb for the suggestions =IF(YourConditions,AA3,K4)
is this something I would enter into a "non auto-filling" cell such as K1, L1, etcThis will bring your example K10's value to K3 to be able to be used in real time, assuming you condition hasn't changed in between. If your conditions change in between, another way to grab a value in 'K10' is to use another formula column, e.g. column P, with this:
can this techniques go in any cell? Also it seems I remember reading somewhere in another post that you mentioned INDEX/MATCH usage does not work well in New Spreadsheets....which I am using=IF(YourConditions,1,0) Then use something like this in yet another formula column: =INDEX(K3:K1002,MATCH(1,P3:P1002,0),1) This will grab the value in column K where the most recent '1' occurred in column P. ** Further elaboration on my previous example ** Let's say at the current bar I decide I need to use K20 & K10. I create a simple formula in K1: =K20-K10 When a new bar is added to the sheet, I now need K1 to be K21-K11. Etc, etc. |
| [2015-10-19 16:48:36] |
| Sawtooth - Posts: 4143 |
|
=IF(YourConditions,AA3,K4)
This formula would need to be in a K3 cell because it is repeating the previous row's value if it didn't change. =INDEX(K3:K1002,MATCH(1,P3:P1002,0),1)
This formula could be in a non-repeating cell because it will only return the most recent value. If you put in a row 3 cell, it would find the value at every '1' in column P, and repeat it until a new '1'. If used in a row 3 cell, this will essentially give you the same result at the first formula.I misled you that this formula would work if the conditions changed since K10. When used in a non-repeating cell, it will return the value at the most recent '1' in column P. The INDEX/MATCH combination is very inefficient in the New Spreadsheets, when used in a row 3 cell. However, if you use it in a non-repeating cell, the inefficiency is negligible. Without knowing exactly what you are doing, it's difficult to give exact solutions. If you want to find the second most recent value, i.e. K20, here is an example of how to return to row 3 the value at previous irregular occurrences: http://www.sawtoothtrade.com/free-stuff-5.html |
| [2015-12-14 16:24:58] |
| bekitz3 - Posts: 83 |
|
Thx tomgilb. Nice website you have there. I downloaded your example to hopefully gain some better insight into how to use the spreadsheets more effectively. The INDEX/MATCH combination is very inefficient in the New Spreadsheets
I definitely noticed a sluggish behavior overall in Sierra after opening this chartbook/spreadsheet combo. And if I'm not mistaken, only 2 columns (K,L) use this particular combination of 'INDEX/MATCH' & that is enough to slow Sierra down significantly. Have you voiced this observation to support? Any feedback (threads, posts, etc)?Without knowing exactly what you are doing, it's difficult to give exact solutions.
I'm basically just trying to profile a trendline and project that trendline value at the current bar. Using your ZigZag example (2m chart), here's a chart where I've drawn a trendline on the 4th & 6th occurrence:http://www.sierrachart.com/image.php?l=1450109718842.png This results in a trendline value of 35.47 at the current bar. Obviously as more bars populate the chart, these 2 anchor points would advance to 5/7, 6/8, 7/9 occurrences, etc. The basic idea is that at any given moment in time, I could choose 2 points, and have the spreadsheet calculate the slope, stay locked to the 2 anchors, and project the value at the current bar. The slope & projection values aren't a big deal for me....it's just keeping locked to the 2 anchors that seems to be challenging. |
| [2015-12-14 22:16:05] |
| Sawtooth - Posts: 4143 |
|
The INDEX/MATCH combination is very inefficient in the New Spreadsheets
SC Support is aware of this.keeping locked to the 2 anchors that seems to be challenging.
Try this:1) Use J1 & J2 to enter the anchor points as ZZ price values 2) Put this in a spare Formula Column, e.g. M3: =IF(J$1=H3,ROW(H3)-2,M4) This value will increment as new ZZ legs are added. 3) Put this in a spare Formula Column, e.g. N3: =IF(M3=1,Y3,IF(M3=2,X3,IF(M3=3,W3,IF(M3=4,V3,IF(M3=5,U3,IF(M3=6,T3,0)))))) 4) Use a reference to column N to find the ZZ value of the J1 anchor, e.g. in J3: =INDEX(AA3:AA1002,N3,1) 5) Repeat 2-4 for the other anchor in J2. Date Time Of Last Edit: 2015-12-16 14:33:22
|
| [2015-12-16 06:02:15] |
| bekitz3 - Posts: 83 |
|
4) Use column N to find the ZZ value of the J1 anchor:
Is this formula supposed to go in column N? Because column N is used in step #3?=INDEX(AA3:AA1002,N3,1) 1) Use J1 & J2 to enter the anchor points as ZZ price values
I'm wondering how this would work if both values are equal? Perhaps it would be better to reference the occurrence instance/# instead of the raw value?
|
| [2015-12-16 07:03:28] |
| |
|
The INDEX/MATCH combination is very inefficient in the New Spreadsheets, when used in a row 3 cell. However, if you use it in a non-repeating cell, the inefficiency is negligible. Sierra Chart Support - Engineering Level Your definitive source for support. Other responses are from users. Try to keep your questions brief and to the point. Be aware of support policy: https://www.sierrachart.com/index.php?l=PostingInformation.php#GeneralInformation For the most reliable, advanced, and zero cost futures order routing, *change* to the Teton service: Sierra Chart Teton Futures Order Routing Date Time Of Last Edit: 2015-12-16 07:03:56
|
| [2015-12-16 10:11:30] |
| |
|
We have released version 1340. We want to know if it makes a difference regarding this: The INDEX/MATCH combination is very inefficient in the New Spreadsheets, when used in a row 3 cell.
It will definitely make some difference for the better, but we do not know to what extent and whether it would be noticeable or not. The next step is for us to understand the typical case for the use of these functions and then to create a single function which is designed specific for that purpose and to be very efficient. Sierra Chart Support - Engineering Level Your definitive source for support. Other responses are from users. Try to keep your questions brief and to the point. Be aware of support policy: https://www.sierrachart.com/index.php?l=PostingInformation.php#GeneralInformation For the most reliable, advanced, and zero cost futures order routing, *change* to the Teton service: Sierra Chart Teton Futures Order Routing Date Time Of Last Edit: 2015-12-16 10:12:58
|
| [2015-12-16 14:46:32] |
| Sawtooth - Posts: 4143 |
|
Is this formula supposed to go in column N? Because column N is used in step #3?
It goes in any non-repeating cell, e.g. J3.Previous post edited. I'm wondering how this would work if both values are equal?
It doesn't work with equal values without an additional condition. Include an IF statement in your line drawing formula that draws a horizontal line if both are equal. Perhaps it would be better to reference the occurrence instance/# instead of the raw value?
The instance# will increment with each new ZZ leg. I was referencing the ZZ price so that the instance# increments automatically.
|
| [2015-12-16 14:51:01] |
| Sawtooth - Posts: 4143 |
|
We have released version 1340. We want to know if it makes a difference regarding this:
I will test this to see if there is a noticeable improvement, and start a new support thread with the report.
The INDEX/MATCH combination is very inefficient in the New Spreadsheets, when used in a row 3 cell. It will definitely make some difference for the better, but we do not know to what extent and whether it would be noticeable or not. The next step is for us to understand the typical case for the use of these functions and then to create a single function which is designed specific for that purpose and to be very efficient. |
| [2015-12-17 03:21:50] |
| |
|
Wait until we release version 1341. One of the changes we made had a negative performance impact that we did not realize at first. We have now realized that after doing some performance testing.
Sierra Chart Support - Engineering Level Your definitive source for support. Other responses are from users. Try to keep your questions brief and to the point. Be aware of support policy: https://www.sierrachart.com/index.php?l=PostingInformation.php#GeneralInformation For the most reliable, advanced, and zero cost futures order routing, *change* to the Teton service: Sierra Chart Teton Futures Order Routing Date Time Of Last Edit: 2015-12-17 03:22:08
|
| [2016-01-07 02:06:40] |
| |
|
We want to update you on New Spreadsheet performance improvements. Here is the latest information: Basic Examples of How You Use INDEX, MATCH / Spreadsheet Performance Improvements Now Available | Post: 77938 Sierra Chart Support - Engineering Level Your definitive source for support. Other responses are from users. Try to keep your questions brief and to the point. Be aware of support policy: https://www.sierrachart.com/index.php?l=PostingInformation.php#GeneralInformation For the most reliable, advanced, and zero cost futures order routing, *change* to 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: