Home >> Support Board

Support Board

Date/Time: Mon, 21 Apr 2025 18:19:39 +0000

How to Post a Message for Support

Spreadsheet Formula

View Count: 242

[2025-02-22 04:59:33] #1 [Go To First Post]
Ticks - Posts: 206
Is there a character limit in the study Formula input? The input won't accept the full formula =IF (AND(BARTIME>= TIME(11, 01, 0), BARTIME <= TIME(13, 0, 0),ID18.SG1, IF (BARTIME>= TIME(17, 00, 0), ID18.SG1,ID18.SG1))

[2025-02-22 15:38:18] #2 [Go To First Post]
cmet - Posts: 690
Pretty sure the limit is over 1000 characters. More people can help you if you explain what you're trying to do along with your formula.

[2025-02-22 16:11:59] #3 [Go To First Post]
User431178 - Posts: 654
=IF(AND(BARTIME>=TIME(11, 01,0),BARTIME<=TIME(13,0,0),ID18.SG1,IF(BARTIME>=TIME(17,00,0),ID18.SG1,ID18.SG1)) You do not have the parentheses correct, count them, one set is not closed. Try this instead, noting that the AND condition is now closed. =IF(AND(BARTIME>=TIME(11, 01,0),BARTIME<=TIME(13,0,0)),ID18.SG1,IF(BARTIME>=TIME(17,00,0),ID18.SG1,ID18.SG1)) Date Time Of Last Edit: 2025-02-22 16:12:16

[2025-02-22 19:11:01] #4 [Go To First Post]
Ticks - Posts: 206
More people can help you if you explain what you're trying to do along with your formula. I am trying to lock in and maintain the same indicator value contained in ID18.SG1 obtained between 11 and 1300 until 17:00.

[2025-02-22 19:15:06] #5 [Go To First Post]
Ticks - Posts: 206
=IF(AND(BARTIME>=TIME(11, 01,0),BARTIME<=TIME(13,0,0)),ID18.SG1,IF(BARTIME>=TIME(17,00,0),ID18.SG1,ID18.SG1)) Thanks. It's weird that the study won't let anyone enter the full formula if it's not in the correct format.

[2025-02-22 19:31:32] #6 [Go To First Post]
User431178 - Posts: 654
My mistake, the formula error is nothing to do with being able to enter the formula or not. Were you able to copy what I wrote above into the study? With the original formula, is it copied in from a text editor? Maybe there is whitespace from the editor screwing it up?

[2025-02-22 19:47:38] #7 [Go To First Post]
Ticks - Posts: 206
@User431178 Your formula worked when I copied it. I was referring to my original formula not being accepted by the study because it wasn't formatted correctly.

[2025-02-23 06:15:53] #8 [Go To First Post]
Ticks - Posts: 206
=IF(AND(BARTIME>=TIME(11, 01,0),BARTIME<=TIME(13,0,0)),ID18.SG1,IF(BARTIME>=TIME(17,00,0),ID18.SG1,ID18.SG1)) I am still trying to lock in and maintain the same indicator value contained in ID18.SG1 obtained between 11 and 1300 until 17:00, but the value still changes after 1300.

[2025-02-23 12:52:13] #9 [Go To First Post]
User431178 - Posts: 654
I was referring to my original formula not being accepted by the study because it wasn't formatted correctly. It is the line break in the originally posted formula that is the problem (preventing the input), that is what I was getting at with the whitespace comment. I am still trying to lock in and maintain the same indicator value contained in ID18.SG1 obtained between 11 and 1300 until 17:00, but the value still changes after 1300. When you say obtained bewteen 1100 and 1300, do you mean the last value of the indicator from that period, I would assume so? The formula you are using won't work because somewhere it needs a reference to itself at the preceding bar to persist the value. =IF(AND(BARTIME>=TIME(11, 01,0),BARTIME<=TIME(13,0,0)),ID18.SG1,IF(BARTIME>=TIME(17,00,0),ID18.SG1,ID18.SG1)) Here are three examples: 1. ID2 is the spreadsheet formula, this has been inserted as the final output of the if blocks. =IF(AND(BARTIME>=TIME(11,0,0),BARTIME<=TIME(13,0,0)),ID18.SG1,IF(BARTIME>=TIME(17,00,0),ID18.SG1,ID2.SG1[-1])) 2. Same as above, but here I have also inlucded a condition so that study acts as a passthrough before 1100 as well. =IF(AND(BARTIME>=TIME(11,0,0),BARTIME<=TIME(13,0,0)),ID18.SG1,IF(OR(BARTIME<TIME(11,0,0),BARTIME>=TIME(17,0,0)),ID18.SG1,ID2.SG1[-1])) 3. As above but simplified, takes the last value that occurred before 1300 and persists until 1700, otherwise acts as passthrough. =IF(OR(BARTIME<=TIME(13,0,0),BARTIME>=TIME(17,0,0)),ID18.SG1,ID2.SG1[-1]) Example chartbook attached.


ValuePersistTimeWindow.Cht - Attached On 2025-02-23 12:51:08 UTC - Size: 5.23 KB - 64 views

[2025-02-23 21:14:06] #10 [Go To First Post]
Ticks - Posts: 206
@User431178 Thank you for all the work with the formula code and the workbook. Works perfect. Sierra Team should add your code example to the Spreadsheet Example Formulas and Usage that I had been trying to use.

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