Using Calculated Range as Input to Standard Excel Function

July 21, 2002 - by Bill Jelen

Tushar Mehta's set of user-defined functions was nominated as the Answer of the Week.

The Question: Lucas in London posed this question. He has a series of raw data in column A of his worksheet. He would like a function that would (a) calculate the percentage difference between each data point, then (b) calculate the standard deviation of those differences. There can be no other data on the worksheet besides the raw data and the result. The image below shows how to work this out using formulas. The goal is to write a single user-defined function that will replace the STDDEV function in B5.

A few MrExcel MVPs spotted TursharM's slick code and nominated it for inclusion in the hall of fame.

What it does:

• Tushar provides a function called ComputeDifference. This function will accept a range of n cells as input, and return an array of size (n-1) that reflects the percentage difference between cells. This function is equivalent to entering the formulas in B2:B4 above.
• Once Tushar had the ComputeDifference function, it is now easy to use the returned array from ComputeDifference as the input to any Worksheet Function. Excel allows us to use the Application.WorksheetFunction object to simulate most Excel built-in functions in VBA code. Tushar's second function is called DiffStDev. It accepts a range of cells as input. In a single line of code, Tushar sends the input range to ComputeDifference and then uses the returned array as the argument for the Application.WorksheetFunction.StdDev function.
• To demonstrate how easy it is to adapt this to other functions, Tushar provides a DiffAvg function to calculate the average of the differences.

This code is entered in a standard module:

Option Explicit
Function computeDiff(rngIn As Range) As Variant
		'Assume rngIn is a single column _
		 If rngIn is a row, don't do the transpose _
		 If it is multi-dimensional, the problem is not well defined
		Dim myArr As Variant, i As Long
		myArr = Application.WorksheetFunction.Transpose(rngIn)

		For i = LBound(myArr) To UBound(myArr) - 1
				myArr(i) = (myArr(i + 1) - myArr(i)) / myArr(i)
				Next i
		ReDim Preserve myArr(LBound(myArr) To UBound(myArr) - 1)
		computeDiff = myArr
End Function

Function DiffStDev(rngIn As Range) As Variant
    DiffStDev = Application.WorksheetFunction.StDev(computeDiff(rngIn))
End Function

Function DiffAvg(rngIn As Range) As Variant
    DiffAvg = Application.WorksheetFunction.Average(computeDiff(rngIn))
End Function

Using Tushar's UDF, Lucas can enter a single formula to calculate the standard deviation of the differences.