is there a good spreadsheet I can get for free (and preferably download) that allows circular references?

Question

For example, if I wanted to make a 'timer' that goes from 0 to lets say 100 in the square A1, I would put "=if(A1>99,0,A1+1)" , but that is an impossibility in most spreadsheets because they do not allow ANY circularity in their formulas

And don't get me wrong, I understand the need for that bug, because it's very easy to accidentally create an uncontrolled, exponentially increasing reference, but there are plenty of cases in which the circularity is no problem at all (like above or in a counting formula, like "A2=if(A1=n,A2+1,A2)", especially if there is an emergency stop formula, like the one that I put in almost all of my codes "=if(E1=1,0, ... )")

garbo7441 · Answer

You can use circular references in Excel, although it is not free.  To do that simply requires a change to an option setting.

For Excel 2003, one would go to Tools > Options > Calculation.

Then click the 'Iteration', check box and set the Maximum iterations to 1 and the Maximum Change to 1.

Then, if you put this formula in cell A2:

=IF(A1=1,A2+1,A2)

Every time you entered a '1' in cell A1, cell A2 will increment by +1.

=-=-=-=-=-=-=

For Excel 2010, the setting is in File > Options > Formulas > Calculation Options

Trending News

is there a good spreadsheet I can get for free (and preferably download) that allows circular references?

1 Answer