Tables for automating data manipulation Test your skills

Have a look at the Discount Code data presented in Columns O and P. Convert the Discount code data to a table. Add a new record directly below the Table as follows: Code: C , %: 15%.

What is the corrected Average Discount % shown in P19. (Do not enter the % symbol, just the numbers using the format #.##)

Turn on the total row. Change the calculation for Total Retail price to Average Retail Price.

What is the Average Retail Price? (Do not enter the $ symbol just the numeric value, e.g. 99.99)

In the Total Row add a calculation in the In Stock column to get the total number of items in stock.

What is the total number of items in stock?

In the Total Row, move across to Discount % and choose StDev from the drop-down list.

What is the standard deviation for the discount percentage (to the nearest whole number with no % sign)?

You need to find out which products need re-ordering most urgently. Sort the data by the column In Stock from Smallest to Largest.

Enter the product code for the product with the smallest number in stock, not counting zero items.

You are doing an audit of products supplied by the company Wu Bo Luo. Filter the data by the supplier Wu Bo Luo. What is the updated value for Total In Stock now?

Clear the filter on Supplier and add a new filter to get the top 5% of stock items by retail price. (Hint: There is a specific filter to get the Top 5%).

What is the Average Retail Price for these items? (Enter without currency symbol e.g. 999.99)

Clear all filters and turn off the total row.

In the first empty row add the following record:

• HARD-062
• Chrome-Plated Face Frame Hinge
• WU BO LUO
• Hardware
• China
• Alexandria
• 02
• 200
• 200
• 50
• =Calc!A53
• $6.30
• $7.30

Turn the Total Row back on.

What is the updated Average Retail Price?