Support Board
Date/Time: Fri, 09 May 2025 21:47:04 +0000
Spreadsheets; formula works in excel but not in Sierra Charts
View Count: 1585
| [2015-12-04 00:59:48] |
| SSGill - Posts: 36 |
|
Hi; I am trying to do the following in SC, but get a #SYNTAX! error. =min(e3:indirect("e" &h4)) but the same formula works in excel. Can anyone help? |
| [2015-12-04 02:11:09] |
| Sawtooth - Posts: 4214 |
|
The text operator & (ampersand) is not supported in the New Spreadsheets. Try this: =min(e3:offset(e3,$h$4,0)) |
| [2015-12-04 02:40:25] |
| SSGill - Posts: 36 |
|
Tomgilb; How about if I wanted the concatenate a letter plus the contents of H4 (which would be a number)? So the formula would be =min(e3: "e" then the contents of H4) |
| [2015-12-04 03:13:01] |
| |
|
There is or we will ensure there is a way to do this in New Spreadsheets: I am trying to do the following in SC, but get a #SYNTAX! error. =min(e3:indirect("e" &h4)) Just give us a couple of days to get back to you on this. We will explain this in the documentation and/or add the necessary functionality. 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 |
| [2015-12-04 03:22:36] |
| Sawtooth - Posts: 4214 |
|
Not sure. I've tried various combinations with INDIRECT using TEXT and CONCATENATE and VALUE to achieve what you are trying to do. I was unsuccessful. I was able to use OFFSET in my application as a workaround. My suggestion will allow you to find the MIN of a dynamic range, which seems is what you want to do. Here are the New Spreadsheet functions: http://www.sierrachart.com/index.php?page=doc/doc_SpreadsheetFunctions.html#AvailableFunctions This is fewer than in Excel, and not all of them work as they do in Excel, which means the New Spreadsheets are not really Excel compatible. |
| [2015-12-04 03:53:47] |
| |
|
We have verified that this works and is the equivalent to the formula given in post #1: min(E3: INDIRECT(CONCATENATE ("E", TEXT(H5)))) 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 |
| [2015-12-04 10:10:29] |
| SSGill - Posts: 36 |
|
Thanks! verified it works. One additional formula I am having difficulty with is; =INDEX(ap3:ap1000, MATCH(FALSE, ISBLANK(ap3:ap1000), 0)) I am trying to return the first non-blank value; I referred to the SC documentation, and also used isnull() and isempty. All return a #NA. This does work in excel if I press the Ctrl, Shift Enter after typing my formula. |
| [2015-12-04 13:21:28] |
| Sawtooth - Posts: 4214 |
|
CSE formulas are not supported in spreadsheet studies. You'll need to use an additional Formula Column, e.g. column O, to mark all blanks, e.g with a 1, that your FALSE produced, then use something like this: =INDEX(AP3:AP1002,MATCH(1,O3:O1002,0)) This will result in the most recent occurrence. Date Time Of Last Edit: 2015-12-04 13:38:29
|
To post a message in this thread, you need to log in with your Sierra Chart account: