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.


img167
img168
img169
img170
img171
img172
img173
img174
img175
img176
img177
img178
img179
img180
img181
img182
img183
img184
img185
img186
img187
img188
img189
img190
img191
img192
img193

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.
Daverage 13