Function to calculate standard error

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I wanted to write a macro or VB function that caculates the Standard Error
(SE) of a variable. I have written the following code:

Public Function SE(Variable AS Variant) As Variant
SE = StDev (Variable)/(Count(Variable)^0.5)
End Function

But i get an error message:
Undefined function in "SE" expression...
I am very new to VB, so am likely doing something very silly,
(i don't even know if i can use a call to StDev or Count in VB)
but would be greateful for any help or guidance...
Thanks,
Fishcakes.
 
Fishcakes,

In Access would you not do this within your SQL somewhere, something like;

SELECT StDev(tblXXX.SomeNumber)/(Count(*)^0.5) AS StErrOfSomeNumber
FROM tblXXX;

In VBA I think you are going to have to pass in 'Variable' as an Array &
work out the count (UBound - easy enough) & Average & Std dev (& I don't know
if someone has some standard code knocking around).

Regards,

Chris.
 
Public Function SE(Variable AS Variant) As Variant
SE = StDev (Variable)/(Count(Variable)^0.5)
End Function
Click to expand...

jetSQL = _
"SELECT Sum(MyVar) AS SigmaVar, " & _
" Sum(MyVar * MyVar) AS SigmaVarSq & " _
" Count(*) AS n " & _
"FROM MyTable " & _
"WHERE Criterion = TRUE"


set rs = db.OpenRecordset(jetSQL, dbOpenSnapshot, dbForwardOnly)

var = (rs!SigmaVarSq - (rs!SigmaVar * rs!SigmaVar)) / (n-1)

stErr = Sqrt(var)



This is from memory, and it's extremely rusty so check the maths... but
it should get you quite close.

To be honest, if this were something important, I'd use an accredited
stats package like SPSS or EpiInfo etc.


Hope it helps


Tim F
 
Back
Top