Standard Deviation Calculation Form 1

[SeadnaS]

Hi,

I am trying to calculate a standard deviation of 8 values entered in text boxes on a form. The text boxes are bound to a table and each text box is named x1, x2, x3... up to x8

I want to be able to press a button to calculate the standard deviation of the 8 numbers and display the result in another text box and send result to a column on the table. The table will have fields x1 to x8 and Std_Dev for the Standard Deviation result.

What would be the best way to go about achieving this? Macro? SQL? Any help much appreciated.

 

[SeadnaS]

Oh sorry forgot to mention this is in Access 2003. Thanks!

 

[MajP]

Your data base is not properly designed. These values should all be records in a single field not separate fields. If you can redesign, I would recommend. If you are stuck with what you have, then you can use a union query to get this in columns.

id1 2 2.5 5
id2 4 3.5 6

into
id1 2
id1 2.5
id1 5
id2 4
id2 3.4
id2 6

then a simple query can return the std

id1 its standard dev
id2 its standard dev

do not save it in the table, you can always display a calculated value based on your query.

 

[MajP]

I would look at fixing your table design, or using a normalizing UNION query first. However I did this at one time.

Public Function getSTDev(ParamArray varVals() As Variant) As Variant
  Dim varVal As Variant
  Dim intcount As Integer
  Dim Arr() As Variant
  For Each varVal In varVals
    If IsNumeric(varVal) Then
      Arr = AddElement(Arr, varVal)
    End If
  Next varVal
  getSTDev = StdDev(Arr)
End Function
Function StdDev(Arr() As Variant) As Variant
     Dim i As Integer
     Dim avg As Single, SumSq As Single
     Dim k As Integer
     avg = Mean(Arr)
     For i = LBound(Arr) To UBound(Arr)
          SumSq = SumSq + (Arr(i) - avg) ^ 2
          k = k + 1
     Next i
     StdDev = Sqr(SumSq / (k - 1))
End Function
Public Function AddElement(ByVal vArray As Variant, ByVal vElem As Variant) As Variant
      ' This function adds an element to a Variant array
      ' and returns an array with the element added to it.
      Dim vRet As Variant ' To be returned
      If IsEmpty(vArray) Or Not IsDimensioned(vArray) Then
          ' First time through, create an array of size 1.
          vRet = Array(vElem)
      Else
          vRet = vArray
          ' From then on, ReDim Preserve will work.
          ReDim Preserve vRet(UBound(vArray) + 1)
          vRet(UBound(vRet)) = vElem
      End If
      AddElement = vRet
  End Function
Public Function IsDimensioned(ByRef TheArray) As Boolean
      If IsArray(TheArray) Then ' we need to test it! otherwise will return false if not an array!
                      ' If you put extensive use to this function then you might modify
                      ' it a lil' bit so it "takes in" specific array type & you can skip IsArray
                      ' (currently you can pass any variable).
        On Error Resume Next
            IsDimensioned = ((UBound(TheArray) - LBound(TheArray)) >= 0)
        On Error GoTo 0
    Else
        'IsDimensioned = False ' is already false by default
        Call Err.Raise(5, "IsDimensioned", "Invalid procedure call or argument. Argument is not an array!")
    End If
End Function
Public Function HasDimension(ByRef TheArray, Optional ByRef Dimension As Long = 1) As Boolean
    Dim isDim As Boolean
    Dim ErrNumb As Long
    Dim LB As Long
    Dim errDesc As String
    'HasDimension = False
    
    If (Dimension > 60) Or (Dimension < 1) Then
        Call Err.Raise(9, "HasDimension", "Subscript out of range. ""Dimension"" parameter is not in its legal borders (1 to 60)! Passed dimension value is: " & Dimension)
        Exit Function
    End If
    
    On Error Resume Next
        isDim = IsDimensioned(TheArray) 'IsArray & IsDimensioned in one call. If Err 5 will be generated if not Array
        ErrNumb = Err.Number
        If ErrNumb <> 0 Then
            errDesc = Err.Description
        End If
    On Error GoTo 0
    
    Select Case ErrNumb
        Case 0
            If isDim Then
                On Error Resume Next
                    LB = LBound(TheArray, Dimension) 'just try to retrive Lbound
                    HasDimension = (Err.Number = 0)
                On Error GoTo 0
            End If
        Case 5
            Call Err.Raise(5, "HasDimension", "Invalid procedure call or argument. Argument is not an array!")
        Case Else
            Call Err.Raise(vbObjectError + 1, "HasDimension", _
                "This is unexpected error, caused when calling ""IsDimensioned"" function!" & vbCrLf & _
                "Original error: " & ErrNumb & vbCrLf & _
                "Description:" & errDesc)
    End Select
End Function
Function Mean(Arr() As Variant)
     Dim Sum As Single
     Dim i As Integer
     Dim k As Integer
     Sum = 0
     For i = LBound(Arr) To UBound(Arr)
         k = k + 1
         Sum = Sum + Arr(i)
     Next i
     Mean = Sum / k
     'MsgBox Mean
End Function

the function can be used in a query or a calculated control. Simply pass in the field values like so

getSTDev([fldOneName],[fldTwoName],...[fiendNname])

 

[SeadnaS]

Thanks for the reply.

So how do I implement this? I'm stuck with separate fields because of the nature of the data. I'm fairly new to this and need a more detailed explanation of how to write this union query.

The table i'm using (lets call it table1) is structured like this:

ID ¦ x1 ¦ x2 ¦ x3 ¦ x4 ¦ x5 ¦ x6 ¦ x7 ¦ x8 ¦ STD_DEV
----------------------------------------------------


x1 to x8 are entered in text boxes in a form (called Calculations) i need to display the Standard deviation in another text box on the form and send it to the STD_DEV column on the table and the x numbers to their corresponding columns also. I know how to use the StDev to get a standard deviation for a column on a table but cant figure out how to do it for the text boxes or a row.

Any further help much appreciated!

 

[SeadnaS]

Thanks MajP that worked! Excellent stuff!

 

[SeadnaS]

I'm having a small problem. My results are rounded down so a number such as 34.6786 is coming out as just 34. I've changed the field type from long unteger to decimal but it still stays the same. I'm using a macro with getSTDev in it. Is it because i'm using a macro? I'm also using this macro to get the average of the 8 numbers and this rounds my numbers also.

 

[SeadnaS]

Also the form will no longer create new records. It just keeps writing over the first record. Its set to data input now and still keeps overwriting the first record. Thanks in advance for any help.

 

[SeadnaS]

Ok figured out how to fix those issues.