stDev Function - Inconsistent Results

[ZappaDog]

I am changing an Excel worksheet to Access 97 and noticed that the stDev result was different between the 2 programs using the same data... Any ideas why?

Access Query:
SELECT StDev([Val]) AS STDEV
FROM tbl_SPC_AllVal; = 0.0161574017103552

Excel Calculation:
=STDEV(K27:K56) = 0.004231248
I also noticed that Excel results varied in the 9th decimal place depending how numbers were grouped???

Dataset:
195.299,195.301,195.298,195.303,195.307,195.299,195.301,
195.303,195.303,195.299,195.301,195.293,195.309,195.308,
195.302,195.303,195.303,195.301,195.3,195.304,195.293,
195.295,195.306,195.305,195.301,195.306,195.299,195.306,
195.301,195.293

 

[Darrylles]

Hiya,

The 0.004231248 standard deviation is in fact correct for the test data.

I've got the same result (barring rounding) from both Excel and Access using your data range.

Access displays this result from your data range.
It gives this: 0.00423124733409578.

Check the data-type definition in your Access table resultant field - it should be Number, and 'Long' data type.

Your prob revolves around 'select Stdev[VAL]...'. VAL is the problem here.
Rename this 'VAL' object for a start, because VAL is a function name - plenty of confusion to MS Access.

Also N.B. The results in an Access datasheet will 'round' values if the column isn't wide enough to display a numeric type, just drag the column to widen it for accuracy.

Regards,

Darrylle


"Never argue with an idiot, he'll bring you down to his level - then beat you with experience."

 

[MichaelRed]

Something this way error comes.

With your "value" list, I get the same answer in both programs.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[ZappaDog]

I have tried all of the above, and nothing seems to work. I can't set the table data type to long integer, as i am working with decimals, so i used single which should be OK. I changed the fieldname from Val to DataVal. i checked recordset to make sure it was getting all the data, I can't think of anything else to try.

My code is as follows:

Set rst30pc = CurrentDb.OpenRecordset & _
("tbl_SPC_AllVal", dbOpenDynaset)
For ic1 = 1 To 30
strText = "txt" & ic1
rst30pc.AddNew
rst30pc("DataVal&quot = Val(rpt(strText))
rst30pc.Update
Next ic1
Set rst = CurrentDb.OpenRecordset & _
("SELECT stDev([DataVal]) as [SIGMAS] FROM " & _
tbl_SPC_AllVal;&quot
MsgBox rst("SIGMAS&quot 'Gives 1.61574017103552E-02
sigmas = rst("SIGMAS&quot
rpt("txtSigmas&quot = sigmas

Please HELP me, i am running out of time and stuck on this.

 

[ZappaDog]

I solved the problem by changing the field size in table from Single to Double. It works, but I don't know why. My data is easily small enough to fit in a single field. Any ideas?

 

[Darrylles]

Hi Zap,

As I said earlier, I got the correct results (even with LongInt!).

Bear in mind that single and double refer to the level of precision - not the 'size' of the value of the data.

Two things that you didn't supply in your prev post was the data-type of the 'sigmas' variable, nor your 'txtSigmas' field.

The best way of finding the answer to this is to intercept results at the earliest stage, rather than passing them from pillar to post.
I'm sure you're tried this already.

Regards,

Darrylle "Never argue with an idiot, he'll bring you down to his level - then beat you with experience."