Excel isn’t the best application for setting up databases. However, you can still set up smaller flat-file (single table) databases with the software. It includes a variety of database functions among which are DMAX and DMIN. Those functions return the maximum and minimum values from a database based on specified criteria.
First, enter a small database table into a spreadsheet. As an example, just copy the one shown in the shot below. With the DMAX function you can find the highest A Team score value.
Next, you’ll need to specifiy the criteria for the DMAX function in a couple of cells. Enter Team in one column and A Team in the cell directly below. Then the spreadsheet should match the one below.
Now select a cell to add the DMAX function to. Press fx and select DMAX to open the window below. There you need to enter a database reference, the field that includes the values and criteria for the function. So the function’s syntax is DMAX(database, field, criteria).
Press the button beside Database and select the database table cells, including column headings, in the spreadsheet. Enter score in the Field text box. Then press the button beside Criteria and select the column you entered Team and A Team in.
Press OK to close the window and add function to spreadsheet. The DMAX function cell will now return the value 434. That’s the highest score value for the A Team in the database table.
DMIN is the antithesis of DMAX. It finds the minimum value in a spreadsheet column for a specified criteria. Choose a cell to add function to, click fx and select DMIN to open the window below.
DMIN is much the same as the DMAX function. Just enter exactly the same cell references and score field in the text boxes as before. Then the function cell will return the minimum A Team score value, which is 244.
DMAX and DMIN will certainly come in handy for analysing data in spreadsheet tables. Open this video for further details on how you can add DMAX and DMIN to Excel 2010/13 spreadsheets.