By default, iterative calculations are turned off in Microsoft® Excel®. In this tip we explain how you can locate and remove a circular reference. Note: Download the sample workbook to practice this exercise. Applies to: Microsoft Excel 2007, 2010 and 2013. The screenshot below will be used for this example.

Locating Circular References A circular reference occurs when a formula directly or indirectly refers to its own cell. This causes the formula to use its result in the calculation, which can create errors.

When a workbook contains a circular reference, Excel cannot automatically perform calculations. You can use error checking in Excel to locate circular references in a formula, and then remove them. If you leave them in, Excel calculates each cell involved in the circular reference by using the results of the previous iteration. An iteration is a repeated recalculation until a specific numeric condition is met.

By default, Excel stops calculating after 100 iterations or after all values in the circular reference change by less than 0.001 between iterations, unless you change the Excel option. Locate a Circular Reference • Click the Formulas tab. • Click the Error Checking button arrow, point to Circular References, and then click the first cell listed in the submenu. • Review the formula cell. • If you cannot figure out if the cell is the cause of the circular reference, click the next cell in the Circular References submenu, if available. • Continue to review and correct the circular reference until the status bar no longer displays the word “Circular.”.

I use a spread sheet (currently excel) to calculate sailing results but have been trying without success to re do in numbers. At the end of each race a number is moved into an array and the previous number moves along, I have used a formula ' if (a1=?, If (b1?, X1,y1),y1)' This has the effect of moving the number in x1 to y1 if the if statement is met but leaves the number in tact if not.

However in numbers I get a 'This formula can't reference it's own cell' error but I have not found a way around this issue. Now Lion won't support Rosetta I need to get this to work in numbers (as my old excel version won't run in Lion) and with a bit of luck be able to use in the iPad version also.

In effect there are multiple boats and multiple race results each that the last five are stored and averaged to calculate a new handicap. With the above set up I have been able (for about 10 years +) to easily keep the last five results everything else works fine just not the array. I assume the question marks in your formula mean something else, that they are not actual question marks.

But I don't know what they refer to. I assume your formula is in cell X1 or Y1. If so, that is the circular reference. If it is A1 or B1, that too would be a circular formula. Do you have automatic recalculation turned off in Excel? Excel can work with circular references if it automatic recalculation is turned off, otherwise it should have the same problem you are seeing in Numbers.

If you have it turned on in Excel and it is working that way, something must be different between the two spreadsheets. If you can walk us through this spreadsheet and how you use it, it would be helpful. All we know right now is that after a race a number magically moves into an array (how did it get there, was it entered into a cell, which cell?) and all the other numbers move down (though it would appear from your formula that they move across. What is in B1, what is in A1? Is this the only formula in the entire spreadsheet or is there a column or row of these formulas? Definitely a circular reference here, which Numbers will not accept.

Numbers cells may contain either an entered value, or a value determined by a formula in the cell. The formula cannot reference the value that is already in the cell. The formula in V9 generates the error message by attempting to return it's own value if either of the stated conditions is false.

Take a step back from the formula itself and look at what determines the values in the formula. What determines whether T9 is 0 or 4?

(Are these the only possible values for T9?). All of the values in your example are in row 9.

I'm assuming that each row is the record for a single boat. Unless there are no factors external to the record of that single boat, I think we need a bigger picture in order to suggest how to accomplish this in Numbers. Regards, Barry.

How to turn off automatic updates microsoft office 2016 mac. Speaking of Windows, uninstalling Office on it is a simple matter of opening Programs & Features from the Control Panel and removing the application there.

Barry, Correct each row represents a different boat and so are all the same as the one above. Yes T9 is just a switch currently activated by a macro I run after each race to calculate the handicap for the next race. My issue is if I take away the last V9 in that cell then the result is either False or 0 if either T9 is not 4 or R9 has no result (ie that boat did not finish and therefore no new handicap). I use the double cell set up V and W so the result only flows into the next cell on 0 and then onto the next after the following race when T becomes a 4. If I remove that set up the numbers just flow through the entire array. I need the double check T & R so 0 handicaps are not picked up. Looks like open office is the go unless you can come up with a clever trick.