How do we do it ?? 1

[Losty]

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

 

[MintJulep]

look at Excel's help for "goal seek".

 

[Losty]

Goal seek didnt work for me ....any other idea or may be en elaboration on how to use goal seek to achieve what i wanted to :-s

Thanks in advance

Losty

 

[MintJulep]

Say cells A1, B1, C1 and D1 represent four variables.

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.

 

[ColourfulFigsnDiags]

You can also use the Solver function which is a much more powerful version of goal seek. It allows you to do some really groovy things like set constraints, and solve for more than one variable, find the value which makes another cell the minimum etc ... it is much better than goal seek.

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

 

[jhardy1]

Another simple approach:

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.