How to find the average and sum of database values in Excel spreadsheets [Guide]

Excel is not the best software package to set up databases with. However, you can still set up effective flat-file (single table) databases with the application. Excel 2010/2013 includes database functions such as DAVERAGE and DSUM. With those you can find the average and sum of database values as specified in criteria.

For example, enter the values 45, 55, 663 and 6,322 in column G. At the top of the column you should also input scores as a column header. In column I enter scores and >500 in a cell directly below. Then your spreadsheet should match the one shown below.

DAVERAGE

Next, select a cell to include the DAVERAGE function in it. Click the Insert Function (fx) button to open the window in the shot below. There select the DAVERAGE function, and press the OK button.

DAVERAGE2

In the DAVERAGE window below, enter a database cell reference, field and criteira. Press the button beside the Database text box and select the column G cells that include the scores (including the column heading). Then enter 1 in the Field text box, which highlights that the database comprises of just one column. Next, press the button beside Criteria and select the scores and >500 cells in column I.

DAVERAGE3

Now press OK to close to the window. The function will return a value that shows you the average of all those scores above 500 as below. As such, the scores of 663 and 6,322 added together and divided by two amount to an average of 3,492.5.

DAVERAGE4

You can also find the total sum of all the scores in the database column above 500 with DSUM. Click a cell to add the function to, and press the fx button to open the Insert Function window. Then select DSUM to open the window in the shot directly below.

DAVERAGE5

Select exactly the same cell ranges as you did for the DAVERAGE function. In addition, also enter 1 in the Field text box. Press the OK button to close the window, and the cell will then add all the values in the database column above 500 as in the shot below. Thus, 663 + 6,332 amount to 6,985. Switching the criteria to <500 will add the cells with values less than 500.

DAVERAGE6

So with those database functions you can now find the totals and averages of cell values in spreadsheet tables based on a specific criteria. The functions covered here apply to Excel 2010/2013, Excel Starter and Excel Online; but might not be available in other versions of the application.

Related Posts

Comments

Leave a Reply

Your email address will not be published. Required fields are marked *