17 Jan Create a Histogram of the Bins

Question 1

Create a spreadsheet that automatically calculates your grade in this class as you enter the grades that you receive. It should include: 1) the weights of each graded assignment, 2) your grade in each assignment, and 3) your final grade. Be sure to label everything: such as the places where the information, is stored; the names of each assignment; and where to add the actual grades that you receive. Make this sheet user friendly; especially for users that see the sheet for the first time.

To use this for your benefit, you may want to design it so that it can be used to calculate your interim grade before you have all the grades. After this week, you will receive the solution from your professor and you will be able to use your solution or our solution for future classes.

Grade Item

Points

Weight Achieved

Grade

Homework2

/7.5

0

Quiz3

/5

0

Quiz4

/5

0

Quiz5

/5

0

Quiz6

/5

0

Mid Term Exam

/10

0

Quiz8

/5

0

Critical review

/10

0

Quiz10

/5

0

homework11

/7.5

0

Final exam

/20

0

Class Participation

/15

0

Question 2

Here is sample data showing the length of nails in boxes labled 1 1/4 inch. The data is in inches.

1.26

1.22

1.27

1.21

1.22

1.25

1.22

1.25

1.25

1.23

1.24

1.24

1.29

1.24

1.27

1.29

1.23

1.22

1.23

1.26

1.28

1.25

1.25

1.23

1.26

1.23

1.26

1.29

1.26

1.26

1.23

1.21

1.23

1.21

1.22

1.22

1.24

1.21

1.23

1.25

1.21

1.28

1.26

1.25

1.22

1.29

1.22

1.22

1.29

1.24

1.21

1.22

1.23

1.26

1.28

1.26

1.28

1.21

1.21

1.21

1.29

1.26

1.26

1.28

1.25

1.24

1.24

1.27

1.28

1.24

1.21

1.25

1.23

1.25

1.29

1.29

1.22

1.21

1.28

1.27

For the following questions, you must use Excel formulas in the cells so that Excel calculates the answers for you.

1) Compute the mean:

Compute the median

Find the mode

2) Compute the first quartile; use =QUARTILE.EXC()

First Quartile

Compute the third quartile; use =QUARTILE.EXC()

Third Quartile

3) Compute the interquartile range

4) Find the largest number

Find the smallest number

What is the range?

5) What is the Variance? Use =VAR.S()

What is the standard deviation? Use =STDEV.S()

6) What is the Coefficient of Variation, or the CV?

https://www.statisticshowto.datasciencecentral.com/probability-and-statistics/how-to-find-a-coefficient-of-variation/

7) When is the Coefficient of Variation especially useful?

Copy all of the data into a column, use Column M and go from cell M1:M80

8) Use the Data Analysis tool on the numbers just copied to find the Descriptive Statistics: Click on Data Analysis and Choose Descriptive Statistics

Click on the Summary Statistics box.

Highlight the mean, median, mode, Standard deviation, Range, Minimum, and Maximum

Notice that the Data Analysis tool gives you all of the info needed for this problem except for the quartiles, variance, and CV

9) Interpret the measures of central tendency within the context of this problem.

Should the company producing the nails be concerned about the central tendency?

10) Interpret the measures of variation within the context of this problem.

Should the company producing the nails be concerned about variation?

Question 3

Rank

Name

Studio

Total DomesticGross in Millions

# Theaters

Open

Close

Rating

Type

1

Black Panther

BV

$70,00,59,566.00

4,084

16-Feb

09-Aug

PG-13

SuperHero

2

Avengers: Infinity War

BV

$67,88,15,482

4,474

27-Apr

13-Sep

PG-13

SuperHero

3

Incredibles 2

BV

$60,85,81,744

4,410

15-Jun

13-Dec

PG

Cartoon

4

Jurassic World: Fallen Kingdom

Uni.

$41,77,19,760

4,485

22-Jun

04-Oct

PG-13

Action

5

Aquaman

WB

$33,20,63,355

4,184

21-Dec

–

