How do I store a battery's state of charge past midnight?

[SolEnergy]

I have an average battery state of charge (lets say a value between 0 and -10) calculated at five-minute intervals, which works great up until midnight. However, because this an average and not repeated for every day of the year, I don't know how to make it loop without being a circular reference. I can upload a sample file if the mini sheet below is too difficult to understand.

How do I make cell C5 (12:00AM) reference the value in C23 cell (11:55PM)? For example, if at 11:55PM the battery has a state of charge of 4, it should loop and use the value for 12:00AM, then 12:05AM uses the value from 12:00AM, etc. I would prefer the solution to be a formula so that this file can be passed to colleagues without needing to know VBA or Power Query.

Example.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	Existing - no loop					How it should look - looped					How it actually looks - looped						Max battery state	Min battery state
2																	10	0
3	Jan	Energy Consumption	Battery State			Jan	Energy Consumption	Looping Battery State			Jan	Energy Consumption	Looping Battery State
4	Midnight		0			Midnight					Midnight
5	12:00:00 AM	1	0			12:00:00 AM	5	-5			12:00:00 AM	5	0
6	12:05:00 AM	1	0			12:05:00 AM	5	0			12:05:00 AM	5	0
7	12:10:00 AM	1	0			12:10:00 AM	5	0			12:10:00 AM	5	0
8	12:15:00 AM	1	0			12:15:00 AM	5	0			12:15:00 AM	5	0
9	12:20:00 AM	1	0			12:20:00 AM	5	0			12:20:00 AM	5	0
10	12:25:00 AM	1	0			12:25:00 AM	5	0			12:25:00 AM	5	0
11	12:30:00 AM	1	0			12:30:00 AM	5	0			12:30:00 AM	5	0
12	Sunrise					Sunrise					Sunrise		0
13	1:00:00 PM	-5	-5			1:00:00 PM	-5	-5			1:00:00 PM	-5	0
14	1:05:00 PM	-5	-10			1:05:00 PM	-5	-10			1:05:00 PM	-5	0
15	1:10:00 PM	-5	-10			1:10:00 PM	-5	-10			1:10:00 PM	-5	0
16	1:15:00 PM	-5	-10			1:15:00 PM	-5	-10			1:15:00 PM	-5	0
17	1:20:00 PM	-5	-10			1:20:00 PM	-5	-10			1:20:00 PM	-5	0
18	1:25:00 PM	-5	-10			1:25:00 PM	-5	-10			1:25:00 PM	-5	0
19	Sunset					Sunset					Sunset		0
20	11:40:00 PM	1	-9			11:40:00 PM	2	-9			11:40:00 PM	2	0
21	11:45:00 PM	1	-8			11:45:00 PM	2	-8			11:45:00 PM	2	0
22	11:50:00 PM	1	-7			11:50:00 PM	2	-7			11:50:00 PM	2	0
23	11:55:00 PM	1	-6			11:55:00 PM	2	-6			11:55:00 PM	2	0
24	Midnight					Midnight					Midnight
Sheet1

Cell Formulas
Range	Formula
C4	C4	=R2
C5:C11,M6:M23,C21:C23,C13:C18	C5	=MIN(MAX(C4+B5,$Q$2*-1),$R$2)
H5	H5	=MIN(MAX(C23+B5,$Q$2*-1),$R$2)
H6:H11,H21:H23,H14:H18	H6	=MIN(MAX(C5+B6,$Q$2*-1),$R$2)
H13,H20	H13	=MIN(MAX(C11+B13,$Q$2*-1),$R$2)
C20	C20	=MIN(MAX(C18+B20,$Q$2*-1),$R$2)
M5	M5	=MIN(MAX(M23+L5,$Q$2*-1),$R$2)

Cheers

 

[NdNoviceHlp]

Hi *SolEnergy and Welcome to the Board! Maybe C4 should be =IF(R23<>0,R23,R3) HTH. Dave

 

[SolEnergy]

Hi *SolEnergy and Welcome to the Board! Maybe C4 should be =IF(R23<>0,R23,R3) HTH. Dave

Hi Dave

Thanks for lending a hand. I think this is what you mean? A good idea, but we still have the issue where every subsequent row relies on what the row above is, which makes this circular. I'm thinking maybe I need to use Power Query and process every line for the whole year and then average it in a Pivot Table?

 

[NdNoviceHlp]

Whoops... I was actually thinking that C4 should be =If(C23 <> 0, C23, R2) I messed that up and I don't think that I fully understand what you're objective is? What actually exists... Columns A:H? I'm not real sure of the role of R2 and/or Q2 ....starting state values for testing? What about the rest of the day's time increments? Maybe add 11:59:59 to A24 and create B24 & C24 values, then you're H5 value would approximate your C24 (1 sec difference). Seems like a recursion error may be created with successful formula looping. Perhaps with a bit more explanation, others may have a nifty solution. Dave