Support Board
Date/Time: Sun, 24 Nov 2024 05:50:09 +0000
S/Sheet Study - Formula returns #inval! error
View Count: 1796
| [2013-09-22 16:17:00] |
| User37170 - Posts: 29 |
|
Still struggling to get any sense out of my s/sheet study attempts. Using the simple MA crossover study cannot get Toms formula to work: I tested it on a standalone s/sheet - works perfectly But not in SC s/sheet. =COUNTIF(INDEX(X3:X1002,MATCH(INT(A3),Y3:Y1002,-1),1):INDEX(X3:X1002,MATCH(INT(A3)-1,Y3:Y1002,-1),1),TRUE) Also this formula stays the same regardless of which cell I paste it into No relative referencing. Odd! Any suggestions pleeze. |
| [2013-09-23 17:15:18] |
| |
|
The meaning of the error codes is documented here: http://www.sierrachart.com/index.php?l=doc/doc_Worksheets.html#WorksheetErrors 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 |
| [2013-09-23 19:30:37] |
| User37170 - Posts: 29 |
|
I solved the problem by reverting back to the CLR version of SC. S/Sheet studies don't work as well in the non CLR version. So error codes won't help. |
| [2013-09-24 01:11:17] |
| |
|
We were going to ask if you are using the non-CLR version because after we posted the response we realized the #inval! error was not listed. So we will have to look into this problem with the non-CLR version. This error code is specific to the non-CLR version. 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: 2013-09-24 01:11:46
|
| [2013-09-24 21:16:45] |
| |
|
We have looked into this. 1. The issue with this formula boils down to INDEX(...):INDEX(...). The colon in this context is the syntax error. This is not supported in the new spreadsheets. The other colons in the formula are valid because they are part of cell range references (X3:X1002), but a colon currently has no meaning between two values. 2. Also, the function COUNTIF was never added due to the complexity of supporting criteria text. (Same with AVERAGEIF, MAXIF, MINIF, SUMIF.) You should be able to accomplish what that formula is doing by breaking the formula down into separate spreadsheet columns using the more basic functions. Now that 60 formula columns are supported with the spreadsheet studies, there should be no problem with this. It also may make it easier to understand the logic. 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: 2013-09-24 21:17:40
|
| [2013-09-24 22:33:55] |
| |
|
2. As an example, the formula =COUNTIF(AA3:AA12,">50") can be implemented in the non-CLR version as follows: In one of the spreadsheet formula columns enter this formula =IF(AA3>50,1,0). Assuming the prior formula was entered in formula column X, then enter =SUM(X3:X12) in another formula column. The result of this last formula will be the same as =COUNTIF(AA3:AA12,">50").
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: 2013-09-24 22:34:37
|
| [2013-09-25 01:20:29] |
| Sawtooth - Posts: 4118 |
|
1. The issue with this formula boils down to INDEX(...):INDEX(...). The colon in this context is the syntax error. This is not supported in the new spreadsheets. The other colons in the formula are valid because they are part of cell range references (X3:X1002), but a colon currently has no meaning between two values.
The colon in this context is not a syntax error. It is the range of the COUNTIF function of the formula in post #1.2. Also, the function COUNTIF was never added due to the complexity of supporting criteria text. (Same with AVERAGEIF, MAXIF, MINIF, SUMIF.)
It is not good news that some existing Excel functions will not be available. Very disappointing to hear that. COUNTIF and SUMIF have been around for years. I was actually hoping for additional functions, like MAXIF and especially MINIF. It's not that the same thing can't be accomplished within 60 columns, but that the functions make it easier to get to the desired result.You should be able to accomplish what that formula is doing by breaking the formula down into separate spreadsheet columns using the more basic functions. Now that 60 formula columns are supported with the spreadsheet studies, there should be no problem with this. It also may make it easier to understand the logic. Date Time Of Last Edit: 2013-09-25 01:51:03
|
| [2013-09-25 02:41:51] |
| |
|
Longer-term, we will look at adding support for functions like COUNTIF. It is important we get the basic spreadsheet functionality implemented and released, that it works reliably and is very fast. There is more work we need to do, but we are getting close to completion. We will have to look at the INDEX function being used in this way. 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: