Arrays in Excel

Question

I have the code below. Which takes the values for an array from a worksheet and calculated the sum and standard deviation.

Public Sub Example()


Dim numbers() As Long, size As Integer, i As Integer

size = WorksheetFunction.CountA(Worksheets(1).Columns(1))
ReDim numbers(size)

For i = 1 To size
    numbers(i) = Cells(i, 1)
Next i

For i = 1 To size

If numbers(i) > 10 Then
    Cells(i, 2) = numbers(i) + 3
 Else
 Cells(i, 2) = numbers(i) + 15
 End If

Next i
Debug.Print Application.WorksheetFunction.StDev(numbers)

Debug.Print Application.WorksheetFunction.Sum(numbers)

End Sub

The sum works correctly but the stdev is slightly different than if I use the STDEV.P function in the worksheet itself.

Any idea why this is happening?

Answer 1

StDev and StDev.P are two different functions

• StDev function evaluates a population sample
• StDev.P function evaluates a population

In your case, you can use

Debug.Print Application.WorksheetFunction.StDev_P(numbers)

depending on whether you are using sample data or entire population