Sum penalty amount for values that fall outside of a specified range

[BAvant]

I need to identify the total penalty incurred within a 30 day period using 30 minute interval data. The 30 minute interval data values are required to be between 39-42 and if they are not a $200 penalty is incurred. The part I'm having trouble with is that if a penalty is incurred there is a 12 hour rectification period so I would need the formula to skip the next 12 hours (24 rows) then begin searching for values outside of the range again and continue adding the $200 penalty as necessary. Also, the 12 hour rectification period only accumulates during the working hours of 7am-4pm, so if a value falls outside of 39-42 at 11pm, the 12 hours would begin at 7am and end at 10am the following day.

 

[awoohaw]

I need to identify the total penalty incurred within a 30 day period using 30 minute interval data. The 30 minute interval data values are required to be between 39-42 and if they are not a $200 penalty is incurred. The part I'm having trouble with is that if a penalty is incurred there is a 12 hour rectification period so I would need the formula to skip the next 12 hours (24 rows) then begin searching for values outside of the range again and continue adding the $200 penalty as necessary. Also, the 12 hour rectification period only accumulates during the working hours of 7am-4pm, so if a value falls outside of 39-42 at 11pm, the 12 hours would begin at 7am and end at 10am the following day.

It may help if you provide an example of what the data would look like and the desired result in a number of scenarios. You can use the xl2bb add (link below) to paste mini workbook. If you can't use that, then please just copy and paste the data as a table. Please do not just post an image... the forum would have to completely type all the scenarios which takes time and could have typos that are not easily recognized.

Thanks in advance.

 

[felixstraube]

Is your data already one value every 30 min? Like this:

Datetime	Values
01/07/2024 00:00	42
01/07/2024 00:30	39
01/07/2024 01:00	40
01/07/2024 01:30	42
01/07/2024 02:00	39
01/07/2024 02:30	41
01/07/2024 03:00	42
01/07/2024 03:30	40
01/07/2024 04:00	42
01/07/2024 04:30	40
01/07/2024 05:00	40
01/07/2024 05:30	39
01/07/2024 06:00	41
01/07/2024 06:30	40

Or do you have it with a higher frequency and you need to check in intervals of 30 min? That is your data is something like this:

Datetime	Values
01/07/2024 00:00	39
01/07/2024 00:03	42
01/07/2024 00:06	41
01/07/2024 00:09	41
01/07/2024 00:12	42
01/07/2024 00:15	40
01/07/2024 00:18	40
01/07/2024 00:21	40
01/07/2024 00:24	40
01/07/2024 00:27	41
01/07/2024 00:30	40
01/07/2024 00:33	40
01/07/2024 00:36	40
01/07/2024 00:39	39

 

[BAvant]

Timestamp	Values	Penalties
01-Jun-24 12:00:00 AM PDT	51.6​
01-Jun-24 12:30:00 AM PDT	52.2​
01-Jun-24 1:00:00 AM PDT	52.7​
01-Jun-24 1:30:00 AM PDT	53.1​
01-Jun-24 2:00:00 AM PDT	53.4​
01-Jun-24 2:30:00 AM PDT	53.7​
01-Jun-24 3:00:00 AM PDT	54​
01-Jun-24 3:30:00 AM PDT	54.3​
01-Jun-24 4:00:00 AM PDT	54.7​

 

[BAvant]

Current formula for C2:C25 is =IF(AND(OR(B17<39,B17>42),COUNTIF(C$2:Cx-1,200)=0),200,"")
Current formula for C26 and on is =IF(AND(OR(B18<39,B18>42),COUNTIF(C3:C25,200)=0),200,"")

The only problem with these formulas is that they do not address the issue of the 12 hour rectification period only accumulating during working hours.

 

[felixstraube]

Ok here is my first try:

First formula in D3, shows all the variables used and calculated for you to check if the result is as expected. If you have any questions just ask.
And in the formula in L3 you get only the result (I understand you need, which is the total penalty count).
The example data i used has more data than just 1 value every 30 min, but the formula should work fine with just 1 value every 30 min.

Here is the working file for you to download: Penalties.zip

Try it and let me know.

