Support Board
Date/Time: Sun, 19 Jan 2025 00:22:12 +0000
[User Discussion] - Count number of long entries #CREF circular reference error
View Count: 1464
| [2018-03-08 06:44:46] |
| User701453 - Posts: 176 |
|
Im trying to count the number of long entries, as I do not want to have more then two trades per valid signal. Until there is a short entry taken. I am able to count the long entries using the P column and below code: =IF(AND($K$3=1,$J$8=0),($P$3 + 1), IF( OR( $M$3,$H$35 <> 0), 0 ,IF(ISBLANK($P$3),0 ,$P$3)))
However I get a #CREF circular reference error when I try to reference the $P$3 value in the K3 Buy Entry cell. =AND($J$8=0,ID38.SG1@3<>0,$P$3<2)
Any assistance is needed to get this to work correctly. |
| [2018-03-08 14:33:45] |
| Sawtooth - Posts: 4154 |
|
Try this: P3: =IF(OR(M3,$H$35<>0),0,IF(AND(K3,$J$8=0),P4+1,P4)) K3: =AND($J$8=0,ID38.SG1@3<>0,P4<2) In this situation: -You don't need to use absolute references of Formula Column cells. -You need to reference row 4 to avoid a #CREF error. -The zeroing IF needs to precede the incrementing IF. Date Time Of Last Edit: 2018-03-08 16:05:39
|
| [2018-03-08 15:02:34] |
| Sawtooth - Posts: 4154 |
|
Post#2 edited
|
| [2018-03-08 15:39:33] |
| User701453 - Posts: 176 |
|
tomgilb Thanks for your assistance . I`m receiving a SYNTAX error with the P3 cell code. =IF(OR(M3,$H$35<>0),0,IF(AND(K3,$J$8=0),P4+1),P4)) Date Time Of Last Edit: 2018-03-08 15:59:32
|
| [2018-03-08 16:06:14] |
| Sawtooth - Posts: 4154 |
|
Sorry, P3 should be this: =IF(OR(M3,$H$35<>0),0,IF(AND(K3,$J$8=0),P4+1,P4)) |
| [2018-03-08 17:31:49] |
| User701453 - Posts: 176 |
|
Thanks tomgilb. Works perfect. I had been banging my head on solving this for over a week. You did it in a few minutes. Sierra support staff, please add tomgilb wisdom to the spreadsheet example page. Spreadsheet Example Formulas and Usage |
| [2018-03-08 18:51:38] |
| User701453 - Posts: 176 |
|
Still getting a #CREF! error when referencing P4<2 K3: =AND($J$8=0,ID38.SG1@3<>0,P4<2) Im using spreadsheets of SC version 1705. Maybe that makes a difference with the #CREF! error issue. Date Time Of Last Edit: 2018-03-08 18:52:12
|
| [2018-03-08 19:41:52] |
| Sawtooth - Posts: 4154 |
|
It was working, but now it's not? It must be from something else. Did you change the formula in P3? |
| [2018-03-09 02:40:50] |
| User701453 - Posts: 176 |
|
Its working was in regard to the syntax error in P3 being removed. After running the following code in P3. =IF(OR(M3,$H$35<>0),0,IF(AND(K3,$J$8=0),P4+1,P4)) The code doesn't count the first trade entry, so i will try to figure out why. I may just adjust the max limit number and move on to the next task. I did a full reboot and the #CREF! error issue hasn't shown back up yet. So hopefully it wont. Thanks again for all your assistance. Much appreciated. Date Time Of Last Edit: 2018-03-09 11:19:36
|
To post a message in this thread, you need to log in with your Sierra Chart account: