Microsoft KB Archive/97647

{| = How Exclude Blanks, but Not Zeros, from an Average =
 * width="100%"|

ID: Q97647

The information in this article applies to:


 * Microsoft FoxPro for Windows, versions 2.5 and 2.5a
 * Microsoft FoxPro for MS-DOS, versions 2.0, 2.5, and 2.5a

SUMMARY
FoxPro's AVERAGE command sums numeric fields and divides the summed result by the number of records summed. In some cases, you may want to determine the AVERAGE only for fields that are not blank. Since there is no command in the FoxPro language that can distinguish between a numeric field that is blank and one that contains a zero, you must write a user-defined function similar to the one below in order to accomplish this task.

MORE INFORMATION
The following function requires two parameters, the database name and field name. An example of calling this function is as follows:

ave = ave_nobl(&quot;test&quot;,&quot;num&quot;) The above command shows &quot;test&quot; as the name of the database file and &quot;num&quot; as the numeric field to be averaged.

Sample Code
FUNCTION ave_nobl

PARAMETERS dbfname,fld_name PRIVATE blank,skipbytes,i,numofblanks skipbytes=0 numofblanks=0

IF USED(dbfname) SELECT (dbfname) ELSE SELECT 0 USE (dbfname) ENDIF

headersize = HEADER(dbfname) no_records = RECCOUNT rec_size  = RECSIZE fld_width = FSIZE(fld_name)

* Place the database structure information in an array to  * determine the field's position. The position is equal to  * the row number of the field in the array, which is returned * by ASUBSCRIPT.

=afields(afld_pos) fld_pos = ASUBSCRIPT(afld_pos,ASCAN(afld_pos,UPPER(fld_name)),1)

FOR i=1 TO (fld_pos-1) skipbytes=skipbytes+FSIZE(FIELD(i)) ENDFOR USE

blank = REPLICATE(&quot; &quot;,fld_width)

m.file = FOPEN(dbfname+&quot;.dbf&quot;)

IF m.file<0 RETURN &quot;Error opening database &quot;+dbfname+&quot;.&quot; ELSE * The numeric field begins on the first byte following the * header and any preceding fields (headersize+skipbytes+1).

m.move = FSEEK(m.file,headersize+skipbytes+1)

* Read the value of the numeric field and compare it to     * the variable blank.

m.num = FREAD(m.file,fld_width)

DO WHILE .NOT. FEOF(m.file) IF m.num = blank numofblanks = numofblanks+1 ENDIF

* Move to the beginning of the numeric field in the next * record, read the next value, and compare it to the variable * blank.

m.move = FSEEK(m.file,(rec_size-fld_width),1) m.num =   FREAD(m.file,fld_width) ENDDO m.move = FCLOSE(m.file)

USE (dbfname) SUM EVAL(fld_name) TO sub_result USE

* Divide the sum by the number of records that are not blank.

result = sub_result/(no_records-numofblanks)

* Return the result to the program that called ave_nobl.

RETURN result ENDIF Additional reference words: FoxDos FoxWin 2.00 2.50 2.50a null blank KBCategory: kbprg KBSubcategory: FxprgGeneral
 * }