Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Equation Help
I have data in column C, that has to be processed through one of 3 possible
equations (which is inputted in column D). In column D, I am using VLOOKUP to look at column C, and then pull the right equation into column D. However, my issue is that my equations are set something like (=0.0456*x). So in column E instead of getting my values, I just get the equation. I would like to know how I can get column D, to pull in the data it needs (ie the value of x) from column C (ie the data in column C = x (in the equation)). So what I should have then is Column C Column D Column E (Value from the chosen Data Which equation to use equation and data) |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Equation Help
jflatt95 wrote:
I have data in column C, that has to be processed through one of 3 possible equations (which is inputted in column D). In column D, I am using VLOOKUP to look at column C, and then pull the right equation into column D. However, my issue is that my equations are set something like (=0.0456*x). So in column E instead of getting my values, I just get the equation. I would like to know how I can get column D, to pull in the data it needs (ie the value of x) from column C (ie the data in column C = x (in the equation)). So what I should have then is Column C Column D Column E (Value from the chosen Data Which equation to use equation and data) I think VBA is the only way to get this done. Here's a UDF that will do it. Paste the code below in a standard module and call function DoEval in your worksheet like this: =DoEval(D2,TRUE,"x",C2) Here's how the parameters work: 1 Expr) The formula you want to evaluate. I.e., what you have in column D. 2 TrimOutside) Specifies whether to trim off the first and last characters from the formula. If your formula has surrounding punctuation that will confuse Excel's evaluation engine, e.g., (=0.0456*x) specify TRUE here. If your formula looks like a proper Excel formula, e.g., =0.0456*x, specify FALSE. 3 and 4 P) Must be entered in pairs. The first element of each pair is the "variable" in your formula ("x"), the second element is the value you want to replace it with (cell C2). You can enter an unlimited number of such pairs. This is not bullet-proof, but hopefully you find it useful. Here's how the UDF can be used with two pairs of substitutions for P: B3: 0.05 C3: 0.7 D3: '=sin(x)+sin(y) E5: =DoEval(D3,FALSE,"x",B3,"y",C3) (result: 0.694196857) ' BEGIN CODE ---------------------------------------------------- Public Function DoEval( _ Expr As String, _ TrimOutside As Boolean, _ ParamArray P() As Variant) As Variant Dim i As Long Dim L As Long L = UBound(P) If L Mod 2 < 1 Then ' incorrect number of parameters -- must be a pairwise list DoEval = "#N/A" Exit Function End If If TrimOutside Then Expr = Mid(Expr, 2, Len(Expr) - 2) End If Do While i < L Expr = Replace(Expr, P(i), P(i + 1)) i = i + 2 Loop DoEval = Evaluate(Expr) End Function ' END CODE ---------------------------------------------------- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Get Excel to provide equation for my x and y | New Users to Excel | |||
How can I use Excel to solve an equation? | Excel Worksheet Functions | |||
If I don't have MS equation installed in my MS excel how should I | Excel Worksheet Functions | |||
Equation Editor- problem when editing an equation | Excel Discussion (Misc queries) | |||
equation help in excel | Excel Discussion (Misc queries) |