Support Board
Date/Time: Thu, 13 Feb 2025 07:27:47 +0000
Post From: Spreadsheet Help: How to sum instances by day
| [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 |