Question

I am working on a project. It involves using excel to organize 8 Movies by Genres, Revenue, and IMDB scores. I am having a hard time organizing by "genre". I keep getting errors when I try to include it. How can I fix this? I want to run some data analysis but cannot without it. Please list all steps on how to insert into excel. I would like to know for future reference.

The data I am working with is here: https://docs.google.com/spreadsheets/d/1EdAqfeqv7NNFIcfgV0IftWAnZNFeiTGbaxDX2sDB-bc/edit?usp=sharing

I need to be able to answer questions like:

1. The value of a measure of central tendency for each genre's gross earnings

2. The value of a measure of Variation for each genres earnings

3. Pick two genres. Is there a difference in the average IMDB scores?

4. Is there a difference in average imdb scores across all genres?

5. Is there a linear relationship between IMDB score and gross earnings?

Answer 1

You can do 4 out of these 5 steps easily by inserting a pivot table.

Steps:

1) Select the table. Click on Insert-> Pivot Table. Click on OK.

2) Now, move Genre to the Column Label and Ratings and Gross Revenue to the Sum of values label by dragging them into respective boxes.

3) Change Sum of Ratings and Sum of Gross Revenue to Average of Ratings and Average of Gross Revenue by clicking on the respective lower triangle buttons in the sum of values dialogue box and selecting "Value Field Settings..." and selecting average and clicking on OK.

It will come up with genre-wise average ratings and genre-wise average gross earnings which solves our first, third and fourth questions asking to calculate the values of average gross earnings for each genre, and differences between average imdb scores respectively which can then be calculated easily.

For Q2, drag gross revenue to sum of values box and select "Value Field Settings..." and then Var and click on OK. Remember to convert the values of "Var" into number format by using shortcut Alt->H->N->N.

For Q5, for finding linear relationship between IMDB score and gross earnings, we can use an easy formula CORREL. For uor purpose, it will be used as =CORREL(C2:C25,D2:D25). It turn out that they are positively correlated.