The DBAvg returns an average value from a column in a table. The rows that are included in the average can be limited by applying a filter.
This function can be added to your worksheet by using the Function Wizard.
DBAvg( connection , table , column , [ filter ] , [ parameter1 ] , [ ...parameter n ])
The DBAvg function syntax has these parts:
|connection||Required. This argument identifies the connection name used to connect to the database.|
|table||Required. This argument identifies a SQL statement or a specific internal table name in the database. If this argument does not supply a valid source of data, then the result of the function will be #TABLE? ("table name" not found).|
|column||Required. This argument identifies an internal field name or alias returned by the table name argument. If this argument does not contain a valid field name, then the result of the function will be #FIELD? ("field name" not found).|
|filter||Optional. A filter is a string of text that includes comparisons between fields in the table and specific values as a literal value or a parameter. Parameters to a cell reference are indicated by using a question mark placeholder within the filter string. The function will evaluate the question mark placeholder with the corresponding parameter. Only the records that satisfy the filter conditions will be used to return values.|
|parameter 1 , [ ...parameter n ]||Required if filter uses placeholders. This is a comma-delimited list of values or cell references used to replace the corresponding question mark placeholders within the filter string. Parameter 1 is used for the first question mark placeholder in the filter, Parameter 2 would represent the second question mark placeholder, and so on.|
Returns the average total invoice amount for the vendor specified in cell A5.
=DBAvg("Sage 100 Contractor", "acpinv", "invttl", "vndnum=?", A5)