PG-13

SuperHero

6

Deadpool 2

Fox

$31,84,91,426

4,349

18-May

18-Oct

R

SuperHero

7

Dr. Seuss’ The Grinch (2018)

Uni.

$27,06,20,950

4,141

09-Nov

–

PG

Cartoon

8

Mission: Impossible – Fallout

Par.

$22,01,59,104

4,395

27-Jul

18-Oct

PG-13

Action

9

Ant-Man and the Wasp

BV

$21,66,48,740

4,206

06-Jul

01-Nov

PG-13

SuperHero

10

Solo: A Star Wars Story

BV

$21,37,67,512

4,381

25-May

20-Sep

PG-13

Sci-Fi

11

Venom (2018)

Sony

$21,35,15,506

4,250

05-Oct

24-Jan

PG-13

SuperHero

12

Bohemian Rhapsody

Fox

$21,23,93,481

4,000

02-Nov

–

PG-13

Drama

13

A Star is Born (2018)

WB

$21,01,20,689

3,904

05-Oct

–

R

Drama

14

Ralph Breaks the Internet

BV

$19,94,07,849

4,017

21-Nov

–

PG

Cartoon

15

A Quiet Place

Par.

$18,80,24,361

3,808

06-Apr

02-Aug

PG-13

Horror

16

Spider-Man: Into The Spider-Verse

Sony

$18,38,90,410

3,813

14-Dec

–

PG

Cartoon

17

Crazy Rich Asians

WB

$17,45,32,921

3,865

15-Aug

10-Jan

PG-13

Comedy

18

Mary Poppins Returns

BV

$17,07,00,172

4,090

19-Dec

–

PG

Musical

19

Hotel Transylvania 3: Summer Vacation

Sony

$16,75,10,016

4,267

13-Jul

29-Nov

PG

Cartoon

20

Fantastic Beasts: The Crimes of Grindelwald

WB

$15,95,50,370

4,163

16-Nov

–

PG-13

Fantasy

21

Halloween (2018)

Uni.

$15,93,42,015

3,990

19-Oct

03-Jan

R

Horror

22

The Meg

WB

$14,54,43,742

4,118

10-Aug

01-Nov

PG-13

Sci-Fi

23

Ocean’s 8

WB

$14,02,18,711

4,145

08-Jun

20-Sep

PG-13

Action

24

Ready Player One

WB

$13,76,90,172

4,234

29-Mar

05-Jul

PG-13

Sci-Fi

25

Bumblebee

Par.

$12,66,80,829

3,597

21-Dec

–

PG-13

Sci-Fi

26

Mamma Mia! Here We Go Again

Uni.

$12,06,34,935

3,514

20-Jul

11-Oct

PG-13

Musical

27

The Nun

WB (NL)

$11,74,50,119

3,876

07-Sep

22-Nov

R

Horror

28

Creed II

MGM

$11,56,72,186

3,752

21-Nov

–

PG-13

Drama

29

Peter Rabbit

Sony

$11,52,53,424

3,725

09-Feb

14-Jun

PG

Family

30

The Mule

WB

$10,32,42,064

3,329

14-Dec

–

R

Drama

31

The Equalizer 2

Sony

$10,20,84,362

3,388

20-Jul

20-Sep

R

Thriller

32

Rampage (2018)

WB (NL)

$10,10,28,233

4,115

13-Apr

26-Jul

PG-13

Action

33

A Wrinkle in Time

BV

$10,04,78,608

3,980

09-Mar

05-Jul

PG

Fantasy

34

Fifty Shades Freed

Uni.

$10,04,07,760

3,768

09-Feb

05-Apr

R

Drama

35

Disney’s Christopher Robin

BV

$9,92,15,042

3,602

03-Aug

29-Nov

PG

Fantasy

36

I Can Only Imagine

RAtt.

$8,34,82,352

2,894

16-Mar

12-Jul

PG

Family

37

Smallfoot

WB

$8,32,40,103

4,131

28-Sep

–

PG

