Variance Built In Function

[Muds]

Is there a built in function to get the variance????

[peet]

there is however a Variance function in SQL ... I mean if your gonna calc. variance on data found in a db ....

Return estimates of the variance for a population or a population sample represented as a set of values contained in a specified field on a query.

Syntax

Var(expr)
VarP(expr)

The expr placeholder represents a string expression identifying the field that contains the numeric data you want to evaluate or an expression that performs a calculation using the data in that field. Operands in expr can include the name of a table field, a constant, or a function (which can be either intrinsic or user-defined but not one of the other SQL aggregate functions).

Remarks

The VarP function evaluates a population, and the Var function evaluates a population sample.

If the underlying query contains fewer than two records, the Var and VarP functions return a Null value, which indicates that a variance can't be calculated.

You can use the Var and VarP functions in a query expression or in an SQL statement.

[Kaverin]

There isn't a built in function that I know of, but it's not difficult to do with a little work. Here's something that you might find useful. A long time ago I made a thing that will do some simple statistical analysis stuff on a set of data. My original isn't commented, so I went back and added that in. It does some minimal error checking, like not allowing you to pass anything other than an array (which would be useless), and not trying to analyze a set of data with less than 2 things in it. The optional boolean parameter of the function is for telling it what the data set is. If you give it true, it treats the data as the whole population; if false, it treats it like a sample of the population. The difference affects how variance is calculated. The function assumes the data is a population by default. The array you give the function can be 0 or 1 based since it uses LBound and UBound.

'in a module
Public Type STAT_ANALYSIS
   N As Long
   Sum As Double
   Mean As Double
   Min As Double
   Max As Double
   Range As Double
   SumSquared As Double
   SumOfXiSquared As Double
   Variance As Double
   StandardDeviation As Double
   SourceData As Variant
   Population As Boolean
End Type
 
Public Function Analyze(varData As Variant, Optional ByVal blnPopulation As Boolean = True) As STAT_ANALYSIS
   Dim blnFirstPass As Boolean 'flag to tell if it's the first pass
   Dim i As Long
   'passing anything other than an array is useless, so terminate if varData doesn't have an array
   If Not CBool(VarType(varData) And vbArray) Then Exit Function
   With Analyze
      'total number of items
      .N = UBound(varData) - LBound(varData) + 1
      'statistical analysis needs at the very very least 2 items
      If .N < 2 Then Exit Function
      .Sum = 0
      .SumOfXiSquared = 0
      blnFirstPass = True
      'go through each item in the data set
      For i = LBound(varData) To UBound(varData)
         'get the running sum of the items
         .Sum = .Sum + varData(i)
         'get the running sum of the square of each item
         .SumOfXiSquared = .SumOfXiSquared + (varData(i) ^ 2)
         'if this isn't the first pass, give the max and min a value to start off
         If blnFirstPass Then
            .Min = varData(i)
            .Max = varData(i)
            blnFirstPass = False
         Else
            'these two statements will eventually find the max and min of the data set
            If varData(i) > .Max Then .Max = varData(i)
            If varData(i) < .Min Then .Min = varData(i)
         End If
      Next i
      'get the range of the data
      .Range = .Max - .Min
      'get the mean (average) of the data
      .Mean = .Sum / .N
      'get the sum of all items squared
      .SumSquared = .Sum ^ 2
      'get the variance, based on whether the data set is a population or a sample
      .Variance = (.SumOfXiSquared - (.SumSquared / .N)) / IIf(blnPopulation, .N, .N - 1)
      'get the standard deviation
      .StandardDeviation = Sqr(.Variance)
      'stick the source data into the struct in case you need it later
      .SourceData = varData
      'tell whether or not the source data was treated as a population
      .Population = blnPopulation
   End With
End Function