Support Board
Support Board
Search Board
Control Panel
View My Posts / Threads
Direct MessagesDate/Time: Sat, 04 May 2024 02:28:47 +0000
Spreadsheets, Substitute Row # with Reference
View Count: 615
| [2017-03-14 00:26:30] |
| User136839 - Posts: 20 |
|
The cell reference format is [Study ID].[Subgraph number]@[Row number]. Is there any way to substitute the Row number with a reference (e.g. J30 or ($J$30+2)). It doesn't seem to work for me. If not it may be a good functionality item for adding? Thanks |
| [2017-03-14 01:33:14] |
| Sawtooth - Posts: 3993 |
|
I use the OFFSET function to create a dynamic range based on the Number of Rows: =MAX(ID0.SG5@3:OFFSET(ID0.SG5@3,$J$30-1,0)) or =MAX(F3:OFFSET(F3,$J$30-1,0)) This will give you the max volume in all of column F, because the offset is 999 from row 3. You can use OFFSET in formulas like this: =AVERAGE(ID0.SG4@3:OFFSET(ID0.SG4@3,10,0)) or =AVERAGE(E3:OFFSET(E3,10,0)) This will give you a simple moving average of the Last price, with a length of 10. |
| [2017-03-14 12:41:10] |
| User136839 - Posts: 20 |
|
Hi tomgilb, you are a lifesaver :), well, you've certainly made my life a lot easier. I'm trying to reference the number of rows in J30 so that whenever I change the row number in the chart studies settings I don't have to go into the spreadsheet and change all the calculation based on the total number of rows. This works perfect. Thanks so much TomA |
To post a message in this thread, you need to log in with your Sierra Chart account: