1. Add a Total Row to the Discounts table, which automatically counts the number of Promo Ad values. Using the total row, display the sum of the Current Price and Discount Price amounts.
2. Camille wants to compare the discount prices visually. In the Discount Price column (range G2:G16), create a new Data Bars conditional formatting rule using Green Gradient Fill data bars.
3. Camille also wants to summarize the number of discounted products by product type and calculate their total and average discounted prices. In cell K2, enter a formula using the COUNTIF function that counts the number of Carrier products, using Discounts[Product Type] as the range and cell J2 as the criteria. Fill the range K3:K9 with the formula in cell K2. In cell L2, enter a formula using the SUMIF function that totals the discount prices for Carrier products, using Discounts[Product Type] as the range, cell J2 as the criteria, and Discounts[Discount Price] as the sum_range. Fill the range L3:L9 with the formula in cell L2. In cell M2, enter a formula using the AVERAGEIF function that averages the discount prices for Carrier products, using Discounts[Product Type] as the range, cell J2 as the criteria, and Discounts[Discount Price] as the average_range. Fill the range M3:M9 with the formula in cell M2.
4. Go to the October Ads worksheet, which lists all the digital ads released in October. Sort the data in the table in ascending order first by channel and then by release date.
5. Camille also wants to calculate ad amount subtotals for each channel. Convert the table to a range. Insert a subtotal at each change in the Channel value, using the Sum function to calculate the subtotals. Add subtotals to the Ad Amt values only, and include a summary below the data. Collapse the outline to display only the subtotals for each channel and the grand total.
6. Go to the Ad Lookup worksheet, which lists all the ads released in October and planned for November. Camille wants to look up the release date of each ad. In cell H3, enter a formula using the INDEX function. Use a structured reference to the AdLookup table (range A2:E36) as the array. For the row_num argument, use the MATCH function. Use the Ad ID (cell H2) as the lookup_value for the MATCH function. Use a structured reference to the ID column (range A2:A36) as the lookup_array for the MATCH function. Specify an exact match (0) for the MATCH function. Use the Release Date column number (column 4) as the column_num for the INDEX function.
7. Camille also wants to identify the number of ads that cost more than $1000. In cell H7, create a formula using the DCOUNT function to count the number of ads that cost more than $1000, using a structured reference to all the columns in the AdLookup table (range A1:E36) as the database, "Ad Amt" as the field, and the range G5:G6 as the criteria.
8. Finally, Camille wants to calculate the average ad amount for the Social media channel. In cell H12, create a formula using the DAVERAGE function to average the ad amounts for the Social media channel, using a structured reference to all the columns in the AdLookup table (range A1:E36) as the database, "Ad Amt" as the field, and the range G10:G11 as the criteria.