Getting around circular reference in Google Spreadsheet

Question

I have a google docs spreadsheet with two columns: A and B. Values of B are just values from A in a different format, and I have a formula in the B column that does the conversion. Sometimes I do not have the values in A format but I have them in B format. I would like to automatically get the values in A format in the A column by adding the formula that does the reverse conversion in the A column. This, of course, generates a circular reference. Is there a way to get around it?

Answer 1

From this week, Google Sheets has announced support for exactly this feature. You can now limit the number of iterations for circular references in the spreadsheets settings :-)

Answer 2

On the top menu of a google spreadsheet do the following:

File > Spreadsheet settings

Choose the "Calculation" tab, and change "Iterative calculation" to ON.

Enjoy :D.

PD: I know that this post is too old, but just some days ago I needed a solution to this, and I couldn´t find any.

Answer 3

In excel you can set it to allow circular dependencies and limit the number of iterations they run (usually 1 is the desired result).

I've looked and nothing like that exists in sheets.

Answer 4

I know that this post is pretty old, but I saw it while looking to see the applications of a thing.

In sheets, you can use importrange to reference the same sheet and call the desired range. For instance, you can put a formula in B1 that is =A1+1 and in A1 use the formula =importrange(<THIS SHEET ID>,"B1")+1.

You may need to initially put the formula in A2 and then move it up to A1, but it should work.

Doing something like this essentially makes a second counter, which is neat I guess?