|
|
Example for database functions
The example may be easier to understand if you copy it to a blank spreadsheet.
- Create a blank spreadsheet.
- Select the example in the Help topic.
Selecting an example from Help
- Press CTRL+C.
- In the spreadsheet, select cell A1, and press CTRL+V.
- To switch between viewing the formula that returns the result and the result in the cell, select the cell and press F2 and then ENTER, or click Commands and Options on the spreadsheet toolbar, click the Formula tab, and look in the Formula in active cell (active cell) box.
| Tree | Height | Age | Yield | Profit | Height |
|---|---|---|---|---|---|
| Apple | >10 | <16 | |||
| Pear | |||||
| Tree | Height | Age | Yield | Profit | |
| Apple | 18 | 20 | 14 | 105.00 | |
| Pear | 12 | 12 | 10 | 96.00 | |
| Cherry | 13 | 14 | 9 | 105.00 | |
| Apple | 14 | 15 | 10 | 75.00 | |
| Pear | 9 | 8 | 8 | 76.80 | |
| Apple | 8 | 9 | 6 | 45.00 | |
| Formula | Description (Result) | ||||
| =DCOUNT(A4:E10,"Age",A1:F2) | This function looks at the records of apple trees between a height of 10 and 16 and counts how many of the Age fields in those records contain numbers. (1) | ||||
| =DCOUNTA(A4:E10,"Profit",A1:F2) | This function looks at the records of apple trees between a height of 10 and 16 and counts how many of the Profit fields in those records are not blank. (1) | ||||
| =DMAX(A4:E10,"Profit",A1:A3) | The maximum profit of apple and pear trees. (105) | ||||
| =DMIN(A4:E10,"Profit",A1:B2) | The minimum profit of apple trees over 10 in height. (75) | ||||
| =DSUM(A4:E10,"Profit",A1:A2) | The total profit from apple trees. (225) | ||||
| =DSUM(A4:E10,"Profit",A1:F2) | The total profit from apple trees with a height between 10 and 16. (75) | ||||
| =DPRODUCT(A4:E10,"Yield",A1:B2) | The product of the yields from apple trees with a height greater than 10. (140) | ||||
| =DAVERAGE(A4:E10,"Yield",A1:B2) | The average yield of apple trees over 10 feet in height. (12) | ||||
| =DAVERAGE(A4:E10,3,A4:E10) | The average age of all trees in the database. (13) | ||||
| =DSTDEV(A4:E10,"Yield",A1:A3) | The estimated standard deviation in the yield of apple and pear trees if the data in the database is only a sample of the total orchard population. (2.97) | ||||
| =DSTDEVP(A4:E10,"Yield",A1:A3) | The true standard deviation in the yield of apple and pear trees if the data in the database is the entire population. (2.65) | ||||
| =DVAR(A4:E10,"Yield",A1:A3) | The estimated variance in the yield of apple and pear trees if the data in the database is only a sample of the total orchard population. (8.8) | ||||
| =DVARP(A4:E10,"Yield",A1:A3) | The true variance in the yield of apple and pear trees if the data in the database is the entire orchard population. (7.04) | ||||
| =DGET(A4:E10,"Yield",A1:A3) | Returns the #NUM! error value because more than one record meets the criteria. |
- You can use any range for the criteria argument, as long as it includes at least one column label and at least one cell below the column label for specifying the condition. For example, if the range G1:G2 contains the column label Income in G1 and the amount 10,000 in G2, you could define the range as MatchIncome and use that name as the criteria argument in the database functions.
- In most cases, your criteria range should not overlap the list.
- To perform an operation on an entire column in a database, enter a blank line below the column labels in the criteria range.
Multiple conditions in a single column
If you have two or more conditions for a single column, type the criteria directly below each other in separate rows. For example, the following criteria range displays the rows that contain either "Davolio," "Buchanan," or "Suyama" in the Salesperson column.
| Salesperson |
|---|
| Davolio |
| Buchanan |
| Suyama |
One condition in two or more columns
To find data that meets one condition in two or more columns, enter all the criteria in the same row of the criteria range. For example, the following criteria range displays all rows that contain "Produce" in the Type column, "Davolio" in the Salesperson column, and sales values greater than $1,000.
| Type | Salesperson | Sales |
|---|---|---|
| Produce | Davolio | >1000 |
One condition in one column or another
To find data that meets either a condition in one column or a condition in another column, enter the criteria in different rows of the criteria range. For example, the following criteria range displays all rows that contain either "Produce" in the Type column, "Davolio" in the Salesperson column, or sales values greater than $1,000.
| Type | Salesperson | Sales |
|---|---|---|
| Produce | ||
| Davolio | ||
| >1000 |
One of two sets of conditions for two columns
To find rows that meet one of two sets of conditions, where each set includes conditions for more than one column, type the criteria in separate rows. For example, the following criteria range displays the rows that contain both "Davolio" in the Salesperson column and sales values greater than $3,000, and also displays the rows for salesperson Buchanan with sales values greater than $1,500.
| Salesperson | Sales |
|---|---|
| Davolio | >3000 |
| Buchanan | >1500 |
More than two sets of conditions for one column
To find rows that meet greater than two sets of conditions, include multiple columns with the same column heading. For example, the following criteria range displays sales that are between 5,000 and 8,000 in addition to sales that are less than 500.
| Sales | Sales |
|---|---|
| >5000 | <8000 |
| <500 |