Using Circular Reference iterations in Excel 13

Question

I have a Job Estimate spreadsheet where the cost of lining several different diameter Pipes are all calculated separately according to their know Length, Labor, Equip and Material costs. These varring work costs are totaled in separate columns. 

At times, I have additional work requirements such as precleaning, videoing, traffic control, etc, the cost of which, needs to be distributed to the cost of each Pipe. Currently, to prevent "Circular References," I simple tally the number of columns holding the different pipe cost totals and divide the cost of the new work by this number which distributes the cost equally to each Pipe.

However, to be more accurate, I am trying to distribute the additional costs relative to the current cost of each pipe. To accomplish this, I add the total of all columns and divide the individual columns by the total number to obtain each one's relative percentage of the total and place it in a cell at the bottom of each column.

As one of these additional work costs are added, my formula in each column now attempts to apply the current relative percentage to determind the distribution to be added to each pipe balance. To avoid the "Circular Reference" I turned on the formula option: "Enable iterative Calculation" with Max Iterations: 100, and Max Change: 0.001.

As I applied the formula to each column, all the additional cost numbers changed to the new relative percentages and all worked perfectly and I could vary the numbers and every column would update automatically according to the changes - Just the way I wanted.

The problem occurred when I added a new work Item. All the new formulas turned to: #Value! - I changed the Max Iteration up to the maximum number and incleased the Max Change to 0.0001, but to no avail. Even when I deleted the new charge entry, the formulas cells remain #Value! - The only way I can effect it is to delete and reenter the formula of each and then the numbers reappear. However when a new item is entered the #Value! returns.

Any ideas on how to prevent this from happening? or any Ideas on accomplishing the Relative Distribution another way?

Answer 1

I would change max iterations back to 1.   You could also change the .0001 back to .001.

Doing more iterations takes time and as I recall there wasn't a reason to iterate - you were just trying to prevent circular reference errors.

--

Regards,

Tom Ogilvy

Answer 2

Tom,

Thank you very much. While your aids did not solve my problem your suggestion #2 was very useful in helping me to systematically track down what was causing the problem. To be able to quickly reset the original formulas really saved me a lot of time.

The cause of the problem was an IF statement in the formula up-stream that was used to collected any new input  =IF(H214=0,0,IF(W214=0,"Missing Cat",W214*H214)). As I entered the data for H214, the "Missing Cat" would come on a second or two until I finished entering the data for W214. That would contaminate all my totals for a millisecond which would cause all my formulas requiring "enable iterative calculation" to lock up permanently displaying: "#Value!" in each cell.

Your hint was the only way I could get the cells to unlock and restore to the original formula. I have changed the offending upstream formula and I can now enter new data and everything readjusts accordingly and doesn’t lock up. However, in the past, when I encounter the "#Value!" warning, as soon as I removed the offending data, the cell would clear. It would not in this case. I suspect it has something to do with the "enable iterative calculation" option. Do you have any idea how to prevent the “Lock up” situation. Could it be a simple Option I could turn on or off?

Thanks Again,

Ken

Answer 3

krJarman,

These are treatments of the symptoms:

two easy things you can try:

1)  CTRL + ALT + SHIFT + F9 to recheck all formula dependencies and then recalculate all formulas.

2)  Select all your cells or the cells you want to update.  Press CTRL + H . Find what: = Replace with: =

By replacing the equal sign with an equal sign the formulas are reevaluated by excel  - pretty close to deleting and reentering the formula but is done instantly.

--

Regards,

Tom Ogilvy