software

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 amp; 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% amp; 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 quot;50quot; in B1 or quot;10%quot; in C1 the formula
gets overwritten with the absolute value.

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

Regards
Abhi

Abhi wrote:
gt; Hi
gt;
gt; I have a formula with circular reference. When a I put any valur in
gt; any of these reference cells the formaula gets overwritten by the
gt; value. Is there any way that I can put values in any of these cells
gt; without overwriting the formuls to try various permutations. Below is
gt; the example:-
gt;
gt; Cell A B C
gt; 500 10 2%
gt; Formula C1*A1 B1/A1
gt;
gt; Cell B1 amp; C1 are having formula with cicular reference. What I am
gt; trying to get is that if I change the B1 value to 50 from 10, then C1
gt; should automatically change to 10% amp; vice versa. OR If C1 is changed
gt; to 10% B1 should change to 50 so that I can try as many combinationas
gt; possible.
gt;
gt; The problem here is that once i put quot;50quot; in B1 or quot;10%quot; in C1 the
gt; formula gets overwritten with the absolute value.
gt;
gt; Would be thank ful if anybody can suggest any solution for this?
gt;
gt; Regards
gt; 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(B1lt;gt;quot;quot;,C1lt;gt;quot;quot;),B1/C1,quot;quot;)
B2=IF(AND(A1lt;gt;quot;quot;,C1lt;gt;quot;quot;),C1*A1,quot;quot;)
C2=IF(AND(A1lt;gt;quot;quot;,B1lt;gt;quot;quot;),B1/A1,quot;quot;)

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

Paul Lautman wrote:
gt; Abhi wrote:
gt;gt; Hi
gt;gt;
gt;gt; I have a formula with circular reference. When a I put any valur in
gt;gt; any of these reference cells the formaula gets overwritten by the
gt;gt; value. Is there any way that I can put values in any of these cells
gt;gt; without overwriting the formuls to try various permutations. Below is
gt;gt; the example:-
gt;gt;
gt;gt; Cell A B C
gt;gt; 500 10 2%
gt;gt; Formula C1*A1 B1/A1
gt;gt;
gt;gt; Cell B1 amp; C1 are having formula with cicular reference. What I am
gt;gt; trying to get is that if I change the B1 value to 50 from 10, then C1
gt;gt; should automatically change to 10% amp; vice versa. OR If C1 is changed
gt;gt; to 10% B1 should change to 50 so that I can try as many combinationas
gt;gt; possible.
gt;gt;
gt;gt; The problem here is that once i put quot;50quot; in B1 or quot;10%quot; in C1 the
gt;gt; formula gets overwritten with the absolute value.
gt;gt;
gt;gt; Would be thank ful if anybody can suggest any solution for this?
gt;gt;
gt;gt; Regards
gt;gt; Abhi
gt; I have done a similar thing with Sin/Tan/Cos formulas and Pythagoras
gt; calculations.
gt;
gt; I use 2 rows, with answers in the second row (A2, B2, C2) being
gt; dependant on what cells are filled in the first row.
gt;
gt; The formulas are all in the second row and can be protected to ensure
gt; that they are not overtyped.
gt; I also format the cells A2, B2, C2 in bold red to indicate that they
gt; are calculated.
gt; Try entering formulas as follows and then enter data in either A1,
gt; B1, C1. In your case you will need to format C1 and C2 as Percentage
gt; with 0 decimal places.
gt;
gt; A2=IF(AND(B1lt;gt;quot;quot;,C1lt;gt;quot;quot;),B1/C1,quot;quot;)
gt; B2=IF(AND(A1lt;gt;quot;quot;,C1lt;gt;quot;quot;),C1*A1,quot;quot;)
gt; C2=IF(AND(A1lt;gt;quot;quot;,B1lt;gt;quot;quot;),B1/A1,quot;quot;)
gt;
gt; If you really want to have only a single row of cells, then you could
gt; use a macro triggered by the worksheet_change event to update the
gt; values in the other 2 cells.
gt;
gt; HTH

