Formula gives 'wrong' answer.

I show Circular Reference Warning starting EJ 57 though GR 57

Originally Posted by horne

Unfortunately on the computer I'm using I can't see any Circular Reference listed.

Look at the bottom left of the excel window, just below the Main Tab, it will show the cell reference. Note however, that just because it saiys the circ ref error is in GO57, the actual error may lay somewhere else in that row - and where ever it is, it looks like it is affecting the rows down to (at least) row 79

hmm ok well are you sure you uploaded the same file? I agree that the pic you provided does not show any error

Look at the lower left corner of the status bar. In the same place where FDibbins indicated you should find a circular reference warning, the status bar says "calculate". Two possible reasons for this: 1) you have calculate set to manual, and this is Excel's way of telling you that it is waiting for you to tell it when to calculate (so any "results" shown on the spreadsheet may not be correct because it waiting to execute a calculate event) or 2) your spreadsheet has a circular reference and you have iteration enabled.

Since I routinely have iteration enabled for my spreadsheets, I saw something similar when I opened your spreadsheet. I don't know how many iterations you have the maximum set to, but when your spreadsheet calculated for me, I could tell that it was going through many iterations, and, though the iterations were fast, it kind of appeared that there was an endless loop to the calculations. The error appears to be related to the failure of the "circular reference" to converge.

At this point, my hypothesis is that you do indeed have a circular reference and you have iteration enabled. Further debugging will require picking the spreadsheet apart to figure out why there is a circular reference and figure out if it should or should not be there.

MrShorty, thanks for the input and assist. I should have picked up that it said "calc"

the circular reference is only decided by the references and logic in the formulas, not the data/formula result.

Debugging this sort of thing can be difficult (especially in a large spreadsheet like this with a lot of formulas). I'm not aware of any "quick fix" debugging solution for these kind of problems.

One quick observation: with iteration disabled, Excel provides precedent/dependent arrows (see formula auditing on the formulas group). These arrows suggest that the circular reference is in the calculation from column 140 to column 200 (whatever those columns are in A1 notation) and only in rows 57+. I don't know what is different about the calculations performed in row 55 and row 57, but something changes there that seems to create the circular reference. That might be a place to start -- compare row 55 and 57 and see what is different in those calculations. Tedious because it is 60 out of 200 columns, and each formula has multiple terms and parts and some even have multiple levels of nesting. It does not look like it will be easy to debug to me.

Sometimes, I find it easier to delete the whole offending section, think through my logic, and start over.

An approach that I sometimes use is to start deleting cells/columns and see when the error goes away. you can always use "undo" to get them back

G'day MrShorty & FDibbins,

It looks like that this Circular Reference has been 'removed'.

MrShorty, just letting You know the difference between the first 56 Rows and subsequent rows is the first 56 are somewhat using text instead of all formulas.

I took FDibbins advise and started deleting a cell at a time and recalculating then undoing if this didn't 'work' until deleting GR57 moved the CR down a row.
Then I had to replace the formulas between Columns EK & FS that contained the column reference GR & put in HD instead.

This seems to have worked.

I wrote this last post at 3:30am, sorry about spelling & grammar mistakes.

Thank You all for Your help, I'm envious of Your excel knowledge.

Kind Regards.
For Forum 5.zip