Ask Mr. Excel - Excel Message Board Hall of Fame

Back

About Mr.Excel

Consulting Services

Learn About Excel

Challenge of the Month

Message Board Hall of Fame - Using Calculated Range as Input to Standard Excel Function

Tushsar Meta's set of user defined function 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 MVP's 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.

Adapted from this thread on the message board

Tushar Meta is wrapping up his PhD in Operatations Management at the University of Rochester. He has worked for a decade as a software consultant on three continents. He joined the MrExcel Message Board in May, 2002. Tushar wins fame, fortune, and a stylish, MrExcel Deluxe Can Koozie for being selected as this week's Hall of Fame winner.

The Message Board Hall of Fame is designed to recognize those solutions on the message board which offer widespread appeal and a clever solution. Solutions are identified and nominated by the MrExcel MVP team.

MrExcel.com provides examples of Visual Basic procedures for illustration only, without warranty either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. The Visual Basic procedures on this web site are provided "as is" and we do not guarantee that they can be used in all situations.

Excel is a registered trademark of the Microsoft® Corporation.