Special Offer
Advanced Excel VBA prorgrams in finance & statistics.
- open source code -

Include Monte Carlo Simulation, Multiple Regression, Bootstrap, Random Numbers Generator and many more!
Risk Analyzer
 The easiest to use decision and risk analysis program that one can find. Completely menu driven.
Visual Basic Collection
Learn to write Excel macros and get over 1200 macros.
<>.



Excel VBA Statistics and Mathematics Examples

This page contains simple Excel VBA Statistics and Mathematics examples.  Many of the examples used are already available in functions that come with Excel.  Users can use these Excel functions as tools to check against the results that come from the examples.  These examples require basic programming skills in VBA.  Users are encouraged to read the Simulation Based Tutorial 101 if they have problem understanding the programming concepts and terms used on this page.

This document contains information about the following topics.

Finding Median

To find the median from an array, two steps are required.  First the array needs to be sorted (in either order), then a decision structure needs to be used.

Step 1.  Sort the array.  See example on sorting.

Step 2.  If the total elements in the array is an odd number (defined by Ubound(Arr) Mod = 1),
             then the median is the middle number (defined by Arr(Int(Ubound(Arr) / 2) + 1) ).
             If the total elements in the array is an even number then take the average of the two middle
             numbers.
Function u_median(Arr() As Single)

    Call Sort(Arr)
   
    If UBound(Arr) Mod 2 = 1 Then
        u_median = Arr(Int(UBound(Arr) / 2) + 1)
    Else
        u_median = (Arr(UBound(Arr) / 2) + Arr(Int(UBound(Arr) / 2) + 1)) / 2
    End If
   
End Function  


This function is also implemented in the Bootstrap - A Non-Parametric Approach example.
               
(This function is similar to the MEDIAN() function provided by Excel.)           



Generate Random Numbers From Uniform Distribution

This function provides an uniform distribution random number between a specified range. 


Function UniformRandomNumner(Low As Single, High As Single)

        UniformRandomNumner = Rnd * (High - Low + 1) + Low
       
End Function

For example, the following function returns a random number between 10 and 100:
UniformRandomNumner(10, 100)

     
(This function is similar to the RANDBETWEEN() function provided by Excel.)  


Sum Numbers

This function reads an array, and then returns the total number of the elements in the array.
Function u_sum(Arr() As Single)

    For i = 1 To UBound(Arr)
        u_sum = u_sum + Arr(i)
    Next i
   
End Function

Here is a sub routine that calls the u_sum function and returns the sum in a message box.
Sub computeSum()

    Dim arr(3) As Single
    arr(1) = 5
    arr(2) = 4
    arr(3) = 10
   
    MsgBox u_sum(arr)

End Sub

The message box will return 19.
(This function is similar to the SUM() function provided by Excel.)     


Compute Factorial

To initiate the loop,  we assign u_fact, the function, an initial value of 1.  Then we multiple the new number (i) with the current number (u_fact) until i = Int(number).  Note that the Int function is require to make sure the number is an integer or becomes an integer.
Function u_fact(number As Single)

    u_fact = 1
    For i = 1 To Int(number)
        u_fact = u_fact * i
    Next i
   
End Function


For example, the following function returns a 6:
u_fact(3)


(This function is similar to the FACT() function provided by Excel.) 



Binomial Coeffieient



Function u_binoCoeff(n, j)

          Dim i As Integer
          Dim b As Double
         
          b = 1
          For i = 0 To j - 1
                    b = b * (n - i) / (j - i)
          Next i
          u_binoCoeff = b
         
End Function


The following function compute all the possible combination on 5 items choosen from 10 items.  This function returns 252:
u_binoCoeff(5, 10)


This function is also implemented in the Bootstrap - A Non-Parametric Approach example.
               
(This function is similar to the COMBIN() function provided by Excel.)     

Cumulative Standard Normal Distribution

This function computes the area under the left hand side of a specified value (the z value) from a standard normal distribution density function curve.  In plain English, it returns the probabilty of  X that is smaller than a specific value.

If you do not know what a normal curve looks like or have already forgotten about it, here is a sample:


In this example, the probabilty of X smaller than 1.64 (z) is 94.9497%. 


Function u_SNorm(z)

    c1 = 2.506628
    c2 = 0.3193815
    c3 = -0.3565638
    c4 = 1.7814779
    c5 = -1.821256
    c6 = 1.3302744
    If z > 0 Or z = 0 Then
              w = 1
    Else: w = -1
    End If
    y = 1 / (1 + 0.2316419 * w * z)
    u_SNorm = 0.5 + w * (0.5 - (Exp(-z * z / 2) / c1) * _
            (y * (c2 + y * (c3 + y * (c4 + y * (c5 + y * c6))))))
           
End Function


u_SNorm(1.64) = 0.949497

This function is also implemented in the Black-Scholes Option Pricing Model - European Call and Put example.
               
(This function is similar to the NORMSDIST() function provided by Excel.)