Book1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1	Days of data ->	15
2	Datetime	Values		30 min Interval	Outside allowed band	Working Hour	Rectification period (dif from 0)	Rec period hours count	Rec period starts	Total penalties		Only penalties count
3	01/07/2024 00:00	41		01/07/2024 00:00	0	0	0	0	0	11		11
4	01/07/2024 00:03	40		01/07/2024 00:30	0	0	0	0	0
5	01/07/2024 00:06	39		01/07/2024 01:00	1	0	0.1	0	0
6	01/07/2024 00:09	42		01/07/2024 01:30	1	0	0.1	0	0
7	01/07/2024 00:12	40		01/07/2024 02:00	0	0	0.1	0	0
8	01/07/2024 00:15	41		01/07/2024 02:30	0	0	0.1	0	0
9	01/07/2024 00:18	41		01/07/2024 03:00	0	0	0.1	0	0
10	01/07/2024 00:21	41		01/07/2024 03:30	1	0	0.1	0	0
11	01/07/2024 00:24	39		01/07/2024 04:00	0	0	0.1	0	0
12	01/07/2024 00:27	42		01/07/2024 04:30	1	0	0.1	0	0
13	01/07/2024 00:30	40		01/07/2024 05:00	1	0	0.1	0	0
14	01/07/2024 00:33	39		01/07/2024 05:30	0	0	0.1	0	0
15	01/07/2024 00:36	41		01/07/2024 06:00	0	0	0.1	0	0
16	01/07/2024 00:39	40		01/07/2024 06:30	0	0	0.1	0	0
17	01/07/2024 00:42	42		01/07/2024 07:00	0	1	1.1	1	1
18	01/07/2024 00:45	39		01/07/2024 07:30	0	1	2.1	2	0
19	01/07/2024 00:48	42		01/07/2024 08:00	0	1	3.1	3	0
20	01/07/2024 00:51	41		01/07/2024 08:30	0	1	4.1	4	0
21	01/07/2024 00:54	39		01/07/2024 09:00	1	1	5.1	5	0
22	01/07/2024 00:57	39		01/07/2024 09:30	0	1	6.1	6	0
23	01/07/2024 01:00	38		01/07/2024 10:00	0	1	7.1	7	0
24	01/07/2024 01:03	41		01/07/2024 10:30	0	1	8.1	8	0
25	01/07/2024 01:06	40		01/07/2024 11:00	0	1	9.1	9	0
26	01/07/2024 01:09	42		01/07/2024 11:30	0	1	10.1	10	0
27	01/07/2024 01:12	40		01/07/2024 12:00	0	1	11.1	11	0
Penalty (3)

Cell Formulas
Range	Formula
A3:A7202	A3	=SEQUENCE(B1*24*2*10,, DATE(2024,7,1), TIME(0,3,0))
D3:J722	D3	=LET(dt, $A$3:$A$14402, t, FILTER(dt, dt<>""), v, FILTER($B$3:$B$14402, dt<>""), date, INT(t), time, MOD(t, 1), i, date + ROUNDDOWN(ROUND(time*24*2,1),0)/24/2, ui, UNIQUE(i), i_time, VALUE(TEXT(MOD(ui, 1), "hh:mm")), workingHour, (i_time>=VALUE("07:00"))*(i_time<VALUE("16:00")), d, BYROW(ui, LAMBDA(x, LET(d, FILTER(v, i=x), ((MAX(d)>42) + (MIN(d)<39))>0)))*1, wh_d, BYROW(HSTACK(workingHour, d), LAMBDA(x, TEXTJOIN(";",, x))), rp, DROP(REDUCE(0, wh_d, LAMBDA(arr,y, LET(wh, VALUE(TEXTBEFORE(y,";")), a, TAKE(arr,-1), b, VALUE(TEXTAFTER(y,";")), c, IF((MOD(a, 24)=0)*b, IF(wh, 1, 0.1), IF(a*(a<24), IF(wh, a+1, a), 0)), VSTACK(arr, c)))), 1), rph, ROUND(rp,0), rps, (rph=1)*1, IFERROR(HSTACK(ui, d, workingHour, rp, rph, rps, SUM(rps)), "") )
L3	L3	=LET(dt, $A$3:$A$14402, t, FILTER(dt, dt<>""), v, FILTER($B$3:$B$14402, dt<>""), date, INT(t), time, MOD(t, 1), i, date + ROUNDDOWN(ROUND(time*24*2,1),0)/24/2, ui, UNIQUE(i), i_time, VALUE(TEXT(MOD(ui, 1), "hh:mm")), workingHour, (i_time>=VALUE("07:00"))*(i_time<VALUE("16:00")), d, BYROW(ui, LAMBDA(x, LET(d, FILTER(v, i=x), ((MAX(d)>42) + (MIN(d)<39))>0)))*1, wh_d, BYROW(HSTACK(workingHour, d), LAMBDA(x, TEXTJOIN(";",, x))), rp, DROP(REDUCE(0, wh_d, LAMBDA(arr,y, LET(wh, VALUE(TEXTBEFORE(y,";")), a, TAKE(arr,-1), b, VALUE(TEXTAFTER(y,";")), c, IF((MOD(a, 24)=0)*b, IF(wh, 1, 0.1), IF(a*(a<24), IF(wh, a+1, a), 0)), VSTACK(arr, c)))), 1), SUM((ROUND(rp,0)=1)*1))
Dynamic array formulas.

 

[felixstraube]

One thing that i didn't consider but im just thinking about is if working hours only acumulate on working days? That is from mon-fri? Or what days specifically?.
Let me know, that is an easy fix.