Show code in...     

  

 
All Functions Math Functions String Functions Logical Functions
Time Functions Statistical Functions Information Functions
Lookup & Reference Functions Database Functions Financial Functions

Database Functions
The only functions available in
ExcelWriterSE and ExcelWriterLE
are sum and average.
The function examples are based on the following database.



Database Functions
Daverage*

Returns the average of the values in a list or database, that meet a specified condition. Daverage takes three arguments:

Database   A range of cells containing the database or list of values.
Field   The column containing the values to average. To specify Field, use the column name, or its number (use 1 for the first column in the database, 2 for the second, etc.).
Criteria   A range of cells containing the conditions that cell values must meet to be included in the list of values to average. The range must contain at least one column label with one or more conditions in cells below it.

Example: If A3:E9 contains the database above, the following will display 54000, the average of all salaries of employees over 30. 

	ws.cells("B30").formula = "=Daverage(A3:E9,""Salary"",A14:A15)"
Note that the embedded string "Salary" is within an additional set of double-quotes. ASP syntax requires that double-quotes embedded in a string be doubled. Alternatively, use single quotes.
Dcount*

Returns the number of cells in a column or database containing values that meet specified criteria. Dcount takes three arguments:

Database   A range of cells containing the database or list of values.
[Field]   The column containing the values to count. To specify Field, use the column name, or its number (use 1 for the first column in the database, 2 for the second, etc.). Optional. If you do not specify Field, Dcount will count all database values that meet the specified conditions.
Criteria   A range of cells containing the conditions that cell values must meet to be included in the list of values to count. The range must contain at least one column label with one or more conditions in cells below it.

Example: If A3:E9 contains the database above, the following will display the value 3, the number of employees over 30. 

	ws.cells("B30").formula = "=Dcount(A3:E9,""Age"",A14:A15)"
Note that the embedded string "Age" is within an additional set of double-quotes. ASP syntax requires that double-quotes embedded in a string be doubled. Alternatively, use single quotes.
Dcounta*

Returns the number of nonblank cells in a column or database containing values that meet specified criteria. Dcounta takes three arguments:

Database   A range of cells containing the database or list of values.
[Field]   The column containing the values to count. To specify Field, use the column name, or its number (use 1 for the first column in the database, 2 for the second, etc.). Optional. If you do not specify Field, Dcount will count all database values that meet the specified conditions.
Criteria   A range of cells containing the conditions that cell values must meet to be included in the list of values to count. The range must contain at least one column label with one or more conditions in cells below it.

Example: If A3:E9 contains the database above, the following will display the value 4, the number of salaries below 60000. 

	ws.cells("B30").formula = "=Dcounta(A3:E9,""Salary"",B11:B12)"
Note that the embedded string "Salary" is within an additional set of double-quotes. ASP syntax requires that double-quotes embedded in a string be doubled. Alternatively, use single quotes.
Dget*

Returns a single column value that meets specified conditions. If Dget finds more than one matching value, it returns the error #NUM!. If no matching value exists, Dget returns the error #VALUE!. Dget takes three arguments:

Database   A range of cells containing the database or list of values.
Field   The column containing the value to get. To specify Field, use the column name, or its number (use 1 for the first column in the database, 2 for the second, etc.).
Criteria   A range of cells containing the conditions that a cell value must meet. The range must contain at least one column label with one or more conditions in cells below it.

Example: If A3:E9 contains the database above, the following will display the value "Development," the only department containing an employee under age 30. 

	ws.cells("B30").formula = "=Dget(A3:E9,""Department"",B14:B15)"
Note that the embedded string "Department" is within an additional set of double-quotes. ASP syntax requires that double-quotes embedded in a string be doubled. Alternatively, use single quotes.
Dmax*

Returns the highest value in a column that meets specified conditions. Dmax takes three arguments:

Database   A range of cells containing the database or list of values.
Field   The column to search for maximum value. To specify Field, use the column name, or its number (use 1 for the first column in the database, 2 for the second, etc.).
Criteria   A range of cells containing the conditions that the cell value must meet. The range must contain at least one column label with one or more conditions in cells below it.

Example: If A3:E9 contains the database above, the following will display the value 55000, the highest salary in the Development department. 

	ws.cells("B30").formula = "=Dmax(A3:E9,""Salary"",A17:A18)"
Note that the embedded string "Salary" is within an additional set of double-quotes. ASP syntax requires that double-quotes embedded in a string be doubled. Alternatively, use single quotes.
Dmin*

Returns the minimum value in a column that meets specified conditions. Dmin takes three arguments:

Database   A range of cells containing the database or list of values.
Field   The column to search for minimum value. To specify Field, use the column name, or its number (use 1 for the first column in the database, 2 for the second, etc.).
Criteria   A range of cells containing the conditions that the cell value must meet. The range must contain at least one column label with one or more conditions in cells below it.

Example: If A3:E9 contains the database above, the following will display the value 59000, the lowest salary in the Marketing department. 

	ws.cells("B30").formula = "=Dmin(A3:E9,""Marketing"",A11:A12)"
Note that the embedded string "Marketing" is within an additional set of double-quotes. ASP syntax requires that double-quotes embedded in a string be doubled. Alternatively, use single quotes.
Dproduct*

Returns the product of column values that meet specified conditions. Dproduct takes three arguments:

