Need a formula to 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.

 

[Eric W]

Welcome to the MrExcel forum!

Interesting question! I tried to come up with a single cell formula, but came up short. Instead I added a helper column, then summed the column:

Book6
	A	B	C	D
1	Time	Value	Penalty	Total Penalties
2	6/1/24 0:00	42		2
3	6/1/24 0:30	39
4	6/1/24 1:00	11	1
5	6/1/24 1:30	11
6	6/1/24 2:00	12
7	6/1/24 2:30	13
8	6/1/24 3:00	14
9	6/1/24 3:30	15
10	6/1/24 4:00	40
11	6/1/24 4:30	41
12	6/1/24 5:00	16
13	6/1/24 5:30	40
14	6/1/24 6:00	42
15	6/1/24 6:30	41
16	6/1/24 7:00	42
17	6/1/24 7:30	45
18	6/1/24 8:00	39
19	6/1/24 8:30	42
20	6/1/24 9:00	45
21	6/1/24 9:30	39
22	6/1/24 10:00	40
23	6/1/24 10:30	42
24	6/1/24 11:00	40
25	6/1/24 11:30	41
26	6/1/24 12:00	41
27	6/1/24 12:30	39
28	6/1/24 13:00	39
29	6/1/24 13:30	41
30	6/1/24 14:00	25	1
31	6/1/24 14:30	42
32	6/1/24 15:00	42
33	6/1/24 15:30	40
34	6/1/24 16:00	41
35	6/1/24 16:30	41
36	6/1/24 17:00	42
Sheet1

Cell Formulas
Range	Formula
D2	D2	=SUM(C2:C1441)
C2:C25	C2	=IF(AND(OR(B2<39,B2>42),COUNTIF(C$1:C1,1)=0),1,"")
C26:C36	C26	=IF(AND(OR(B26<39,B26>42),COUNTIF(C2:C25,1)=0),1,"")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B:B	Expression	=OR(B1>42,B1<39)	text	NO

Someone might be able to come up with a single cell formula, but it will likely require functions I don't have (I'm on Excel 2021).