×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Are you an
Engineering professional?
Join Eng-Tips Forums!
• Talk With Other Members
• Be Notified Of Responses
• Keyword Search
Favorite Forums
• Automated Signatures
• Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

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

RE: How do we do it ??

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

RE: How do we do it ??

(OP)
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

Losty

RE: How do we do it ??

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

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 ??

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

RE: How do we do it ??

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!