Daily Battery Cycle Formulas Give Circular Reference Errors

I'm creating a list to estimate energy utilisation for a given battery size on a daily cycle of charging from solar cells. I'm looking for tips to create this without causing circular reference errors.

I have a list with 24 hrs for one typical weekday and one typical weekend per month. Available energy/drain is given in column G. The idea is that given energy or load, the battery will charge or drain, and I can track expected usage in column K. That way I can find the minimum size needed for a give load/charge profile.

The problem is that if the the stored charge at 1am is dependent on what the charge is at midnight. To reflect this I used an If statement to skip down 24 cells if the time is zero. IE for H2

Formula:

Please Login or Register  to view this content.

I also added a statement to force the stored energy to 0 at 5am so that I get a fresh start each day before the sun comes up.

Formula:

Please Login or Register  to view this content.

but this means that I'm making a circular set of formulas for each day. My if statements should prevent any errors, but I still have the circular reference issue. I would also rather not have to turn on iterative calculations.

Does anyone have a suggestion of how to accomplish this?

Many thanks.

Originally Posted by JeteMc

It may be important for us to understand is why the stored charge at 1am is dependent on what the charge is at [the next] midnight [as opposed to the previous]. I am again guessing that to be the case as the IF formula skips "down" 23 rows [formula in H3 references I26].
Let us know if you have any questions.

To explain the formula, rows 3:26 represent a typical weekend (Is Week Day = 0) in January (month = 1). Rows 27:50 are the typical weekday in Jan. As I’m running a typical day set for each month (week vs week end) I only need to have 24 sets in about 600 rows instead of running a much longer list of 24hr x 365days requiring 8800 rows (I have a lot of other work to do with this data, and this saves a lot of calculation time).

As such each day is an independent set, typical for that month, and the charge stored at 1am is dependant on the charge generated in the SAME typical day. For that reason the reference for stored charge at 1 am is 23 rows down where midnight for the current set is, NOT in the previous day.

Originally Posted by JeteMc

When I open the file and enable editing, I don't get a message about circular references as I expected from reading the narrative in post #1. Therefore I am guessing that you are able to tell there is a problem by looking at the numbers.
Let us know if you have any questions.

I don't have any problems with numbers I'm getting here, which do make sense to. I was purely concerned about getting the circ ref error. I didn't want to pass my sheet on colleagues to use if it was giving out errors.

Oddly, I now don't get a circular reference error on the example file either. I did when I posted it.
The columns the example file I provided are an exact copy of the set up I had in a MUCH larger file that still does give me circular ref error on those same formulas. But now the circular ref error disappears on sections of the worksheet If I click into the formula bar on on those cells. I'm a bit confused, but my problem seems to be evaporating (I don't like evaporative problems, they leave me worried that they will come back)

Follow up.
The problem did come back. I still get a 'circular reference' error each time I open the file. That said, the formulas do give correct results.

Originally Posted by JeteMc

So there appears to be no doubt that a circular reference occurs and, according to post #5, is purposeful.

Yes. I knew that this was a circular reference when I built it. The point of the post is looking for better method that will allow me to calculate the charge stored with out using such a a circle. but keeping a similar row format.

I'm happy to insert any number of helper columns, but still haven't thought of a way around this method.

Thanks protonLeah,

Of course. Just set the formulas in VBA, and then I still have formulas, and the formula recorded in each cell doesn't have to cover all cases (in a circle). Simple. I should have thought of that. I guess once I got focused on the formulas, forget the easy way.

Marking as solved