How do we do it ??
How do we do it ??
(OP)
What i want is to do is
I have a formula with 4 variables. Now depending upon situation, i may need to calculate either one of them with other three known values.
Now what is the easiest way to program this in excel so that at the begining i can select the variable i want to calculate and accordingly the excel will ask me the values of three known variable.
Losty
I have a formula with 4 variables. Now depending upon situation, i may need to calculate either one of them with other three known values.
Now what is the easiest way to program this in excel so that at the begining i can select the variable i want to calculate and accordingly the excel will ask me the values of three known variable.
Losty





RE: How do we do it ??
RE: How do we do it ??
Thanks in advance
Losty
RE: How do we do it ??
Put your formula in cell E1. For a simple example, consider the formula
=2*A1+2*B1+2*C1+2*D1
Enter 1, 2 and 3 into A1, B1 and C1 respectively, your three known variables
(This will result in E1 evaluating to 12)
Suppose the real value of the formula is 14.
Fire up goal seek.
Set cell E1
To value 14
By changing cell D1
D1 being your unknown variable.
Goal seek will tell you that your unknown variable must = 1.
This will work equally well for any of the four variable cells.
RE: How do we do it ??
It is not installed in a defualt install so you need to add it as an add in. Go to tools>>addins and select the "Solver addin". You might need to restart excel for it to appear.
A new menu item will appear in the tools menu called "Solver". The dialouge box is fairly self explanatory. You can use it for your problem or lets say you know two of your 4 variables, but the other 2 are unknown- you can't use goal seek for that, but you can use solver.
Also a topic title like "How do we do it ??" is akin to having "HELLPPP!!" it doesn't actually tell us anything about the topic, and a comment like "Goal seek didnt work for me" doesn't help either, as it does not tell us what you tried and how it didn't work ...
Read the Eng-Tips Site Policies at FAQ731-376
RE: How do we do it ??
Suppose we have 4 variables a, b, c & d, related to each other such that:
a = f1(b,c,d) (e.g. a = b + c + d)
then this can be rearranged for any of the other variables being the unknown:
b = f2(a,c,d) (e.g. b = a - c - d)
c = f3(a,b,d) (e.g. c = a - b - d)
d = f4(a,b,c) (e.g. d = a - b - c)
Now, you need some means of knowing which variable is the unknown. In the following example, I will assume that none of the variables can be equal to zero, but you can easily substitute whatever test is applicable. Now you can write a nested IF statement like this:
=IF(a=0,f1(b,c,d),IF(b=0,f2(a,c,d),IF(c=0,f3(a,b,d),f4(a,b,c))))
Crude, but effective!
Hope this helps.