d. Apply the Accounting number format with zero decimal places to the newly created column (if necessary).
e. Add a Total Row to the Office table, which automatically totals the amounts in the Current Value column.
f. In cell C30, use the total row to display the count of the items.
11. Michael has created an area in the range J1:L4 for looking up data in the Office table. First, he wants to find the purchase price for a specific item. He has already entered the item ID in cell J2. Look up the price of the item with Item ID 332 as follows:
a. In cell J4, begin to enter a formula using the VLOOKUP function.
b. Use the Item ID in cell J2 as the value to look up.
c. Use a structured reference to the Office table as the table to search.
d. Display the value in the Purchase Price column (column 6) of the table.
e. Specify an exact match (FALSE) for the range_lookup argument.
12. Michael also wants to calculate the total cost of replacing items in a specific category. He has already entered the category number in cell K2. Calculate the total cost of replacing items in Category 2 (office equipment) as follows:
a. In cell K4, begin to enter a formula using the DSUM function.
b. Use a structured reference to the Office table (beginning in cell A7) including the headers and the data as the formula database.
c. Use a structured reference to the Purchase Price field header as the field to summarize.
d. Use the values Michael set up in the range K1:K2 as the criteria.
13. The third calculation Michael wants to make is to identify the number of replacement items, which are items in poor condition. Calculate the number of items in poor condition as follows:
a. In cell L4, begin to enter a formula using the DCOUNTA function.
b. Use a structured reference to the Office table (beginning in cell A7) including the headers and the data as the formula database.
c. Use a structured reference to the Condition field header as the field to count.
d. Use the values Michael set up in the range L1:L2 as the criteria.
14. Michael wants to list the items in each category that have a current value of $250 or more to identify items that are worth more than his current homeowners insurance