So I know it is not just me. I mean it cannot simply be only me making that mistake so often.
Frankly, it is even too complex a mistake to make. Beauty in chaos kind of.
I’d understand someone doing that while calculating the mass of the sun, but me? No, it shouldn’t be happening to me trying to split expenses with my room-mates. But it does.
So today we shall be looking at techniques to resolve and avoid this type of mess as often as possible.
Circular referencing is an iterative calculation in excel; in other words, an infinite loop.
When a formula in a cell is capable of altering its own contents directly or indirectly a case of circular referencing arises.
Allow me to demonstrate what we unfortunately only get by chance.
Ok, it was way too easy to make that mistake. No worries let me up things a little.
I get 500$ a month from home for expenses. So I go deposit it in the bank. At the end of each month, the bank gives me a 10% rate (yep I know awesome bank right) on the monthly average in my account. I use my handy dandy excel to predict what I’ll make in year 2016.
My excel displays the circular reference warning and loses it.
When the machine gets confused we are bound to confuse our self with it , cause even something mechanical is confounded how dare we in flesh and blood.
So don’t panic in such a situation. It is not unbreakable.
To think about this clearly if this formula is correct and I want to calculate the amount I will receive in 12 months; this formula should be executed 12 times. So the loop as it is should have 12 iterations.
We don’t necessarily need to resolve the looping situation.
Excel has a nifty way of letting us do that or working around the situation.
We can go about and define after how many iterations should it stop. So 12 yes I know that where should I put it.
It is easier than finding Narnia
Here is the map.
File -> Options -> FormulasSo I wanted to know what it will the sum look like after 12 iterations.
Now since this will give me the final amount I’ll change my captions a bit.
When you hit OK; the formula will run 12 times and the values you see of average and interest earned will be what you will get AFTER 12 months.
So yay my money makes money.
I love how banks work.
And now you love how Excel works.
But be vary whichever calculations you do now will iterate 12 times. So don’t forget to uncheck the box in options and disable looping. Or you can change the number of iterations for the next calculations after obviously saving previous work.
If the excel file is too complex and you cannot figure out which cell is the trouble you can always use this:Go to Formulas option in ribbon and click on circular referencing. The drop down will display all the rogue cells.
First World Problems
Let us look at another problem to understand the power of circular referencing using it for our benefit.
I was doing expenses for my boss and he flies around like a buzzing bee.
This time, he embarked on a conference and merger meetings with various companies in the US.
His private jet took this route: Houston–Los Angeles–Seattle–Minneapolis– Houston.
Starting at Houston the plane started with 1000 gallons of fuel.
The fuel used on each part of the journey 40-0.2(average fuel)
So I wanted to know what the average was on each journey which would be (initial fuel + final fuel)/2
This average would help me calculate the fuel used as 40 – 0.2* (average from above)
Now this quantity had to be deducted at the end of the journey.
So the total fuel left would be calculated and the average would change.
Without circular referencing this problem would be too complicated to solve for the remaining 3 stops.
So I went ahead did the first calculation for it.
I had to ensure this was repeated 4 times. So I went to Narnia.
No I mean I went ahead and redefined iterations allowed for circular reference to 4.And yoo-hoo!!
With the trick, I saved time to another round of coffee at work and so can you.
Circular referencing, in short, is not an error at least for me. Ok, sometimes it is.
But mostly I think of it as an added feature.
What I would spend 20 minutes coding in VBA is an inbuilt feature.
I would have sat designing a very confusing For-Loop or whatever and then run it and re-run until it was error free. Excel is kind enough to do ALL the work and let me have the pay.
So can you!!
Download working files
Download the working file from the links below.
HAPPY CIRCULAR REFERENCING FOLKS!!