Expert Lotto - StdDev(Diff)

[PAB]

Hi Expert Lotto,

Do you have an Excel Formula or VBA Function that you can Post to Work out the Standard Deviation Please?.
If Not, What is the Algorithm for it Please?.

Thanks in Advance.
All the Best.
PAB

 

[GillesD]

Standard deviation

Hi PAB, Excel has two functions that calculate the standard deviation:

- STDEV: Calculates the standard deviation based on a sample;
- STDEVP: Calculates the standard deviation based on the entire population.

Currently, after 2452 draws in the Canadian 6/49, usiing the STDEV function, the average standard deviation is 14.0 with a minimum of 3.2 (with numbers 38-43-44-45-46-47) and a maximum of 23.0 (with numbers 02-04-05-44-46-47).

 

[PAB]

Thanks for the Reply GillesD, I Hope you are Keeping Well.

I used this Formula ...

=STDEV(IF(A1:F1=0,False,A1:F1))

... in a Spreadsheet, but I was Mainly after a Function ( with Criteria I could Set ) I could Use for Each Combination when I Cycle through ALL the Combinations of a 649 Lotto for Example.

Thanks in Advance.
All the Best.
PAB

 

[GillesD]

Standard deviation

Most likelt PAB you are working in VBA. So as you cycle through all combinations, the six numbers could be variables A, B, C, D, E and F, each being integers. If you define a variable nStDev as Long, you could use the following line to calculate the standard deviation for the 6 numbers:

nStDev = Application.WorksheetFunction.StDev(A, B, C, D, E, F)

After that, you could determine if the value of the standard deviation for the six numbers (A to F) meet any criteria you define. This is what I used to determine the distribution of standard deviation for all 13,983,816 combinations.

 

[PAB]

Thanks for the Reply GillesD, it is Appreciated .

You are Quite Right, I am Using VBA. I will give your Algorithm a Try.

All the Best.
PAB

 

[GillesD]

Standard deviation for all combinations

PAB, you will find below the macro I use to go through all combinations and for each one, calculate its standard deviation. Where indicated, you can insert your VBA code to do whatever you want with the standard deviation just calculated.

Option Explicit
Dim A As Integer, B As Integer, C As Integer, D As Integer, E As Integer, F As Integer
Dim nStDev As Double

Sub All_StDev()
Application.ScreenUpdating = False
For A = 1 To 44
For B = A + 1 To 45
For C = B + 1 To 46
For D = C + 1 To 47
For E = D + 1 To 48
For F = E + 1 To 49
nStDev = Application.WorksheetFunction.StDev(A, B, C, D, E, F)
' Insert your code to analyze the standard deviation just calculated
Next F
Next E
Next D
Next C
Next B
Next A
End Sub