laire continues to analyze social media metrics and needs to evaluate what factors might result in a successful Facebook post. Claire has compiled a list of Facebook posts that advertise company specials and business news. Claire wants to know whether user engagement is higher when the posts include photos and coupon offers. She also wants to determine whether certain days of the week are better than others to engage the user. Finally, Claire is mindful that users might ignore long posts and wants to examine whether word count has an impact on user engagement. Claire grades post length according to the table in Figure $753.$

Figure $753$

Lengths of Facebook posts

Character Count

Post Size

$0<80$ characters

short

$80<160$ characters

medium

$160<240$ characters

long

$>=240$ characters

very long

Complete the following:

Open the NP$\_$EX$\_7-2.$xlsx workbook located in the Excel$7>$ Review folder included with your Data Files. Save the workbook as NP$\_$EX$\_7\_$Facebook in the location specified by your instructor.

In the Documentation sheet, enter your name and the date.

In the Lookup Tables worksheet, use data in Figure $753$ to complete the lookup table in the range B$4$:C$8,$ and then give the lookup table the defined name Character$\_$Lookup.

In the Media Posts worksheet, in column G of the Facebook table, apply the VLOOKUP function to categorize the size of each post, using the value of the Characters field for the lookup value, the Character$\_$Lookup table as the lookup table, and the second column of the table as the return value.

In the Report worksheet, count the number of posts with and without photos and coupons as follows:

In cell C$15,$ use the COUNTIF function to count the number of records in the Photo field of the Facebook table equal to the $$Photo$$ field value.

In cell C$16,$ calculate the difference between cell C$14$ and C$15.$

In cell C$18,$ count the number of records in the Coupon field equal to the $$Coupon$$ field value.

In cell C$19$ calculate the difference between cell C$14$ and C$18.$

Calculate the average engagement rate for posts with and without photos and coupons as follows:

In cell C$23,$ calculate the average engagement rate for posts with photos by using the AVERAGEIF function with the Range argument equal to Facebook$[$PHOTO$],$ the Criteria argument equal to $$Photo$,$ and the Average$\_$Range argument equal to Facebook$[$ENGAGEMENT RATE$].$

In cell C$24,$ calculate the average engagement rate for posts without posts by copying the formula in C$23$ and change the value of the Criteria argument to $$No Photo$.$

In cell C$26,$ calculate the average engagement rate for posts with coupons by using the AVERAGEIF function with the Range argument equal to Facebook$[$COUPON$],$ the Criteria argument equal to $$Coupon$,$ and the Average$\_$Range argument equal to Facebook$[$ENGAGEMENT RATE$].$

In cell C$27,$ calculate the average engagement rate for posts without coupons by copying the formula in C$26$ and change the value of the Criteria argument to $$No Coupon$.$

Create a PivotTable of the data in the Media Posts worksheet, placing the PivotTable in cell E$12$ of the Report worksheet.

Make the following changes to the PivotTable:

Rename the PivotTable as Day Pivot.

Place the Day field in the Rows area of the table, and then place the Post and Engagement Rate fields in the Values area. $($Posts are in the left column of the PivotTable.$)$

Change the label for the Count of Posts value field to Posted. Change the label for the Sum of Engagement Rate value field to Engagement Rates and display the average engagement rate as a percentage formatted to two decimal places.

Apply the Light Green, Pivot Style Medium $7$ to the PivotTable and turn on Banded Rows and Banded Columns.

Modify the PivotTable options so that the Excel does not AutoFit column widths on update.

Copy the PivotTable in the range E$12$:G$20$ and paste it in cell E$22,$ and then make the following changes to the PivotTable:

Rename the PivotTable as Size Pivot.

Replace the Day field in the Rows area with the Post Size field.

Reorder the categories in the Rows area to the following order: short, medium, long, very long.

In the Media Posts worksheet, create a PivotChart from the data, placing the PivotTable$/$PivotChart in the Engagement Types worksheet in cell B$4,$ and then do the following:

Rename the PivotTable as Engaged Pivot.

Place the Clicks, Likes, Comments, and Shares field in the Values area box.

Rename the value fields as Clicked, Liked, Commented, and Shared.

Move the $$Values item into the Rows area box so that all values fields are displayed in separate rows.

Make the following changes to the PivotChart:

Move the PivotChart to the Report worksheet, and then resize it to cover the range I$12$:L$18.$

Change the chart type to a pie chart.

Remove the field buttons and the chart title from the chart.

Change the chart style to Style $8.$

Create another PivotChart from the data in the Facebook table, place it in the Engageme