Example for database functions

MS Excel Spreadsheet

The example may be easier to understand if you copy it to a blank spreadsheet.

ShowHow?

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.

ShowTips

ShowExamples of criteria