Exam 11: Exploring Pivottable Design

A manager creates data for cost of products of the firm he works for. He enters headings in cells A1, B1, C1 and D1 as Order ID, Quantity, Unit Cost and Total Cost respectively. He mentions the criteria >1000 and >=5 in cells A2 and B2 respectively. Cells A3 to A10 with heading "Order ID" in A4 cell contain data for the order IDs, cells B3 to B10 with heading "Quantity" in B3 cell contain data for quantities for each order ID, cells C3 to C10 with heading "Unit Cost" in C3 cell contain data for unit cost of each order ID and cells D3 to D10 with heading "Total Cost" in D3 cell contain data for total cost of each order ID. He can use the formula: =DCOUNT(A4:D10, "Unit Cost", A1:B2) to count the occurrences that meet the conditions in A1:B2.

Ned wants to calculate the total sale of a product during quarters 3 and 4 of a financial year. He creates a table of quarterly sales of different products o the company in different regions. In the table, cells B1 to B20 with heading "Quarter" list the quarters of the years I to IV, cells C1 to C20 with heading "Product" lists the different products and cells D1 to D20 with heading "Sales" list the sales amount for each quarter and product in dollars. The criteria specified-Quarter and Product are in cells H5 and I5 and their values >3 and Balloons respectively are mentioned in cells H6 and I6. To calculate the total sale of a particular product, he can use the formula =DSUM(B1:D20,"Sales",H5:I6).