Support Board
Date/Time: Thu, 13 Feb 2025 02:56:14 +0000
[Programming Help] - Spreadsheet Help: How to sum instances by day
View Count: 688
| [2020-10-30 20:30:14] |
| Atlatyl - Posts: 19 |
|
I'm importing targets from a Daily chart into a 10 range bar chart, and trying to get whether or not the upside target or downside target are hit *for that day* on the 10 range bar spreadsheet. No problem to do this on the Daily spreadsheet....My problem is I get multiple instances on the 10 range bar column as it chops around the target. How do I either collate those instances into just 1 per each day going back any number of days; or stop collecting the "hit" data after the first hit for the day? Once I have this, I can add indicators/conditions to get stats on which target is likely to be hit for the day. For example.. if I apply a 10SMA to the 10 range bar chart, and I have a 10 range bar close under the SMA as a trigger, and the 10SMA is heading south..., I want to see how many times out of 30 days that hits my lower target. Hope this makes sense? Thanks in advance : ) |
 ____Targets.jpg / V - Attached On 2020-10-30 20:28:52 UTC - Size: 162.53 KB - 253 views Attachment Deleted. |
| [2020-11-15 13:33:37] |
| Sawtooth - Posts: 4173 |
|
The first target hit for the day on a 10 range chart is all you need because that is the one 'for the day'. Any subsequent hits are superfluous 'for the day'. To find how many times out of 30 days a target is hit, you'd need to create some persistent TRUEs that reset each day and then count them. And you'd need enough Number Of Rows to span at least 30 days with a 10 range chart. First, create a persistent TRUE of the trigger, with something like this, in P3: =IF(INT(A3)>INT(A4),0,IF(TriggerCondition,1,P4)) Second, create another persistent TRUE of the target hit, with something like this, in Q3: =IF(INT(A3)>INT(A4),0,IF(AND(P3,TargetHitCondition),1,Q4)) Third, count the number of transitions in column Q from 0 to 1, with something like this, in R3: =IF(AND(Q4=0,Q3),R4+1,R4) Fourth, count the number of days in the 10 range chart, with something like this, in S3: =IF(INT(A3)>INT(A4),S4+1,S4) Fifth, compare R3 and S3, with something like this, in T3: =IF(S3=30,R3,0) Or return a running percentage: =R3/S3*100 |
| [2020-11-21 13:58:37] |
| Atlatyl - Posts: 19 |
|
Thanks, Tom! :-)
|
To post a message in this thread, you need to log in with your Sierra Chart account: