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
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
RE: Formula repersentation
R = a*b/c
How can we use the solver or Vlookup option
Thanks
Losty
RE: Formula repersentation
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
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
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!