Constructing DAVERAGE
We will now work out the average gross margin:
-
Click into cell G27 on the DATABASE worksheet (using the Database.xlsx
workbook).
- Construct the formula =DAVERAGE(Inventory[All#],7,$G$2:$G$3) to work out the average gross margin above 50%.
Figure 10.28 – Formula construction for the average gross margin above 50%
- Press Enter to see the formula result of 74.71%. Now we can change the value in cell G3 to see any further updates required.
See whether you can work out the maximum total cost for Division 2, the total quantity sold for Division 1, and the minimum quantity sold for Division 3, using the appropriate function for each. Below is guidance as to the formula you will need to construct and the displayed result for each cell:
Figure 10.29 – The DMIN, DSUM, and DMAX functions explained
We can expand on our database functions a little more by adding AND/OR criteria. Let’s look at two examples.
Using the AND/OR criteria
In the following steps, we will build a DSUM function to locate the sales figure for the year 1992 and Matts Winery, and with a date range greater than or equal to 01/01/2019 and less than or equal to 31/12/2020:
- Continuing with the Database.xlsx workbook, make sure you have clicked on the MULTIPLE CRITERIA worksheet.
- The database table is located in cells A1:I145. To build the AND criteria, we will include the column headers along with the relevant criteria in the same row.
- Click into cell K1 and then add the columns and criteria as per the following table:
Table 10.6 – Columns and criteria to get ready for the DSUM function
- We are now ready to construct the formula in cell O2.
- Type =DSUM( and then select the database range A1:I1445, including the headings. Insert a comma to move to the next argument.
Figure 10.30 – DSUM formula construction
-
The field argument we are returning values from is the Sales column. Either type a 9
for the column number within the database range, or “Sales”.
- Lastly, select the column headings and their criteria in cells K1:N2. Don’t forget to make the range absolute.
- Press the Enter key to see the sum of sales for Matts Winery and the year 1992 and between the date range 01/01/2019 and 31/12/2020.
- We will now add another row to the criteria table to include the OR criteria.
- As we would like to locate the sales for Matts Winery 1991 between the date range listed, OR prominent wines for 1992, we would need to amend the year in K2 to 1991. In K3, add the year 1992 and Prominent Wines in cell L3.
- Lastly, amend the formula in cell O2 to include the second row in the criteria selection so that it includes the OR criteria.
Figure 10.31 – OR condition DSUM formula
- Press Enter to update the formula in O2.
