Excel Database Functions

The surprising thing is, that in order to use the database functions you have to create little snippets of tables, that contain at least one column header cell and at least one contents cell, in order to get the database functions, which refer to these snippets, to work.

Sort of reminds you of finger painting you would think the snippets would be unnecessary, that the info derived from them could be directly written into the database functions.

The snippets the database functions refer to can be hidden and unhidden though.

DCount
http://office.microsoft.com/en-us/excel/HP052090491033.aspx

Dcount(a,b,c) looks at the rows specified by c (criteria) in a (database) to see how many have a number in the cell or field in the b (field) column.

Daverage http://office.microsoft.com/en-us/excel/HP052090451033.aspx
Daverage(a,b,c) gets the average of the b (field) column in the a (database) table, focusing on elements in the b column that are specified by c (criteria). Note the (database,field,criteria) terms used by MS leave one mystified.

Dget http://office.microsoft.com/en-us/excel/HP052090591033.aspx

Dget(a,b,c) gets the value of the b (field) column in the a (database) table, focusing on elements in the a (database) that are specified by c (criteria table). If more than one row is returned the num error is returned, if no value is returned, the value error message is returned.

DcountA
http://office.microsoft.com/en-us/excel/HP052090501033.aspx
Dcounta(a,b,c) looks at the rows specified by c (criteria) in a (database) to see how many have a non-blank cell or field in the b (field) column.

Tree	Height	Age	Yield	Profit	Height
=apple					>15
=Pear
Tree	Height	Age	Yield	Profit
Apple	18	20	14	105
Pear	12	12	10	96
Cherry	13	14	9	105
Apple	14	15	10	75
Pear	9	8	8	76.8
Apple	8	9	6	45

		Formula comments

1		DCOUNT(A4:E10,"Age",A1:F2) In the a4e10 range, looks at age col to determine number rows where there is apple tree between two given values, or greater or lesser than a value.				14	DGET(A4:E10,"Yield",A1:A3) Tthis is supposed to yield the num error message because more than one row contains the thing searched for. If no match is found it returns the #value! Error message. If a match is found, and there is only one match, it returns... if a match is found, it will give the yield of the row in the database, that satisfies the criteria specced in the criteria table.





3		DCOUNT(A4:E10,"Age",A1:F3) In the a4e10 range, looks at age column to report number of rows where there is an apple tree and or a pear tree between 2 given values, or greater or lesser than a value.



						1	DCOUNTA(A4:E10,"Profit",A1:F2) Looks at the rows specced by the criteria table mentioned thid in the database table mentioned first, to see how many have a non-blank cell or field in the profit column.

10		DAVERAGE(A4:E10,"Yield",A1:B2) This should be the average yield of the apple trees in the table that have a height of between 2 values or greater or lesser than a value.