Home >> Support Board

Support Board

Date/Time: Sat, 04 May 2024 03:33:14 +0000

How to Post a Message for Support

[User Discussion] - Count number of long entries #CREF circular reference error

View Count: 1264

[2018-03-08 06:44:46] #1 [Go To First Post]
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] #2 [Go To First Post]
Sawtooth - Posts: 3993
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] #3 [Go To First Post]
Sawtooth - Posts: 3993
Post#2 edited

[2018-03-08 15:39:33] #4 [Go To First Post]
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] #5 [Go To First Post]
Sawtooth - Posts: 3993
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] #6 [Go To First Post]
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] #7 [Go To First Post]
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] #8 [Go To First Post]
Sawtooth - Posts: 3993
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] #9 [Go To First Post]
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: