Advanced Spreadsheets – Database Functions
Use database functions: dsum, dmin, dmax, dcount, daverage.
In the same ‘Function Wizard’, under the ‘Database’ category, we have some database functions. One of these functions is ‘DSUM’. This function adds all the cells of a data range where the contents match the search criteria. This function takes 3 arguments, ‘Database’, ‘Database field’, and ‘Search criteria’. ‘Database’ gives the range of cells containing the data. ‘Database field’ gives the database column which needs to be used for the search criteria. ‘Search criteria’ defines the cell range containing the search criteria.
Let us take an example; let the ‘Database’ contains some fields as have been shown below.
Fruit | Weight | Cost | Profit |
Apple | 5 | 6 | 4 |
Pear | 4 | 8 | 3 |
Pineapple | 8 | 2 | 1 |
Mango | 2 | 9 | 3 |
Also, let the ‘Search criteria’ be defined as given below.
Fruit | Weight | Cost | Profit |
=Apple | |||
=Pear |
So, when these inputs are used, and the ‘Database field’ is set as ‘Profit, the result will come out to be ‘7’. This is because the entire database is evaluated and it is found that only two rows satisfy the criteria, row 2 and row 3. So after this the sum of the values of ‘Profit’ column is taken for these two rows, which comes out to be 4+3 = 7. This is how ‘DSUM’ works.
We also have the ‘DMIN’ and ‘DMAX’ functions in the same category. ‘DMIN’ returns the minimum of all the cells of a data range where the contents correspond to the search criteria. ‘DMAX’ does the same thing but it returns the maximum value. Both these functions use the same three arguments which have been described above.
Let us take an example, using the same data; we will get the result of ‘DMIN’ to be ‘3’ and for ‘DMAX’ to be ‘4’.
‘DCOUNT’ counts the cells of a data range whose contents match the search criteria. This function also uses the same three arguments and the working of this function is also exactly similar to the ‘DSUM’ or the ‘DMIN’ or the ‘DMAX’ functions.
‘DAVERAGE’ returns the average value of all the cells of a data range whose contents match the search criteria. This function also uses the same three arguments and the working of this function is also exactly similar to the ‘DSUM’ or the ‘DMIN’ or the ‘DMAX’ functions.