It's best practice when modeling to add a circular switch to your model to handle any formulas that contain circular references. This means that if you want to edit parts of the model, you can turn off iterations and you can turn off the switch, and that will allow all the other cells without circular references to calculate as normal. You should always check your status bar though to see if you've done this correctly. So if you look at your status bar and you have the circular references message, this means that iterations are off. But there is a circular reference present in any open file on your machine. You may also see the situation where you have the circular references message and a cell reference. What this means is, again, that iterations are switched off, but there is circular references present on your machine and that also includes the sheet that you are currently working on, the active sheet in the file you are working on. If you look at the bottom of the screen and you see the calculate message in the status bar, this appears when you have a circular reference present and iterations are switched on. Let's create an accidental circular reference. So here I have two numbers. I'm going to add them both together. And I'm going to include the answer in the calculation, in the sum formula. So I'm adding the answer in there as well. When I press enter, I get an error message as you would expect. The reason I get an error message is because I've switched off iteration. Now, you access the iteration menu by going ALT + FT, okay? So that's file options. And in here under formulas you'll see that we've got the enable iterative calculation setting unchecked. If we check this box and hit enter, it now allows the calculation to calculate an answer. And you can see it's given us a value of 300. And it's 300 because as we showed you on the previous page back in those settings, you can see that the maximum number of iterations is set to 100. So what our calculation is doing is taking one plus two, plus the answer 100 times, okay? Now, this is obviously undesirable in a model. We don't want this thing this to be allowed to happen accidentally and it can get worse than this. 'Cause every time we type something into another cell, every single time we press enter then it recalculates again. So effectively it would be adding 300 each time. So obviously that sort of calculation is undesirable. There are certain formulas, however, in our models that sometimes will have circular references within them. And obviously we need to be able to deal with these so we don't receive an error message. So here's what we can do. We can use a circular switch to handle them. So what I'm going to do, I'm gonna edit my formula now. I'm going to say equals if open brackets.

My circular switch cell equals one. Then I'm going to out calculate the answer by adding D4 through D6. However, if it's not set to one of the switches turned off, I'm going to return a zero and press enter, okay. So at the moment it's showing 300 and that's because the switch is on. If I turn the switch off however and press, set that to zero, that now gives me a zero in here. So this is a way of controlling the circularity, okay? Now, let's just have a look at a couple of other things. So when I turn the switch on again, if we look at the status bar on the bottom left-hand side, you'll see that this says calculate. If I switch iteration off, it no longer says calculate. It says circular references and it gives me a cell reference as well, D6, which basically means that the circular reference is on this particular sheet. So when we're editing a model it's best practice to have any switches set to zero so that the output is zero. And also so that the, have the iteration setting switched off. And what that means is that if we accidentally create a circularity again, we're going to end up with an error message straight away. So we know to fix it straight away. If we have the iteration setting switched on then we could create an accidental circularity and that could mean to a big error that we'd have to find later on.