Database   A range of cells containing the database or list of values.
Field   The column containing the values to multiply. To specify Field, use the column name, or its number (use 1 for the first column in the database, 2 for the second, etc.).
Criteria   A range of cells containing the conditions that cell values must meet to be multiplied. The range must contain at least one column label with one or more conditions in cells below it.

Example: If A3:E9 contains the database above, the following will display the value 792, the product of 24 and 33, the ages of employees with salaries under 55000. 

	ws.cells("B30").formula = "=Dmax(A3:E9,""Age"",B17:B18)"
Note that the embedded string "Age" is within an additional set of double-quotes. ASP syntax requires that double-quotes embedded in a string be doubled. Alternatively, use single quotes.
Dstdev*

Returns standard deviation based on a sample, of column values that meet a specified condition. The standard deviation is a measure of how widely values are dispersed from the average value. Dstdev takes three arguments:

Database   A range of cells containing the database or list of values.
Field   The column containing the values from which Dstdev will estimate standard deviation. To specify Field, use the column name, or its number (use 1 for the first column in the database, 2 for the second, etc.).
Criteria   A range of cells containing the conditions that cell values must meet to be included in the list of values to average. The range must contain at least one column label with one or more conditions in cells below it.

Example: If A3:E9 contains the database above, the following will display the value 3.055050463, the standard deviation from the average age of employees over 30, based on a sample. 

	ws.cells("B30").formula = "=Dstdev(A3:E9,""Age"",A14:A15)"
Note that the embedded string "Age" is within an additional set of double-quotes. ASP syntax requires that double-quotes embedded in a string be doubled. Alternatively, use single quotes.
Dstdevp*

Returns standard deviation based on the entire population, of column values that meet a specified condition. The standard deviation is a measure of how widely values are dispersed from the average value. Dstdevp takes three arguments:

Database   A range of cells containing the database or list of values.
Field   The column containing the values from which Dstdevp will estimate standard deviation. To specify Field, use the column name, or its number (use 1 for the first column in the database, 2 for the second, etc.).
Criteria   A range of cells containing the conditions that cell values must meet to be included in the list of values to average. The range must contain at least one column label with one or more conditions in cells below it.

Example: If A3:E9 contains the database above, the following will display the value 2.494438258, the standard deviation from the average age of employees over 30, based on the entire population of arguments. 

	ws.cells("B30").formula = "=Dstdevp(A3:E9,""Age"",A14:A15)"
Note that the embedded string "Age" is within an additional set of double-quotes. ASP syntax requires that double-quotes embedded in a string be doubled. Alternatively, use single quotes.
Dsum*

Returns the sum of all column values that meet a specified condition. Dsum takes three arguments:

Database   A range of cells containing the database or list of values.
Field   The column containing the values to sum. To specify Field, use the column name, or its number (use 1 for the first column in the database, 2 for the second, etc.).
Criteria   A range of cells containing the conditions that cell values must meet to be included in the list of values to sum. The range must contain at least one column label with one or more conditions in cells below it.

Example: If A3:E9 contains the database above, the following will display the value 85000, the sum of all salaries under 55000. 

	ws.cells("B30").formula = "=Dsum(A3:E9,""Salary"",B17:B18)"
Note that the embedded string "Salary" is within an additional set of double-quotes. ASP syntax requires that double-quotes embedded in a string be doubled. Alternatively, use single quotes.
Dvar*

Returns the estimated variance in a population of column values that meet specified conditions, based on a sample. Dvar takes three arguments:

Database   A range of cells containing the database or list of values.
Field   The column containing the values for which to estimate variance. To specify Field, use the column name, or its number (use 1 for the first column in the database, 2 for the second, etc.).
Criteria   A range of cells containing the conditions that cell values must meet to be included in the list of values for which to estimate variance. The range must contain at least one column label with one or more conditions in cells below it.

Example: If A3:E9 contains the database above, the following will display 9.33333333, the estimated variance in ages over 30, assuming that the database contains only a sample of the population of employees. 

	ws.cells("B30").formula = "=Dvar(A3:E9,""Age"",A14:A15)"
Note that the embedded string "Age" is within an additional set of double-quotes. ASP syntax requires that double-quotes embedded in a string be doubled. Alternatively, use single quotes.
Dvarp*

Returns the variance in a population of column values that meet specified conditions, based on the entire population. Dvarp takes three arguments:

Database   A range of cells containing the database or list of values.
Field   The column containing the values for which to calculate variance. To specify Field, use the column name, or its number (use 1 for the first column in the database, 2 for the second, etc.).
Criteria   A range of cells containing the conditions that cell values must meet to be included in the list of values for which to calculate variance. The range must contain at least one column label with one or more conditions in cells below it.

Example: If A3:E9 contains the database above, the following will display 6.22222222, the true variance in ages over 30, assuming that the database contains the entire population of employees. 

	ws.cells("B30").formula = "=Dvarp(A3:E9,""Age"",A14:A15)"
Note that the embedded string "Age" is within an additional set of double-quotes. ASP syntax requires that double-quotes embedded in a string be doubled. Alternatively, use single quotes.
*This function is not available in ExcelWriterSE, ExcelWriterLE, or ExcelWriterFree. The only functions available in ExcelWriterSE, ExcelWriterLE, and ExcelWriterFree are sum and average.


Copyright © 2005, SoftArtisans, Inc.