Microsoft KB Archive/172084

= PRB: CALCULATE STD--Standard Deviation--Gives Numeric Overflow =

Article ID: 172084

Article Last Modified on 5/12/2003

-

APPLIES TO


 * Microsoft Visual FoxPro 3.0 Standard Edition
 * Microsoft Visual FoxPro 3.0b Standard Edition
 * Microsoft Visual FoxPro 5.0 Standard Edition
 * Microsoft Visual FoxPro 5.0a
 * Microsoft Visual FoxPro 3.0 for Macintosh

-



This article was previously published under Q172084



SYMPTOMS
When calculating the standard deviation using the CALCULATE STD function, FoxPro can return results with a numeric overflow on certain numerical values over various ranges of records.



RESOLUTION
Use a custom routine that calculates the standard deviation. The following program accepts two parameters for the table and the field. Then it calculates the various sums and products of the fields before computing the standard deviation. Create a program called std and enter the following code: * Call this program by ?std("TableName","FieldName") or  * by assigning the result to a variable, y= std("TableName","FieldName") *     PARAMETER mtable mfield SELECT SUM(&mfield^2) FROM (mtable) INTO ARRAY yy     SELECT SUM(&mfield)^2 FROM (mtable) INTO ARRAY zz      SELECT COUNT(&mfield) FROM (mtable) INTO ARRAY xx      stdev=((xx*yy-zz)/xx^2)^.5 RETURN ROUND(stdev,12) If a table named values contains the field num, enter this command in the Command Window: ?std("values","num")



STATUS
Microsoft is researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.



MORE INFORMATION
The numeric overflow is generally observed when a table contains the same repeated real values over a broad range of records. For example, if a table contains 10 records and each record's value is 0.031, the CALCULATE STD will produce an overflow. This can vary depending on the value and the number of records.

One way to detect that there was a numerical overflow is to display the result of the CALCULATE STD to the desktop or in a WAIT WINDOW. If SET TALK is ON, the result will be displayed on the desktop automatically. If there is an overflow, all asterisks will be displayed. Also, if the result of the CALCULATE STD is appended or replaced to a numerical field in a table, the following error may occur:

Numeric overflow. Data was lost.

Steps to Reproduce Behavior
Enter the following code in a program and run it: CLEAR SET SAFETY OFF SET DECIMALS TO 12 CREATE TABLE stdresult (num N(15,8)) &&Table for result of calculation APPEND BLANK CREATE TABLE stdtest (num N(15,8))  &&Table of values for calculation FOR i = 1 TO 10 INSERT INTO stdtest(num) VALUES (0.031) ENDFOR

FOR i = 1 TO RECCOUNT ?ALLTRIM(STR(i))+" " SET TALK OFF CALCULATE STD(num) FOR RECNO<i TO nresult && Comment this line & && the next line once && you uncomment the && commented lines below.

??nresult

*nresult=custstd('stdresult','num') && Uncomment this line & the next && line to compute & display the && standard deviation with a                                         && user-defined function.

*?nresult SELECT stdresult REPLACE num WITH nresult  &&Overflow error on 10th iteration SELECT stdtest ENDFOR

SET SAFETY ON  SET TALK ON   SET DECIMALS TO 2

PROCEDURE custstd PARAMETER mtable mfield SELECT SUM(&mfield^2) FROM (mtable) INTO ARRAY yy  SELECT SUM(&mfield)^2 FROM (mtable) INTO ARRAY zz   SELECT COUNT(&mfield) FROM (mtable) INTO ARRAY xx   stdev=((xx*yy-zz)/xx^2)^.5 RETURN ROUND(stdev,12) After running this code, the first nine iterations should be all zeroes. The calculation will be all asterisks and will produce the overflow error message on the REPLACE command.

Keywords: kbprb kbcode KB172084

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.