Home >> Support Board

Support Board

Date/Time: Tue, 26 Nov 2024 18:16:40 +0000

How to Post a Message for Support

[Programming Help] - Spreadsheet formula randomly returns 0 - Why?

View Count: 428

[2023-10-08 09:28:05] #1 [Go To First Post]
User61168 - Posts: 403
Hi guys, I am wondering why this formula below would randomly return a value of 0? I am using this spreadsheet formula study to have a Volatility based "Daily Max Profit" value set based on the length of a custom-made channel (similar to an ATR channel), I use this study to triggers my "close all" exit alert condition to exit my open trades and stop trading for the day. =MAX(5,ID59.SG1-ID60.SG1,ID59.SG1[-1]-ID60.SG1[-1]) ID59 and ID60 are both spreadsheet formula which calculate channel High and low respectively - even if channel calculations returns a zero, the MAX() should always return a value of 5. - Even if I replace the formula with a constant value of "5", it randomly returns a value of 0 Edit: I should also mention that when I use this formula directly in my buy/sell Exit Alert condition, it works beautifully confirming there is nothing goofy going on with my study collection. Date Time Of Last Edit: 2023-10-08 09:55:22

[2023-10-09 15:24:07] #2 [Go To First Post]
Sawtooth - Posts: 4120
Is 'Draw Zero Values' set to No?

[2023-10-09 20:22:55] #3 [Go To First Post]
User61168 - Posts: 403
Hi Tom, Draw Zero values is set to "Yes" across all spreadsheet formula studies and draw style is "hidden". I tried running the recalculate study every second and it still sets to zero. Also tried delete and download all data. Happens in replay with tick accuracy as well. p.s. I am assuming its a zero and not a negative or invalid value as there is no way for me to tell what is happening with this formula. I also tried to put a audio sound alert in the formula study to alert when value=0 and there is no sound alert triggering. Just weird!

[2023-10-10 15:12:44] #4 [Go To First Post]
John - SC Support - Posts: 36300
Make sure that this Spreadsheet Formula is lower down in the list of studies than the other Spreadsheet Formulas that it depends on. Otherwise, you could be having a calculation order issue. For the most reliable, advanced, and zero cost futures order routing, use the Teton service: Sierra Chart Teton Futures Order Routing

[2023-10-12 05:45:59] #5 [Go To First Post]
User61168 - Posts: 403
Thanks John. It is in the lowest order from all the studies it is dependent upon. Regardless, I have the "Max(5" to always return 5 if ID59 or ID60 returns a zero value. And mysteriously, if I replace the formula with =5 (a constant value), even then it returns a zero value (randomly) triggering my close-all logic. Using Max formula in the buy/sell exit alert study works perfectly fine so let's not waste our time on this. Something weird could be happening during replay and nested formulas as ID59 and ID60 are dependent on two more spreadsheet formula studies. Date Time Of Last Edit: 2023-10-12 05:56:44

[2023-10-29 06:21:26] #6 [Go To First Post]
User61168 - Posts: 403
Hello John / Tom, Here's another example of funky business with spreadsheet formula study that I am unable to decipher. Alert study ID6 draws point on high/low and two spreadsheet formula studies (ID30 and ID31) draws a continuous line at H[-1] or L[-1] of the signal bar until the next signal bar is generated. Why is it leaving a gap of 2 ticks at High and 1 tick at Low??? Please see attached image. Chart type for ES is price changes per bar = 300. ID30 formula.....=IF(ID6.SG1[-1]=1,H[-1],ID30.SG1[-1]) ID31 formula.....=IF(ID6.SG1[-1]=1,L[-1],ID31.SG1[-1]) Edit: The immediate bar next to the signal bar is accurate but second bar onward, it is creating a gap. sub graph style is dash. And alert study generates signal only at candle close Edit1: The gap is there on a M1 time chart also. Date Time Of Last Edit: 2023-10-29 06:45:17


Screenshot 2023-10-28 231109-spreadsheet formula.png / V - Attached On 2023-10-29 06:19:55 UTC - Size: 26.91 KB - 81 views

[2023-10-29 14:33:13] #7 [Go To First Post]
Sawtooth - Posts: 4120
Why is it leaving a gap of 2 ticks at High and 1 tick at Low??? I'm not able to reproduce this. It works as expected for me. See pic. Try this: Add the Spreadsheet Study study, and look in columns AA and above to see what your ID6 study is outputting.


H L extended.PNG / V - Attached On 2023-10-29 14:33:06 UTC - Size: 47.65 KB - 78 views

[2023-10-29 19:57:35] #8 [Go To First Post]
User61168 - Posts: 403
Thanks Tom. The signal alert always returns a 1 when true (and only on candle close). I am attaching my chart as I have yet to play around with spreadsheet studies. Edit: Weirdly, lines are gaping randomly in the attached screenshot Date Time Of Last Edit: 2023-10-30 00:21:35


Screenshot 2023-10-29 130053-HL lines.png / V - Attached On 2023-10-29 20:02:35 UTC - Size: 19.39 KB - 84 views

[2023-10-29 22:17:09] #9 [Go To First Post]
Sawtooth - Posts: 4120
To get your lines to behave, change the 'Value Format' of ID30 and ID31 to 'Inherited'.

[2023-10-30 00:03:58] #10 [Go To First Post]
User61168 - Posts: 403
wow! Thanks a lot for your assistance Tom. I really appreciate it.

To post a message in this thread, you need to log in with your Sierra Chart account: