If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
connecting formulas
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! |
#2
|
|||
|
|||
connecting formulas
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! |
#3
|
|||
|
|||
connecting formulas
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! |
#4
|
|||
|
|||
connecting formulas
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! |
#5
|
|||
|
|||
connecting formulas
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: 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! |
#6
|
|||
|
|||
connecting formulas
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! |
#7
|
|||
|
|||
connecting formulas
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! |
Thread Tools | |
Display Modes | |
|
|