How Exclude Blanks, but Not Zeros, from an Average

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("test","num")

The above command shows "test" as the name of the database file and "num" 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(" ",fld_width)

   m.file = FOPEN(dbfname+".dbf")

   IF m.file<0
      RETURN "Error opening database "+dbfname+"."
   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

Last Reviewed: June 27, 1995