Support Board
Support Board
Search Board
Control Panel
View My Posts / Threads
Direct MessagesDate/Time: Tue, 30 Apr 2024 00:49:57 +0000
[Programming Help] - Spreadsheet formula incorrectly evaluating true?
View Count: 560
| [2020-07-16 13:48:37] |
| j4ytr4der_ - Posts: 918 |
|
Not sure what's going on here but maybe someone else can point me to it. I'm trying to build in various "error detectors" in a spreadsheet, to get me flat if certain things ever happen (which shouldn't happen, so these are "emergency breaks" of sorts). One of them is that I should never have a naked position, that is a position without working orders around it. Sc doesn't provide (as far as I can tell anyway) a direct way to see if there are entry or exit orders in specific in place, so the best I can do is just assume that an open position, with no working orders, is definitely bad and I better get flat before something blows up on me. Unfortunately there's some sort of problem with this. It's fairly simple really... this formula is in S8 and is one of a few conditions that inform J29 (Flatten & Cancel), and they all state lock so that if any of them trigger I can see which one(s) did it. Here is my formula: =IF(AND(J3 = 0, J84 = FALSE, J8 <> 0), TRUE, S8)
If the Working Orders Quantity is zero, and Working Orders do not exist, and there is currently a position on, this evaluates true and I'm flattened. And it does work, but a little too well which is the problem. It is evaluating true for a split second on initial order entries, so I get filled and am instantly flattened. This doesn't happen on all order entries, not even on a majority. But it will happen several times per day easily. It looks like there is a split second during order entry, that the initial working orders no longer exist, and new ones are created, and during this instant of time the position is flattened. It happens so fast that I can't see it nor record it (I have video of this happening and all you see is a position fill and then get flat). Anyone have any ideas on why this happens? Or is there maybe a better way to detect what I'm looking to detect? |
| [2020-07-16 15:04:04] |
| Sawtooth - Posts: 3985 |
|
Column S is a Formula Column, so you can't enter a formula in S8. All formulas in Formula Columns must be entered in row 3. Also, when referencing column J in a Formula Column formula, you must use absolute references to column J cells. Your observation is correct that it is evaluating TRUE for a split second, so using 'Locking a TRUE state' can't do what you want. J6 goes non-zero when you have a naked position. Try this in J29: =J6<>0 Date Time Of Last Edit: 2020-07-16 15:10:30
|
| [2020-07-16 16:02:57] |
| j4ytr4der_ - Posts: 918 |
|
That's odd, why would I not be able to use S8? I have only 2 rows of data being used so it seems that it should work fine? And in fact has been and continues to work fine...? I'll try J6, thanks! |
| [2020-07-16 16:11:16] |
| j4ytr4der_ - Posts: 918 |
|
FYI, this appears to work perfectly: =IF(AND(J84 = FALSE, J6 <> 0), TRUE, S8)
Oh and I forgot to mention, I am actually using absolute cell references, I just removed them here for readability. |
To post a message in this thread, you need to log in with your Sierra Chart account: