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