Formula repersentation 1

[Losty]

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

 

[melone]

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

 

[Losty]

say for example,

R = a*b/c

How can we use the solver or Vlookup option

Thanks

Losty

 

[electricpete]

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.

 

[electricpete]

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.

 

[yakpol]

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!