Circular Error problem *Anyone want a big challenge?*

[stuckagain22]

I have a circular error that is plaguing me, and I can't see any reason why it would be giving me an error.

I will try to describe the process here, but it will probably be best if you look at the sheet (it is a very complex sheet). Plus I'm uncertain how well I can explain it, without someone else being able to see the sheet.

The sheet can be downloaded at http://www.pathfinderportal.com/ogresheet.html

The below 3 referenced sheets will need to be unhidden.

Sheets involved:

Stats_DATA
Bonuses_DATA
Attributes_DATA

On Stats_DATA, I calculate 6 scores and have called them:
Cell_strmod
Cell_dexmod
Cell_conmod
Cell_wismod
Cell_intmod
Cell_chamod

All 6 of these are calculated the same way, but only Cell_chamod results in a circular error (when certain conditions are met). One of the calculated values is a total named "cell_bonusesDATA_CHA" from my Bonuses_DATA sheet. (explained later)

I know that the error involves an array formula that I have in a large range of cells. Here is a sample. (The array itself works fine).

{=MAX(IF(list_attributesDATA_bonustypestat=IF($B10=0,AI$3&$C10,AI$3&$B10),list_attributesDATA_bonusamount))}

This is copied across a bunch of columns. All of these columns are added up in this row, and are assigned the value: cell_bonusesDATA_CHA

Example: On this row, the array looks for about 12 different prefixes (rows AI) in front of CHA (column B10) in the given named arrays. A sample would be "inherentCHA".

On AttributesDATA I have various bonus values in "list_attributesDATA_bonustypestat" column (such as inherentCHA), and then assign these a numerical value in the "list_attributesDATA_bonusamount" column.

The cells in "list_attributesDATA_bonusamount" that are giving me problems have a reference to cell_chamod. (An example cell on Attributes_DATA, would be cell AC301, which contains the formula:
=cell_chamod

The column before it, has the value "ALLSAVES" (this is the "list_attributesDATA_bonustypestat" column).

When I delete this cell contents, then the circular error goes away. The confusing part is that in no way is ALLSAVES related to the Cell_chamod calculations, so this should not be causing any problems.

I suspect of you are not looking at the sheet at this time, you are very lost (I'm not too great at giving clear descriptions.

To generate the error you will need to do 1 of 2 things:
Firstly, you need to select a "race" on the Stats sheet.
Secondly, you will need to go to the Classes sheet and either select "Cleric" for the first 2 level selections, or to select "Paladin" for the first 3 level selections.

(The 2nd cleric selection activates the value error on Attributes_DATA cell AC179 and AC180 because they have references to cell_chamod. The 3rd paladin selection activates the value error on Attributes_DATA cell AC301.)

Any help that anyone could give with this error would be greatly appreciated. I have over 1 years of work that has grinded to a halt on this.

Thank you.

 

[ZetaNik]

I did take a look at the file.
I did not find the circular error reproduced by following the instructions, so maybe something was left out or I missed a step.
It is possible to get this error even if, as you say, the one does not depend on the other.
The way this could happen is if the result cell is in a range that refers back to itself through the seried of formulas and named formulas.
I did not take the trouble to try to trace the named ranges involved.
This is your creation so you should be able to do this faster than most people.

You can go to Tools, Options, and check iterations, with Maximum iterations =1.
This should at least confirm that the file works.
But this can cause a problem with a major slow-down of your file, as it has to recalculate more than once.

Here are some suggestions for a game project like this:
Save a series of files, by day or week.
If a problem comes up you can go back and trace when it first occured.
Make a VLOOKUP it's own named formula, instead of refering to it twice in a named formula.
Learn more advanced techniques like VBA coding to make the file more efficient.
A complex project can be easier to work with if it is chopped into short segments of code.