Question

10 on the info worksheet, you want to insert a database function based on conditions. display the info worksheet. in cell b2, insert the dsum function to calculate the total tuition for junior and senior accounting students. use the range a1:i42 from the students worksheet for the database, tuition for the field, and the criteria range on the students worksheet. 11 you want to insert database functions to perform calculations. in cell b3, insert the daverage function to calculate the average gpa for junior and senior accounting students on the students worksheet. format the range b3:b6 with comma style. 12 you want to identify the highest gpas for junior and senior accounting majors. in cell b4, insert the dmax function to identify the highest gpa for junior and senior accounting students on the students worksheet. 13 in cell b5, insert the dmin function to identify the lowest gpa for junior and senior accounting students on the students worksheet. 14 in cell b6, insert the dcount function to count the number of junior and senior accounting students on the students worksheet. 15 in cell b9, insert the dget function to retrieve the last name of the student who has the id listed in cell a9. use the column number representing the last name column for the field argument and use the criteria range a8:a9. edit the function to make the column letters absolute. copy the dget function from cell b9 to cell c9. edit the field number to represent the gpa column. in cell a9, enter 55128275. 16 you want to identify the student's last name that corresponds when you enter a student id. in cell b13, insert the index function with a nested xmatch function. use the range students!d$2:h$42 as the array. the xmatch function uses the student id in cell b$12 on the info worksheet to find the position within the range students!a$2:a$42. use 1 in the column num argument. use mixed references as indicated. 17 copy the function in cell b13 to cell b14. in cell b14, edit the function to change the column number to 4 to retrieve the student's major. copy the function from cell b14 to cell b15. in cell b15, edit the function to change the column number to 5 to retrieve the student's gpa. 18 you want to test the index functions by changing the student id. in cell b12, type 47305021. the results of the match and index functions should change. 19 you want to insert a function to display other functions as text. in cell d2, insert the formulatext function to display the formula that is stored in cell b2. copy the function to the range d3:d6 and to the range d13:d15. in cell d9, insert the formulatext function to display the function that is stored in cell c9. 20 increase the width of column d to 63.

Answer 1

Excel database functions such as DSUM, DAVERAGE, DMAX, DMIN, and DCOUNT allow for conditional calculations based on specified criteria. The DGET function can retrieve specific data entries, while the INDEX function combined with XMATCH provides a method to lookup data. FORMULATEXT is used to display the formula from one cell as text into another cell.

Step-by-step explanation:

How to Use Database Functions in Excel

Working with database functions in Excel allows users to execute conditional calculations. For example, in cell B2, the DSUM function can be used to calculate the total tuition for junior and senior accounting students. The syntax would look something like this:

=DSUM(Students!A1:I42, "Tuition", CriteriaRange)

This formula would be using a criteria range that specifies the conditions 'Junior' and 'Senior' for the 'Year' field, and 'Accounting' for the 'Major' field. Similarly, other functions like DAVERAGE, DMAX, DMIN, and DCOUNT perform average, maximum, minimum, and count calculations under specified criteria, respectively.

Additionally, using the DGET function in cell B9 retrieves the last name of a student whose ID is specified in cell A9. If you wanted to make a column letter absolute in the formula, you would use dollar signs like so:

=DGET(Students!A1:I42, "$G", A8:A9)

where "$G" represents an absolute reference to the column containing last names.

Further, the INDEX function combined with the XMATCH function allows for the lookup of data in a specified array. The FORMULATEXT function is used to display formulas as text in another cell, which is helpful for auditing and documentation purposes.