Introduction

The IMDB $($Assignment$2.$xIsx$)$ data shows the top $1000$ movies rated on the website IMDB. You will be describing the data using summary and statistics functions as well as creating a userfriendly worksheet to display information based on what a user selects.

Download Assignment$2.$xlsx for the assignment. Follow the instructions below carefully.

Section $1$: Data Preparation

The first task is to apply the text manipulation skills from Chapter $6$ to break up column F $($Genre$)$ into the types Comedy, Animation, Romance, and Horror.

a$.$ Insert four columns after column F $($Genre$)$ and name a column Comedy, Animation, Romance, and Horror.

b$.$ For each column use ISNUMBER$()$ and SEARCH$()$ to find if a cell contains the genre type that the column is named.

c$.$ It should look like this:

$\backslash$table$[[$G$,H,$I,J$,$K$],[$Genre$,$Comedy,Animation,Romance,Horror$],[$Drama$,$FALSE,FALSE,FALSE,FALSE$],[$Crime$,$ Drama,FALSE,FALSE,FALSE,FALSE$],[$Action$,$ Crime, Drama,FALSE,FALSE,FALSE,FALSE$],[$Crime$,$ Drama,FALSE,FALSE,FALSE,FALSE$],[$Crime$,$ Drama,FALSE,FALSE,FALSE,FALSE$],[$Action$,$ Adventure, Drama,FALSE,FALSE,FALSE,FALSE$],[$Crime$,$ Drama,FALSE,FALSE,FALSE,FALSE$],[$Biography$,$ Drama, History,FALSE,FALSE,FALSE,FALSE$],[$Action$,$ Adventure, Sci$-$Fi$,$FALSE,FALSE,FALSE,FALSE$],[$Drama$,$FALSE,FALSE,FALSE,FALSE$],[$Action$,$ Adventure, Drama,FALSE,FALSE,FALSE,FALSE$],[$Drama$,$ Romance,FALSE,FALSE,TRUE,FALSE$],[$Western$,$FALSE,FALSE,FALSE,FALSE$],[$Action$,$ Adventure, Drama,FALSE,FALSE,FALSE,FALSE$],[$Action$,$ Sci$-$Fi$,$FALSE,FALSE,FALSE,FALSE$],[$Biography$,$ Crime, Drama,FALSE,FALSE,FALSE,FALSE$],[$Action$,$ Adventure, Fantasy,FALSE,FALSE,FALSE,FALSE$],[$Drama$,$FALSE,FALSE,FALSE,FALSE$]]$

The second task is to apply the text manipulation skills from Chapter $6$ to remove "min" from Runtime.

a$.$ Insert a new column after Column E $($Runtime$)$ and call it Run Time Minutes.

b$.$ Referencing the cells in the Runtime column, use the LEFT$()$ and FIND$()$ functions to get only the numbers in the cells.

c$.$ It should look like the following:

$1$Section $2$: Summarize the Data

In the worksheet called, Summarize the Data you will write functions to answer the following

questions.

Use the COUNTIE$()$ function to count the TRUE and FALSE values for each genre type.

a$.$ How many movies involved the comedy genre?

b$.$ How many movies involved the animation genre?

c$.$ How many movies involved the romance genre?

d$.$ How many movies involved the horror genre?

e$.$ Apply conditional formatting to the counts of each genre to show a data bar.

In the section $2$B: Answer Questions, use the functions indicated to answer to the

following questions.

a$.$ What is the maximum Gross $($total money made$)$ by a movie? Use the MAX$()$

function.

b$.$ What is the oldest movie on in the top $100$ according to the Released$\_$Year? Use the

MIN$()$ function.

c$.$ What is the most frequently used rating? Use the MODE$()$ function.

i$.$ What is the average rating?

ii$.$ Explain why you might use one measure over the other in analysis?

d$.$ What are the average IMDB$\_$Ratings for the following Certificates?

i$.G$

ii$.PG$

iii. PG$-13$

iv$.R$e$.$ How many movies did not release a number for Gross $($total revenue made$)?$ Use

COUNTBLANK$().$

ection $3$: Create an Interactive Movie Fact Sheet

Duplicate the imdb$\_$top$\_1000$ worksheet and call the worksheet Table. Use Ctrl $+$ T to make

the data a table and name the table movies.

a$.$ This will be the data that you will refence in the Movie Fact Sheet.

b$.$ It will look like this:

In the Movie Fact Sheet worksheet cell A$2,$ create a drop$-$down list of all the titles in the

Series$\_$Title column. Use data validation to create the drop$-$down $($mentioned in chapter $37$

of the book$).$

a$.$ Create an input Message Title: Movie and Input message: Select a movie.

Use Index and match functions $($from chapters $4$ and $5$ of the book$)$ to fill in the information

for Released$\_$Year, Certificate, Runtime, IMDB$\_$Rating, and Gross. Be sure to reference cell

A$2,$ the drop$-$down list in your formula.

a$.$ Format the values to the right side.

b$.$ Format the Gross value as currency with no decimals.

Make cell A$10$ equal the Release$\_$Year cell B$3$ to get a dynamic Title.

In cell A$11$ use the FILTER$()$ function to get a list of movies titles from the Series$\_$Title column

from the same year of the movie chosen in the drop$-$down list.

a$.$ The finished result should look like this when a movie is selected:$\backslash$table$[[,$A$,$B$,$C$],[1,$Section $3$: Movie Fact,,$],[2,$Paddington $2,$grad,$],[3,$Released$\_$Year Movie,$2017,],[4,$Certificate $,$ Select a movie.,U$,],[5,$Runtime,$103$min,$],[6,$IMDB$\_$Rating,$7\mathrm{.}8,],[7,$Gross,$40,442,052,],[8,,,],[9,$Other Movies From,,$],[10,2017,,],[11,$Ayla: The Daughter of War,,$],[12,$Baahubali $2$: The Conclusion,,$],[13,$Baby Driver,,$],[14,$Blade Runner $2049,,],[15,$Call Me by Your Name,,$],[16,$Coco,,$],[17,$Dunkirk,,$],[18,$Get Out,,$],[19,$Gifted,,$],[20,$God's