 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.)            Return to Top of Page 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.) Return to Top of Page 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.)      Return to Top of Page 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.)  Return to Top of Page 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.)      Return to Top of Page 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.)      Return to Top of Page 