Cartoon

38

Night School (2018)

Uni.

$7,73,39,130

3,019

28-Sep

03-Jan

PG-13

Comedy

39

The First Purge

Uni.

$6,94,88,745

3,038

04-Jul

06-Sep

R

Horror

40

Game Night

WB (NL)

$6,91,79,066

3,502

23-Feb

21-Jun

R

Comedy

41

Book Club

Par.

$6,85,66,296

3,169

18-May

09-Aug

PG-13

Comedy

42

The House With A Clock In Its Walls

Uni.

$6,85,49,695

3,592

21-Sep

03-Jan

PG

Fantasy

43

Skyscraper

Uni.

$6,84,20,120

3,822

13-Jul

27-Sep

PG-13

Action

44

Insidious: The Last Key

Uni.

$6,77,45,330

3,150

05-Jan

29-Mar

PG-13

Horror

45

Instant Family

Par.

$6,73,63,237

3,426

16-Nov

31-Jan

PG-13

Comedy

46

Green Book

Uni.

$6,72,10,436

2,648

16-Nov

–

PG-13

Drama

47

Blockers

Uni.

$6,03,11,495

3,418

06-Apr

02-Aug

R

Comedy

48

Pacific Rim Uprising

Uni.

$5,98,74,525

3,708

23-Mar

26-Jul

PG-13

Sci-Fi

49

Tomb Raider

WB

$5,82,50,803

3,854

16-Mar

31-May

PG-13

Action

50

Maze Runner: The Death Cure

Fox

$5,80,32,443

3,793

26-Jan

03-May

PG-13

Sci-Fi

11) Using the data on the Pivot Table Data Sheet, create a Pivot table showing

The Type of Movie and the Domestic Gross of each Type; columns D and I on the Pivot Table Data Sheet

Have three columns: Type, Count of Type, and Sum of Total Domestic Gross

Format the Domestic Gross Field using $

12) Which type of movie had the highest Domestic Gross Total for 2018?

13) Which type of movie had the highest number of films made of that type in 2018?

(You might try making more/different pivot tables to learn about the raw data. What do you want to know about Domestic Movies in 2018?)

14) Use the Data in the Pivot Table Data Sheet to create a Frequency Chart:

What is the Total DomesticGross of the lowest movie sales? Hint – use either =MIN()or just choose the movie at the bottom of the list

What is the Total DomesticGross of the highest movie sales? Hint – use either =MAX()or just choose the movie at the top of the list

15) Subtract the lowest from the highest

The range of Total Domestic Gross for these movies is

16) We will use 10 bins so divide the range by 10: Each bin will be : wide.

Start with the minimum number:

Add the width of the bins

This number is the highest total for the first bin. Put this number is cell C21 for the first bin. Now add the number that you just put into cell 21 to cell C27. Remember that C13 contains the width of each bin.

The next bin highest number starts with the first bin highest number and adds the size of the bins

Therefore, the second bin begins with and adds the bin size to get

Continue adding to get the Bins array for the =FREQUENCY() function. The last bin number in cell C36 will equal the highest Domestic Gross movie total

17) Here are the highest numbers for each bin: Bins: Frequency:

18) Follow the instructions in the youtube videos to use the =FREQUENCY() array function. https://www.youtube.com/watch?v=c4b1F4-tv8Q

You know that you have correctly used the =FREQUENCY() function if Excel automatically puts {} around the function. Don’t forget to push Control-Shift-Enter at the same time to enter the =FREQUENCY function. Bins: Frequency:

Copy the Bins and Frequency Data from the Q4 – Frequency sheet

19) Histogram

Create a Histogram of the Bins and Frequency data by first creating a column Chart

20) Format the historgram so there are no spaces between the bars. Histograms have not spaces and the graph does not become a Histogram until the spaces are removed.

Add a title to the Histogram

Add horizontal and Vertical Axes titles

21) Explain the difference between a histogram and a bar graph:

22) Make a pie chart of the frequency data with a title and Legend: