not really circular refs


Posted by dave oliver on January 25, 2002 11:43 AM

For example, I want to be able to enter either amount in Pounds or Euro, and to simplify lets say £1 = €1.618034 (or €1 = £0.618034) {doncha just love the golden ratio?}

Cell A1 = B1*1.618034
Cell B1 = A1*0.618034 (or A1/1.618034)

Either I enter currency in Pounds in A1 or in Euro in B1 and the appropriate conversion takes place. How do I avoid circular references before any data is entered?

I've tidled around with ISNUMBER but that doesn't seem to work. There must be lots of similar cases e.g. net + tax = gross and you want to be able to have both formulas in place and overwrite when you enter real data.

Thanks for any pointers, Dave.

Posted by Juan Pablo G. on January 25, 2002 11:55 AM

Go to Tools, Options, Calculate, and CHECK Iterate.

Juan Pablo G.



Posted by Joe Was on January 25, 2002 11:56 AM

Test for a value before you do the calculations!

= IF(A1<=0,"",A1*0.618034)
= IF(B1<=0,"",B1*1.618034)

Try this. JSW