×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • 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.

Students Click Here

How do we do it ??

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

Thanks in advance

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

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

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.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members! Already a Member? Login



News


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:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close