Support Board
Date/Time: Tue, 04 Mar 2025 21:13:30 +0000
OFFSET Function in Spreadsheet
View Count: 1101
| [2021-12-31 15:03:45] |
| User265682 - Posts: 17 |
|
Hello, I'm trying to get the '=SUM(OFFSET' function to work in a spreadsheet. I've been able to get it to work using the Rows / Columns section, but not the Height / Width ones. Every time I try this I'm getting a NUM error. Please can you advise? Thank you in advance. Date Time Of Last Edit: 2021-12-31 15:21:13
|
| [2021-12-31 16:29:39] |
| Sawtooth - Posts: 4179 |
|
The Height/Width values must be positive. It works the same as this: https://support.microsoft.com/en-us/office/offset-function-c8de19ae-dd79-4b9b-a14e-b4d906d11b66 |
| [2021-12-31 16:47:42] |
| User265682 - Posts: 17 |
|
Hi Tom, Thanks for your reply. I have tested this, but am getting the same issue. The formula I am using is; =SUM(OFFSET(BC3,0,0,0,Z3)) I have to reference another cell for the width as it's a dynamic number that updates dependent on the POC location. This number is usually positive although, at times it's a 0 so I'll need to find a way around that at a later stage as this will result in an error. I have also tried swapping the Z3 cell in the formula for a number, but I get the same issue. For reference what I'm trying to do is get an average delta figure for above and below the POC so need to count the price levels from the high / low to the POC. If you know of a study that does this already please let me know haha..! Thanks, Edd Date Time Of Last Edit: 2021-12-31 17:33:20
|
| [2021-12-31 18:22:29] |
| John - SC Support - Posts: 38369 |
|
The Width and Length values have to be non-zero (same as Excel). So if you wanted to sum the value in cell BC3, you would enter =SUM(OFFSET(BC3, 0, 0, 1, 1)) Therefore, it looks like you would just need to change your formula to the following (assuming that a value of 1 in Z3 really means a total of 2 columns): =SUM(OFFSET(BC3, 0, 0, 1, Z3 + 1)) This will also eliminate the issue with Z3 being 0, since it adds 1 to the value. For the most reliable, advanced, and zero cost futures order routing, use the Teton service: Sierra Chart Teton Futures Order Routing |
| [2021-12-31 22:20:54] |
| User265682 - Posts: 17 |
|
Thanks John, that tweak has worked and I'm getting values now. What I'm trying to do is get an average delta figure from above and below the POC of each bar - I'm unsure if adding the +1 will work as it might then include extra price levels - but I'll test this out. Is there a better way of achieving the above? For instance I know there is a Volume above POC in Number Bars calculated 2 - although nothing for the Delta and neither have an 'average' option either. Thanks again for your help. |
| [2022-01-03 20:30:30] |
| John - SC Support - Posts: 38369 |
|
What you are doing is the only way we can think to do what you want. There definitely is not a simple study that exists that does this calculation.
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: