Three ways to use Excel worksheet functions in VBA

Function xlAverage01(rng)     xlAverage01 = WorksheetFunction.Average(rng) End Function Function xlAverage02(rng)     With WorksheetFunction         xlAverage02 = .Average(rng)     End With End Function Function xlAverage03(rng)     Dim wsf As WorksheetFunction     Set wsf = WorksheetFunction     xlAverage03 = wsf.Average(rng) End Function

Mix-up about the covariance in Excel

One of the Excel mix-ups in its statistical functions that lasted longer is about the computation of the covariance.

Until recently (we think that this change was only introduced in the 2007 version) Excel provided two functions for the variance and standard deviation, one,for population and the other for sample data.

However, there was only one function for the covariance that returned the value for population data. There was (an there is) also only one function for the correlation, which is right, since the correlation values are the same for population ans sample data. This situation could lead to the erroneous conclusion that the covariance also has the same value for both cases.

The fixing of this situation was made by introducing two formulas for the computation of the covariance.

However, the procedure Covariance  of the Analysis ToolPak still returns the population covariances. Additionally, it also maintains another heritage mix-up. Although the Toolpak general methodology presents fixed values in the output, that does not change automatically if data values change, in this case the diagonals of the covariance matrix contain the formula to compute the population variance. This means that changes in the data will automatically change values in the diagonal, contrary to what happens for the other elements of the matrix.

The matrix.xla add-in, presented in another message also has only one function for the covariance matrix that returns the values for population data.

Generating correlated normal random numbers - Part 01

This function generates five columns containing numbers (the headers are not generated by the function):

1. Order number (i)
2.  Uniform random sequence (Z1)
3. Uniform random sequence (Z2)
4. First combination of Z1 and Z2  >>> Z3
5. Second combination of Z2 and Z1 >>> Z4

The pairs (Z1,Z3) and (Z2,Z4) simulate a sample extracted from populations in which the pairs have a chosen value for the correlation. As it happens in sampling you usually get a value for the sampling correlation that differs from the population.

The values returned can be unstandardized specifying for each case the mean and standard deviation.

If you are not satisfied with the outcome, recalculate the spreadsheet. As the random values are generated by VBA code they are not volatile as it happens when you input the Excel built-in RAND function in a spreadsheet cell.

See more about volatility in the follow link in a post entitled Handle Volatile Functions like they are dynamite:

http://chandoo.org/wp/2014/03/03/handle-volatile-functions-like-they-are-dynamite/

  

The inputs are:

1. Number of lines in the sequences (k)
2. Value of population correlation  

Source:
https://www.blogger.com/blogger.g?blogID=3230308801600609435#editor/target=post;postID=4988601992196925693;onPublishedMenu=posts;onClosedMenu=posts;postNum=1;src=postname

In a future message the remaining models in the source will be presented.

B6:E9	{=xlCorrMtx(B12:E41)}
A12:E41	{=xlRndNormal(C3,E3)}
H6:K9	{=xlCorrMtx(H12:K41)}
H12	=H$3+B12*H$4

Function xlRndNormal(k As Integer, corr)     With WorksheetFunction           Dim i As Integer         Dim rslt()         ReDim rslt(1 To k, 1 To 5)           For i = 1 To k             rslt(i, 1) = i         Next i           For i = 1 To k             rslt(i, 2) = .Norm_S_Inv(Rnd)         Next i           For i = 1 To k             rslt(i, 3) = .Norm_S_Inv(Rnd)         Next i           For i = 1 To k             rslt(i, 4) = corr * rslt(i, 2) + Sqr(1 - corr ^ 2) * rslt(i, 3)         Next i         For i = 1 To k             rslt(i, 5) = corr * rslt(i, 3) + Sqr(1 - corr ^ 2) * rslt(i, 2)         Next i         xlRndNormal = rslt           End With End Function

The code for the function xlCorrMtx is in this blog at

https://www.blogger.com/blogger.g?blogID=3230308801600609435#editor/target=post;postID=4988601992196925693;onPublishedMenu=posts;onClosedMenu=posts;postNum=1;src=postname

Sample covariance matrix

Function xlCovSMtx(rng As Range)         Dim i, j     Dim nCls As Integer     Dim mtx()         With WorksheetFunction             nCls = rng.Columns.Count         'MsgBox nCls         ReDim mtx(1 To nCls, 1 To nCls)             For i = 1 To nCls             For j = 1 To nCls                 mtx(i, j) = .Covariance_S(rng.Columns(i), rng.Columns(j))             Next j         Next i             xlCovSMtx = mtx         End With End Function Function xlCovSMtx_U(rng As Range)         Dim i, j     Dim nCls As Integer     Dim mtx()         With WorksheetFunction             nCls = rng.Columns.Count         'MsgBox nCls         ReDim mtx(1 To nCls, 1 To nCls)             For i = 1 To nCls             For j = 1 To nCls                 If i <= j Then                     mtx(i, j) = .Covariance_S(rng.Columns(i), rng.Columns(j))                 Else                     mtx(i, j) = 0                 End If             Next j         Next i             xlCovSMtx_U = mtx         End With End Function Function xlCovSMtx_L(rng As Range)         Dim i, j     Dim nCls As Integer     Dim mtx()         With WorksheetFunction             nCls = rng.Columns.Count         'MsgBox nCls         ReDim mtx(1 To nCls, 1 To nCls)             For i = 1 To nCls             For j = 1 To nCls                 If i >= j Then                     mtx(i, j) = .Covariance_S(rng.Columns(i), rng.Columns(j))                 Else                     mtx(i, j) = 0                 End If             Next j         Next i             xlCovSMtx_L = mtx         End With End Function