Regarding the macro, if the value in A1 is changed (to say 1000) which of B1
or C1 would you rather changed?
Paul Lautman wrote:
gt; Paul Lautman wrote:
gt;gt; Abhi wrote:
gt;gt;gt; Hi
gt;gt;gt;
gt;gt;gt; I have a formula with circular reference. When a I put any valur in
gt;gt;gt; any of these reference cells the formaula gets overwritten by the
gt;gt;gt; value. Is there any way that I can put values in any of these cells
gt;gt;gt; without overwriting the formuls to try various permutations. Below
gt;gt;gt; is the example:-
gt;gt;gt;
gt;gt;gt; Cell A B C
gt;gt;gt; 500 10 2%
gt;gt;gt; Formula C1*A1 B1/A1
gt;gt;gt;
gt;gt;gt; Cell B1 amp; C1 are having formula with cicular reference. What I am
gt;gt;gt; trying to get is that if I change the B1 value to 50 from 10, then
gt;gt;gt; C1 should automatically change to 10% amp; vice versa. OR If C1 is
gt;gt;gt; changed to 10% B1 should change to 50 so that I can try as many
gt;gt;gt; combinationas possible.
gt;gt;gt;
gt;gt;gt; The problem here is that once i put quot;50quot; in B1 or quot;10%quot; in C1 the
gt;gt;gt; formula gets overwritten with the absolute value.
gt;gt;gt;
gt;gt;gt; Would be thank ful if anybody can suggest any solution for this?
gt;gt;gt;
gt;gt;gt; Regards
gt;gt;gt; Abhi
gt;gt; I have done a similar thing with Sin/Tan/Cos formulas and Pythagoras
gt;gt; calculations.
gt;gt;
gt;gt; I use 2 rows, with answers in the second row (A2, B2, C2) being
gt;gt; dependant on what cells are filled in the first row.
gt;gt;
gt;gt; The formulas are all in the second row and can be protected to ensure
gt;gt; that they are not overtyped.
gt;gt; I also format the cells A2, B2, C2 in bold red to indicate that they
gt;gt; are calculated.
gt;gt; Try entering formulas as follows and then enter data in either A1,
gt;gt; B1, C1. In your case you will need to format C1 and C2 as Percentage
gt;gt; with 0 decimal places.
gt;gt;
gt;gt; A2=IF(AND(B1lt;gt;quot;quot;,C1lt;gt;quot;quot;),B1/C1,quot;quot;)
gt;gt; B2=IF(AND(A1lt;gt;quot;quot;,C1lt;gt;quot;quot;),C1*A1,quot;quot;)
gt;gt; C2=IF(AND(A1lt;gt;quot;quot;,B1lt;gt;quot;quot;),B1/A1,quot;quot;)
gt;gt;
gt;gt; If you really want to have only a single row of cells, then you could
gt;gt; use a macro triggered by the worksheet_change event to update the
gt;gt; values in the other 2 cells.
gt;gt;
gt;gt; HTH
gt;
gt; Regarding the macro, if the value in A1 is changed (to say 1000)
gt; which of B1 or C1 would you rather changed?

If you want B1 to take precedence then this VBA code should do it:
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
On Error GoTo ee
Select Case Target.Address
Case quot;$A$1quot;, quot;$C$1quot;
[b1].Value = [c1].Value * [a1].Value
Case quot;$B$1quot;
[c1].Value = [b1].Value / [a1].Value
End Select
ee:
Application.EnableEvents = True

End Sub
Alternatively if C1 is to take precedence then:
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
On Error GoTo ee
Select Case Target.Address
Case quot;$C$1quot;
[b1].Value = [c1].Value * [a1].Value
Case quot;$A$1quot;, quot;$B$1quot;
[c1].Value = [b1].Value / [a1].Value
End Select
ee:
Application.EnableEvents = True

End Sub

Enjoy
You will need some VBA macro code (Worksheet_Change). Do you mind?

HTH
--
AP

quot;Abhiquot; gt; a écrit dans le message de
...
gt; Hi
gt;
gt; I have a formula with circular reference. When a I put any valur in any of
gt; these reference cells the formaula gets overwritten by the value. Is there
gt; any way that I can put values in any of these cells without overwriting
the
gt; formuls to try various permutations. Below is the example:-
gt;
gt; Cell A B C
gt; 500 10 2%
gt; Formula C1*A1 B1/A1
gt;
gt; Cell B1 amp; C1 are having formula with cicular reference. What I am trying
to
gt; get is that if I change the B1 value to 50 from 10, then C1 should
gt; automatically change to 10% amp; vice versa. OR If C1 is changed to 10% B1
gt; should change to 50 so that I can try as many combinationas possible.
gt;
gt; The problem here is that once i put quot;50quot; in B1 or quot;10%quot; in C1 the formula
gt; gets overwritten with the absolute value.
gt;
gt; Would be thank ful if anybody can suggest any solution for this?
gt;
gt; Regards
gt; Abhi