Porblem with a module..any help is appreciated.

I have a module that I have made a custom function in. It calculates the Standard Deviation for row level values.
I use 6 variables in the Sub Procedure on the form.
If I use 1, 2, 3 or 4 variables it returns a value that looks like a "Scientific" data type "ie: 5E-07"
If I use 5 or 6 variables it works perfectly except for one thing----if all the variables are the same which should give me an answer of 0.00000 I get an error in a line of the code in the macro. This part is giving me problems:

If n > 0 Then 'only applies if points available
RStDevP = Sqr((n * dblSumOfSq - dblSum * dblSum) / n / n)
Else
RStDevP = Null
End If

Can anyone look at it and help me with what is wrong?
Thanks in advance!

Function RStDevP(ParamArray FieldValues()) As Variant
Dim dblSum As Double, dblSumOfSq As Double
Dim n As Long
Dim varArg As Variant
For Each varArg In FieldValues
If IsNumeric(varArg) Then
dblSum = dblSum + varArg
dblSumOfSq = dblSumOfSq + varArg * varArg
n = n + 1
End If
Next
If n > 0 Then 'only applies if points available
RStDevP = Sqr((n * dblSumOfSq - dblSum * dblSum) / n / n)
Else
RStDevP = Null
End If

End Function



The Data type is Double
It's specifically this part that is giving the following error:

"Invalid Procedure Call or Argument"

RStDevP = Sqr((n * dblSumOfSq - dblSum * dblSum) / n / n)

One more thing....
If all 6 values are the same and they are =< 2 as a whole number or decimal like 0.5 then I get the error.
If the values are all 4 as a whole number then I get the error.
If the values are all the same except for the above numbers then i get that funky scientific date type value.

Again it works fine if the numbers are different.

Hi Randy,

Might want to check the formula. In the function Sqr() your totals are ending up in a negative number and then trying to square that number, which will result in an error.

Try this and see if it will work

RStDevP = Sqr((dblSum * dblSum - n * dblSumOfSq) / n / n)

lwells

I did not actually use that smip you sent me because when you said negative number I thought that I could jsut add a "Abs" to always get a positive answer.
It worked.

I still am dealing with the return of weird answers. ir "1E-08". How do I get a number to show up.