Circular Reference

[BradH]

I have a spreadsheet that I use for our annual budget here at work. I have about 30 programs a sheet which describes the charges for each program. I then have a sheet I use which calculates the rates for the program sheets.

A couple of my cost calculations are derived from grouping all programs overall costs and averaging them out to determine the cost for each program. IE: Network costs: To determine the Network costs, we take the overall cost of each program and the program whose overall costs are the highest pays a higher percentage.

Since the Network cost is calculated into the overall program cost, this creates a circular reference.

Now, I figured out how to suppress the Circular Message popup, but there are two problems I am having.

1. My network apparently resets these settings every once in a while, because after changing that setting to allow Circular Reference, I am now getting the error again, about a week later.

2. When making changes, sometimes it causes the circular referenced fields to error out, then I have to go in and remove the formula's on the individual cost sheets and then reenter them.

Any idea's on how to get around the circular reference?

Thanks
Brad

 

[ChrisM]

You can't get around a circular reference, it is an inherent flaw in your logic flow. Just because a number is in the cell doesn't mean it is right. All those intrusive annoying warnings about circular references are there for a reason, don't ignore them or try to work around them.

Without knowing your model it's hard to advise, but I would recommend you calculate your cost percent allocation on everything except network cost. The allocation number is artificial anyways, you have no real world indicator that program x really used some percent of the network costs. Your percents should only be based on the things you know for sure.