Im working on a Excel sheet where I have to connect two formulas as follows:

A1 and B1 have together a value of 20%. if A1 is 12%, B1 should
automatically shows 8% and vice versa. I tried it with A1: =0.2-B1; B1:
=0.2-A1 but its not working properly. Could someone please help me?

Thanks in advance!

In cell A1 enter 20% and in cell B1 enter formula

=0.2-A1

--
Jacob


"Chris" wrote:

Im working on a Excel sheet where I have to connect two formulas as follows:

A1 and B1 have together a value of 20%. if A1 is 12%, B1 should
automatically shows 8% and vice versa. I tried it with A1: =0.2-B1; B1:
=0.2-A1 but its not working properly. Could someone please help me?

Thanks in advance!

This would work in one direction. is it also possible for both directions so
i could change percentages in A1 or in B1?

Thanks,
Chris

"Jacob Skaria" wrote:

In cell A1 enter 20% and in cell B1 enter formula

=0.2-A1

--
Jacob


"Chris" wrote:

Im working on a Excel sheet where I have to connect two formulas as follows:

A1 and B1 have together a value of 20%. if A1 is 12%, B1 should
automatically shows 8% and vice versa. I tried it with A1: =0.2-B1; B1:
=0.2-A1 but its not working properly. Could someone please help me?

Thanks in advance!

Hi,
if you enter the % in both cells you will overwrite the formulas you need to
have the formula in one cell,


"Chris" wrote:

This would work in one direction. is it also possible for both directions so
i could change percentages in A1 or in B1?

Thanks,
Chris

"Jacob Skaria" wrote:

In cell A1 enter 20% and in cell B1 enter formula

=0.2-A1

--
Jacob


"Chris" wrote:

Im working on a Excel sheet where I have to connect two formulas as follows:

A1 and B1 have together a value of 20%. if A1 is 12%, B1 should
automatically shows 8% and vice versa. I tried it with A1: =0.2-B1; B1:
=0.2-A1 but its not working properly. Could someone please help me?

Thanks in advance!

Hi Chris

You can't do that as it creates a circular reference, as each cells
value refers back to itself.
Equally, you cannot enter a value into a cell, and have a formula
residing there.

If you put values say 16 in A1 and 4 in B1
and then put in A2
=0.2*A1/(A1+B1)
and in B2
=0.2*B1/(A1+B1)

the values in A2 and B2 will adjust so that they always sum to 20%
--
Regards
Roger Govier

Chris wrote:
Im working on a Excel sheet where I have to connect two formulas as follows:

A1 and B1 have together a value of 20%. if A1 is 12%, B1 should
automatically shows 8% and vice versa. I tried it with A1: =0.2-B1; B1:
=0.2-A1 but its not working properly. Could someone please help me?

Thanks in advance!

ok, so it's not possible...thanks anyway
Chris

"Eduardo" wrote:

Hi,
if you enter the % in both cells you will overwrite the formulas you need to
have the formula in one cell,


"Chris" wrote:

This would work in one direction. is it also possible for both directions so
i could change percentages in A1 or in B1?

Thanks,
Chris

"Jacob Skaria" wrote:

In cell A1 enter 20% and in cell B1 enter formula

=0.2-A1

--
Jacob


"Chris" wrote:

Im working on a Excel sheet where I have to connect two formulas as follows:

A1 and B1 have together a value of 20%. if A1 is 12%, B1 should
automatically shows 8% and vice versa. I tried it with A1: =0.2-B1; B1:
=0.2-A1 but its not working properly. Could someone please help me?

Thanks in advance!

Not possible with just XL...if you want to use VB, you could try this.
Right click on sheet tab, view code, paste the following in:

'=============
Private Sub Worksheet_Change(ByVal Target As Range)
'If not changing A1 or B1, or if you change a group of cells
'then do nothing
If Intersect(Target, Range("A1:B1")) Is Nothing Or _
Target.Count 1 Then Exit Sub

Application.EnableEvents = False
If Target.Address = Range("A1").Address Then
'Formula that controls B1
Range("B1").Value = 0.2 - Range("A1").Value
Else
'Formula that controls A1
Range("A1").Value = 0.2 - Range("B1").Value
End If
Application.EnableEvents = True
End Sub
'=============

Back in XL, you can then change A1/B1, and the other cell will change
accordingly.

--
Best Regards,

Luke M
"Chris" wrote in message
...
ok, so it's not possible...thanks anyway
Chris

"Eduardo" wrote:

Hi,
if you enter the % in both cells you will overwrite the formulas you need
to
have the formula in one cell,


"Chris" wrote:

This would work in one direction. is it also possible for both
directions so
i could change percentages in A1 or in B1?

Thanks,
Chris

"Jacob Skaria" wrote:

In cell A1 enter 20% and in cell B1 enter formula

=0.2-A1

--
Jacob


"Chris" wrote:

I'm working on a Excel sheet where I have to connect two formulas
as follows:

A1 and B1 have together a value of 20%. if A1 is 12%, B1 should
automatically shows 8% and vice versa. I tried it with A1: =0.2-B1;
B1:
=0.2-A1 but its not working properly. Could someone please help me?

Thanks in advance!