Support Board
Date/Time: Tue, 04 Mar 2025 12:56:19 +0000
Spreadsheet programming question #CREF using Locking the State of a One Time Condition
View Count: 816
| [2021-12-12 22:17:06] |
| User183724 - Posts: 191 |
|
Does anyone have a working example of a "Locking the State of a One Time Condition" ? I've tried a thousand combinations and get #CREF every time. I have a 5 minute chart with a BAR NUMBER STUDY. I'm attempting to allow a cell to be updated only once per bar#. If this isnt possible, i'd be ok with some formula using a version of bar time or FRACTIME ...?? where the new bar time allows the cell to update but havent had any luck getting that to work either. basically, i want the cell to only update once per bar. Locking the State of a One Time Condition Spreadsheet Studies Special Tasks |
| [2021-12-12 23:11:10] |
| Sawtooth - Posts: 4179 |
|
Here's another example: https://www.sawtoothtrade.com/example-9.html |
| [2021-12-13 00:04:10] |
| User183724 - Posts: 191 |
|
thanks.. i looked at that...got #cref with it too
|
| [2021-12-13 00:51:33] |
| Sawtooth - Posts: 4179 |
|
The example does not create a #CREF. Try this exercise: 1. Put this formula in cell H3: =AND(OR(H3=TRUE,H4>1),H5) 2. Put a 1 in both H4 and H5. H3 will be 0, and ready to be locked. 3. Put a 2 in H4. H3 will go to 1, and be locked. 4. Put a 1 in H4. H3 remains a 1 because it is locked. 5. Put a 0 in H5. H3 will go to 0, and be unlocked, but not ready to be locked again. 6. Put a 1 in H5. H3 remains a 0, but is ready to be locked again. I admit it can be tricky to get this to work automatically, especially the unlocking and rearming portion. Steps 3 & 4 can easily be done automatically because this is the event that you want to lock TRUE. Steps 5 & 6 can be tricky to get to work automatically because you need an event that changes state, followed by a change of state in the opposite direction. IOW, step 5 can be 0, and step 6 can be 1, or vice versa if H5=0. |
| [2021-12-13 01:29:42] |
| User183724 - Posts: 191 |
|
i've been trying all that for several days... I've pretty much give up on it for now...thanx anyways.
|
| [2021-12-15 05:46:32] |
| j4ytr4der_ - Posts: 946 |
|
In case it's helpful to anyone... here's another (commented) approach... =IF(REM="How to set & lock a state","",
IF( AND( IF(REM="If state #1 is true...","",""), J5 <> 0, ), AND( IF(REM="then set this cell (O3) to true","",""), O3 = 1, ), AND( IF(REM="Otherwise keep the value at this cell's value...","",""), O3 ) ) ) My platform's shut down at the moment so I didn't test this, but it's just a slightly modified copy & paste out of a project I'm working on right now so... reasonably certain it's lacking in huge errors. Reasonably. =D Obviously it can be cleaned up & condensed without all the comments, just including them to help folks understand the logic. |
To post a message in this thread, you need to log in with your Sierra Chart account: