×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

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!
  • Students Click Here

*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

Jobs

Formula repersentation

Formula repersentation

Formula repersentation

(OP)
Hi Guys,

This is what i am trying to achieve:

I want to make a calculation sheet where the formula involves three variables...say,

Results(R) = f(a,b,c)

At any given time, three of the varibles are known and the user needs to find the 4th variable.

I want a simple interface, where user first select which variable he/she wants to find out (say from a drop-down list) and accordingly the excel displays ( on the same sheet) cells for entering three unknown variables thus calculating the fourth variable.

Since my knowledge of excel is pretty basic, any advice on modelling the above problem without VBA or writing code tc would be really useful.

Thanks

Losty

RE: Formula repersentation

How are these variable related?  It may be possible to use a lookup table, or it may require the use of the "solver" funtionality in Excel.....

RE: Formula repersentation

(OP)
say for example,

R = a*b/c

How can we use the solver or Vlookup option

Thanks

Losty

RE: Formula repersentation

It's hard to understand exactly the problem.

If in some cases you know R and want to find one of the inputs a or b or c where formula is not explicitly known, then solver sounds like the way to go.

On the other hand, if you know the exact formula's R = f1(a,b,c), a=f2(R,b,c), b = f3(R,a,c), c=f4(R,a,b)... and you are just looking for a convenient way to present user with input screen for appropriate calculation, then I would suggest autofilter.  The columns will be:
R_known_unkown, a_known_unkown, b_known_unkown, c_known_unkown,  R, a, b, c

The data is as follows:
Heading R      a     b     c     R       a  b c
Row2: unknown known known known f1(a,b,c) 0 0 0
Row3: known unknown known known 0 f2(R,a,b) 0 0
Row4: known known unknown known 0 0  f3(R,a,0) 0
Row5: known known known unknown 0 0  0 f4(R,a,b)
where the f1,f2,f3,f4 are defined as equation in terms of the cell values to left and right representing the inputs (initialized to ).

Select the entire 8 columns and presss autofilter.

To use the spreadsheet, the user selects the combination of unknown and known from the first four columns.  Then there is only one row left.  In that row the user enters the input variables and the result is calculated. I would write-protect the formula so it doesn't get overwritten.  A little bit of color coding and instructions would help as well.

I'm sure there are lots of better ways to do it but this is one quick easy way.

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

RE: Formula repersentation

Perhaps easier for the user would be to condense those first four columns into one column with choices
Row2: solve R
Row3: solve a
Row4: solve b
Row5: solve c
Remaining columns would be the same.

User picks the one he wants which shows the row of interest and hides the other. The next 4 columns of that row provide the inputs and outputs for the equation. Color code green for inputs and red for outputs.

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

RE: Formula repersentation

Losty,
Here's another solution that using tools/goalseeker and one line macro. It will solve equation of four variables (in your example a*b/c - R =0) even if you cannot easly derive formula for anyone of them. Follow the link to the file on my website EngTipsAnswer143602

hope it 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!


Resources