In Calculation Bug Fixed, an old but ongoing thread in the Daily Dose of Excel blog, I mentioned a problem with Excel 2007’s trendline regression formulas. Apparently small regression coefficients were treated like small errors that occur in the insignificant digits when converting from binary to decimal. I had documented the error in May of 2007, and while following up in Daily Dose, I tested in Excel 2007 SP1, and the error has been corrected. I’m here not to raise unnecessary alarms among users who have already updated to SP1, but to report correction of this problem, and to describe the errors for those who have still not updated. [Read more…] about Excel 2007 Regression Error – Fixed in SP1

# Statistics

## UDF to Calculate an Arbitrary Formula

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