Last night a colleague complained because there was no way to evaluate a formula in the worksheet. What he meant was, he wanted to enter “m X + b” (for example, with numerical entries in place of m and b) into a cell, then link to another cell that contained a value of X, and have Excel compute the formula.

I got an idea to try a User Defined Function (UDF), so your formula could be in cell B1, for example, and your X value in A2, and cell B2 would contain the formula

=CalculateThisFormula(B$1,$A2)

and the value displayed in B2 would be the formula’s value evaluated at the value of `X` in A2. I’d never heard of this being done, but I thought it must be possible. To avoid confusing `X` in the formula with the letter *x* in a function, like `exp()`, I decided `x` should be placed in square brackeds, like `[x]`. I then wrote the following UDF:

Function CalculateThisFormula(Formula As Variant, Xvalue As Variant) As Variant Dim sFormula As String Dim sXvalue As String Select Case TypeName(Formula) Case "Range" If Formula.Cells.Count = 1 Then sFormula = Formula.Value Else CalculateThisFormula = CVErr(xlErrValue) Exit Function End If Case "String" sFormula = Formula Case Else CalculateThisFormula = CVErr(xlErrValue) Exit Function End Select Select Case TypeName(Xvalue) Case "Range" If Xvalue.Cells.Count = 1 Then sXvalue = Xvalue.Value Else CalculateThisFormula = CVErr(xlErrValue) Exit Function End If Case "String" sXvalue = Xvalue Case "Double" sXvalue = CStr(Xvalue) Case Else CalculateThisFormula = CVErr(xlErrValue) Exit Function End Select sFormula = Replace(sFormula, "[x]", "(" & sXvalue & ")") sFormula = Replace(sFormula, "[X]", "(" & sXvalue & ")") CalculateThisFormula = Evaluate(sFormula) End Function

The syntax of the UDF is

CalculateThisFormula(Formula, Xvalue)

**Formula** – string representation of formula, or reference to cell containing string representation of formula, where `Formula` is the `f(x)` part of `y=f(x)`, and `x` must be represented as `[x]` and all operators must be explicitly included within `Formula`.

**Xvalue** – string or numeric representation of the value of `x`, or reference to single cell containing string or numeric representation of the value of `x`.

The result is the evaluated value of the formula at the value of x. If either argument contains a multiple cell range or other inappropriate input, the formula returns an error. The following shows a worksheet array in which the UDF is tested:

The formula in cell B2 is

=CalculateThisFormula(B$1,$A2)

and this cell is copied and pasted into B2:E6. It looks good to me.

Other examples of usage in a worksheet cell:

=CalculateThisFormula("4*[x]^2-3*[x]+5",2)

which duplicates cell D6 above

=CalculateThisFormula("1/[x]",4)

which duplicates cell C4

Dan says

Schweeet! :P

Macario says

Hi Jon

What does mean amp, in the formula?

sFormula = Replace(sFormula, “[X]”, “(” & sXvalue & “)”)

I get a compile error in these two lines.

Best regards

Macario

Jon Peltier says

Hi Macario –

Sometimes the WordPress editor changes ampersands into the html compatible combination “&”. I haven’t quite figured out the intricacies of this system, which is new for me, but I think if you switch back and forth from the WYSIWYG mode to the HTML mode too frequently you are at increased risk.

Put an ampersand character into the following line where you see [ampersand] (take out the square brackets too). All I am doing is placing parentheses around sXvalue, although it’s probably not necessary.

sFormula = Replace(sFormula, “[X]”, “(” [ampersand] sXvalue [ampersand] “)”)

If you are using Excel 2000 or later, this should work. Excel 97 uses an older version of VBA, which does not recognize ‘Replace’, but you could use WorksheetFunction.Substitute instead, but check its syntax first.

Macario says

Hi Jon

Thanks you for your answer.

This is a great site.

Best regards.

Macario

Thom says

Did I really “complain”?

;-)

Jon Peltier says

Poetic license!