Performing Calculations on Numeric Arrays

Article
06/14/2014

This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.

Many mathematical functions operate on a variable set of numbers. For example, you can take the median, or middle value, of a set of any size. Because you will not know how many numbers the set will contain while you are writing code to find the median, you cannot create a procedure with a set number of arguments. Instead, you can use a dynamic array to store an indeterminate number of values and perform an operation on them.

The following procedure takes a parameter array and returns the median of the values in the array. A parameter array encompasses a variable number of arguments that are passed to a procedure as an array. The ParamArray keyword specifies a parameter array, which must be defined as type Variant.

The Median procedure calls another procedure, IsNumericArray, which determines whether the array contains any non-numeric elements before the Median procedure attempts to find the median. It then calls the QuickSortArray procedure, which sorts the array. Finally, it determines whether the array contains an even or odd number of elements. If the number of elements is odd, the middle element in the sorted array is the median. If the number of elements is even, the median is the average of the two midmost elements.

Function Median(ParamArray avarValues() As Variant) As Double
   ' Return the median of a set of numbers.
   
   Dim lngCount      As Long
   Dim varTemp       As Variant
   
   ' Store array in temporary variable.
   varTemp = avarValues()
   
   ' Check whether array is numeric.
   If IsNumericArray(varTemp) Then
      ' Determine how many elements are in array.
      lngCount = UBound(varTemp) - LBound(varTemp) + 1
      ' Sort the array.
      QuickSortArray varTemp
      ' Determine whether array contains an odd or even number of elements.
      If IsEven(lngCount) Then
         ' If even, need to find the two middle elements and
         ' return the average of their values.
         ' Remember we're working with a zero-based array!
         Median = (varTemp(lngCount / 2 - 1) + varTemp(lngCount / 2)) / 2
      Else
         ' If odd, need to find the middle element.
         Median = varTemp(Int(lngCount / 2))
      End If
   Else
      ' Return -1 if array isn't numeric.
      Median = -1
   End If
End Function

To test the Median procedure, try calling it with an even set of numbers, then with an odd set of numbers, as follows:

? Median(45, 67, 23, 89, 52, 101)

To make sure it is working properly, you can check it against the Excel Median worksheet function. Note that the Excel Median function can take no more than 30 arguments, while the procedure shown here can take any number of arguments.

You could also modify this procedure to take an array, rather than a parameter array. The parameter array is somewhat easier to test in isolation, but a procedure that takes an array might be more practical for use within your code. For example, you might have a procedure that fills an array with numeric data from a data source, which you then can pass to the Median procedure to determine the median of the set of numbers, without having to pass each value as an argument to the procedure.

The strategy shown here for finding the median also works for other operations that take an indeterminate number of values, such as finding the average or standard deviation, or performing other statistical calculations.

Performing Calculations on Numeric Arrays

See Also

Additional resources