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.
Generate Random Numbers From Uniform Distribution Sum Numbers Compute Factorial Binomial Coefficient 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. 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. 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. |