Database Functions in Excel

Database functions are formulas used in spreadsheets to calculate amounts based on tables of information that meet certain criteria. For example, this type of formula may be used to calculate the total salary above €20,000 of employees in a table. The DSUM function adds numbers in a table of data based on specified criteria. The DMAX, DMIN, DCOUNT & DAVERAGE functions are also used to calculate amounts in a table based on specified criteria.

Dsum

This function adds numbers within a database depending on specified criteria. A database in a worksheet is a range of cells that contain numerical information.

1. Download the Advanced ICDL Spreadsheets work files folder from www.digidiscover.com/downloads

2. Open the Database Function workbook

3. On the Formula tab in the Function Library select Insert Function

4. Select the Database category

5. Choose Dsum

6. Click OK

7. Save the workbook and keep it open

8. Select A3:E10 for the Database textbox

9. In the Field textbox type in D3

10. In the Criteria textbox, select D4:D10

11. Click OK

12. Save the workbook and leave it open

Dmin

This function finds the minimum value out of a selected range of cells depending on specified criteria. This is useful as it can find the lowest value from a large selection of cells in a range of data in a table.

1. With the Database Function workbook open, on the Formula tab in the Function Library select Insert Function

2. Select the Database category

3. Choose Dmin

4. Click OK

5. Enter the following information into each textbox:

6. Click OK

7. Leave the workbook open

Dmax

This function finds the maximum value out of a range of selected cells in a database. This will be calculated depending on criteria specified in the function e.g. find the maximum value in a range based on certain values.

1. Open the Database Function workbook

2. On the Formula tab in the Function Library select Insert Function

3. Select the Database category

4. Choose Dmax

5. Click OK

6. Enter the following information into each textbox:

7. Click OK

8. Save the workbook

Dcount

This function counts the number of cells depending on certain criteria. For instance, if you want to find the range of cells that are below a certain number, this function will count all of the cells that are below this specified number.

1. Open the Database Function workbook

2. On the Formula tab in the Function Library select Insert Function

3. Select the Database category

4. Choose Dcount

5. Click OK

6. Enter the following information into each textbox:

7. Click OK

8. Save the workbook

Daverage

This function averages the values in a column in a list of database that meet specified conditions, e.g. average all of the numbers that are above 30 in a list.

1. Open the Database Function workbook

2. On the Formula tab in the Function Library select Insert Function

3. Select the Database category

4. Choose Daverage

5. Click OK

6. Enter the following information into each textbox:

7. Click OK

8. Save the workbook and close it

To learn more about advanced spreadsheet features check out the Advanced ICDL Spreadsheets paperback available on Amazon: