- 28 Mar, 2021
- 0 Comments
- 2 Mins Read
Excel Module 6 SAM Project B
⦁ Go to the August Advertising worksheet, which lists all the articles published in August. Jack wants to display the data by article type and then list the projects by publication date.
Sort the data in the table in ascending order first by article type and then by publication date.
⦁ Jack also wants to calculate ad amount subtotals for each article type:
⦁ Convert the table to a range.
⦁ Insert a subtotal at each change in the Article Type value.
⦁ Use the Sum function to calculate the subtotals.
⦁ Add subtotals to the Ad Amt values only.
⦁ Include a summary below the data.
⦁ Collapse the outline to display only the subtotals for each article type and the grand total.
⦁ Go to the Lookup worksheet, which lists article details, including the ID code that Jack uses to refer to the articles. He wants to find a simple way to look up an article title name based on its ID.
Create a formula that provides this information as follows:
⦁ In cell H3, begin to enter a formula using the VLOOKUP function.
⦁ Use the Article ID (cell H2) as the lookup value.
⦁ Use the Lookup table (range A2:E37) as the table_array.
⦁ Use the Article Title column (column 2) as the col_index_num.
⦁ Specify an exact match (FALSE) for the range_lookup.
⦁ Jack also wants to look up the publication date of each article. Instead of using the VLOOKUP function, he suggests using the INDEX and MATCH functions, which are faster with large amounts of data.
Create a formula that provides the publication date of an article as follows:
⦁ In cell H4, begin to enter a formula using the INDEX function.
⦁ Use the Lookup table (range A2:E37) as the array.
⦁ For the row_num argument, use the MATCH function.
⦁ Use the Article ID (cell H2) as the lookup_value for the MATCH function.
⦁ Use the ID column (range A2:A37) as the lookup_array for the MATCH function.
⦁ Specify an exact match (0) for the MATCH function.
⦁ Use the Publication Date column (column 4) as the column_num for the INDEX function.
⦁ Jack also wants to identify the number of articles that earned more than $500 of advertising and calculate the average ad amount for Analysis articles.
Create formulas that provide this information as follows:
⦁ In cell H8, create a formula using the DCOUNT function to count the number of articles with ad amounts more than $500, using the Lookup table (range A1:E37) as the database, “Ad Amt” as the field, and the range G6:G7 as the criteria.
⦁ In cell H13, create a formula using the DAVERAGE function to average the ad amounts for Analysis articles, using the Lookup table (range A1:E37) as the database, “Ad Amt” as the field, and the range G11:G12 as the criteria.
⦁ Your workbook should look like the Final Figures on the following pages. Save your changes, close the workbook, and then exit Excel. Follow the directions on the SAM website to submit your completed project.