Paul Lautman wrote:
Abhi wrote:
Hi

I have a formula with circular reference. When a I put any valur in
any of these reference cells the formaula gets overwritten by the
value. Is there any way that I can put values in any of these cells
without overwriting the formuls to try various permutations. Below is
the example:-

Cell A B C
500 10 2%
Formula +C1*A1 B1/A1

Cell B1 & C1 are having formula with cicular reference. What I am
trying to get is that if I change the B1 value to 50 from 10, then C1
should automatically change to 10% & vice versa. OR If C1 is changed
to 10% B1 should change to 50 so that I can try as many combinationas
possible.

The problem here is that once i put "50" in B1 or "10%" in C1 the
formula gets overwritten with the absolute value.

Would be thank ful if anybody can suggest any solution for this?

Regards
Abhi
I have done a similar thing with Sin/Tan/Cos formulas and Pythagoras
calculations.

I use 2 rows, with answers in the second row (A2, B2, C2) being
dependant on what cells are filled in the first row.

The formulas are all in the second row and can be protected to ensure
that they are not overtyped.
I also format the cells A2, B2, C2 in bold red to indicate that they
are calculated.
Try entering formulas as follows and then enter data in either A1,
B1, C1. In your case you will need to format C1 and C2 as Percentage
with 0 decimal places.

A2=IF(AND(B1<"",C1<""),B1/C1,"")
B2=IF(AND(A1<"",C1<""),C1*A1,"")
C2=IF(AND(A1<"",B1<""),B1/A1,"")

If you really want to have only a single row of cells, then you could
use a macro triggered by the worksheet_change event to update the
values in the other 2 cells.

HTH

Regarding the macro, if the value in A1 is changed (to say 1000) which of B1
or C1 would you rather changed?