Sheet 4:Pivot Table Data

Movie Rank

Domestic Gross (in millions)

Rating

Type

1

$999.00

PG-13

Fantasy

2

$988.00

PG

Documentary

3

$984.00

PG-13

Thriller

4

$984.00

Unrated

SuperHero

5

$981.00

R

Fantasy

6

$979.00

PG

Fantasy

7

$972.00

PG

Fantasy

8

$970.00

PG

Family

9

$970.00

PG

Romance

10

$957.00

PG-13

Musical

11

$956.00

R

Horror

12

$953.00

PG-13

SuperHero

13

$952.00

PG

Documentary

14

$951.00

Unrated

Sci-Fi

15

$948.00

PG-13

Horror

16

$939.00

G

Documentary

17

$934.00

G

Family

18

$932.00

Unrated

Action

19

$932.00

PG

Comedy

20

$930.00

R

Horror

21

$907.00

R

Western

22

$905.00

PG-13

Romance

23

$904.00

R

Sci-Fi

24

$903.00

Unrated

Family

25

$898.00

PG-13

Horror

26

$896.00

PG-13

Sci-Fi

27

$882.00

Unrated

Cartoon

28

$878.00

Unrated

Horror

29

$875.00

Unrated

Action

30

$874.00

PG

Unknown

31

$873.00

Unrated

Horror

32

$860.00

R

Thriller

33

$856.00

Unrated

Thriller

34

$851.00

R

Drama

35

$848.00

R

Horror

36

$839.00

PG

Thriller

37

$835.00

Unrated

Drama

38

$830.00

PG-13

Comedy

39

$829.00

R

Horror

40

$824.00

R

Horror

41

$821.00

R

Comedy

42

$819.00

R

Unknown

43

$815.00

R

Comedy

44

$809.00

PG-13

Documentary

45

$802.00

PG

Thriller

46

$798.00

PG

Cartoon

47

$792.00

G

Family

48

$787.00

Unrated

Family

49

$785.00

PG

Musical

50

$775.00

PG-13

Fantasy

51

$770.00

R

Comedy

52

$758.00

PG

Drama

53

$755.00

PG-13

Family

54

$750.00

PG-13

Western

55

$744.00

PG-13

Thriller

56

$740.00

R

Action

57

$736.00

PG-13

Thriller

58

$736.00

R

Thriller

59

$731.00

G

Cartoon

60

$721.00

PG

Sci-Fi

61

$721.00

R

Musical

62

$717.00

R

Sci-Fi

63

$700.00

G

Sci-Fi

64

$692.00

Unrated

SuperHero

65

$684.00

PG

Fantasy

66

$682.00

PG-13

Fantasy

67

$671.00

PG

Action

68

$667.00

G

Romance

69

$658.00

Unrated

Horror

70

$645.00

R

Horror

71

$642.00

G

Musical

72

$642.00

G

Family

73

$637.00

PG-13

Thriller

74

$637.00

PG-13

Comedy

75

$634.00

PG-13

Horror

76

$632.00

Unrated

Horror

77

$629.00

Unrated

SuperHero

78

$623.00

R

Documentary

79

$623.00

Unrated

Romance

80

$620.00

PG-13

SuperHero

81

$615.00

Unrated

Sci-Fi

82

$612.00

Unrated

Documentary

83

$610.00

PG

Cartoon

84

$610.00

Unrated

Musical

85

$605.00

PG-13

Action

86

$600.00

G

Family

87

$594.00

PG-13

SuperHero

88

$590.00

G

Family

89

$589.00

G

Family

90

$586.00

R

Musical

91

$582.00

R

Unknown

92

$581.00

R

Drama

93

$571.00

PG-13

Comedy

94

$570.00

G

Sci-Fi

95

$564.00

PG

Documentary

96

$562.00

PG

Unknown

97

$556.00

PG-13

Thriller

98

$545.00

PG-13

Thriller

99

$545.00

R

Thriller

100

$531.00

G

Family

101

$527.00

R

Horror

102

$520.00

G

Fantasy

103

$503.00

G

Documentary

104

$494.00

G

Sci-Fi

105

$494.00

PG-13

Thriller

106

$485.00

PG-13

Thriller

107

$478.00

R

Drama

108

$474.00

R

Documentary

109

$474.00

PG-13

Horror

110

$470.00

R

Romance

111

$453.00

R

Horror

112

$449.00

PG

Horror

113

$446.00

PG

Unknown

114

$445.00

PG

Family

115

$434.00

PG

Sci-Fi

116

$429.00

PG

Family

117

$423.00

R

Drama

118

$401.00

G

Sci-Fi

119

$398.00

R

SuperHero

120

$393.00

G

Family

121

$391.00

G

Musical

122

$388.00

R

Action

123

$387.00

PG

Romance

124

$386.00

PG-13

Unknown

125

$381.00

G

Romance

126

$380.00

PG

Musical

127

$378.00

PG-13

Family

128

$378.00

R

Action

129

$376.00

R

Sci-Fi

130

$375.00

PG

Family

131

$364.00

PG-13

Drama

132

$352.00

PG-13

Horror

133

$335.00

PG-13

SuperHero

134

$335.00

PG-13

Action

135

$332.00

R

Documentary

136

$326.00

G

Family

137

$321.00

PG-13

SuperHero

138

$317.00

PG-13

SuperHero

139

$313.00

G

Romance

140

$306.00

Unrated

Fantasy

141

$300.00

PG

Cartoon

142

$298.00

PG-13

Thriller

143

$296.00

R

Thriller

144

$293.00

G

Comedy

145

$284.00

PG

Comedy

146

$278.00

G

Sci-Fi

147

$269.00

R

SuperHero

148

$253.00

R

Documentary

149

$253.00

R

Cartoon

150

$250.00

PG-13

Action

151

$245.00

R

Horror

152

$241.00

R

SuperHero

153

$233.00

R

Comedy

154

$230.00

R

Comedy

155

$228.00

PG-13

Horror

156

$224.00

PG

Family

157

$219.00

PG-13

Western

158

$218.00

R

Fantasy

159

$216.00

PG-13

Fantasy

160

$202.00

G

Romance

161

$202.00

R

Action

162

$197.00

PG-13

Documentary

163

$194.00

PG

Drama

164

$193.00

PG

Action

165

$187.00

PG

Action

166

$183.00

PG-13

Cartoon

167

$182.00

G

Family

168

$181.00

G

Comedy

169

$171.00

PG-13

Comedy

170

$169.00

PG-13

Documentary

171

$167.00

PG-13

Romance

172

$166.00

PG-13

Cartoon

173

$164.00

PG

Romance

174

$157.00

R

Cartoon

175

$130.00

PG-13

Documentary

176

$129.00

PG

Comedy

177

$129.00

G

Family

178

$125.00

PG-13

Thriller

179

$106.00

PG-13

Romance

180

$82.00

PG

Family

181

$81.00

G

Action

182

$79.00

R

Horror

183

$79.00

R

Romance

184

$78.00

R

Cartoon

185

$73.00

G

Documentary

186

$70.00

PG-13

Sci-Fi

187

$69.00

PG-13

SuperHero

188

$69.00

G

Cartoon

189

$63.00

PG-13

Drama

190

$60.00

R

Horror

191

$57.00

PG

Documentary

192

$54.00

PG-13

SuperHero

193

$36.00

PG

Thriller

194

$30.00

R

Romance

195

$26.00

R

Fantasy

196

$15.00

G

Documentary

197

$14.00

R

Action

198

$4.00

R

Fantasy

199

$4.00

PG

Documentary

200

$1.00

R

SuperHero

Sheet 5:Q3 - Pivot Table

11)

Using the data on the Pivot Table Data Sheet, Pivot table showing:

1) The Movie Type, Count of Type, and Sum of Domestic Gross (in millions); columns D and I from the Pivot Table Data Sheet

Have three columns:Movie Type, Count of Type, and Sum of Domestic Gross (in millions)

Format the Sum of Domestic Gross (in millions) 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?)

Sheet 6:Q4 - Frequency

Use the raw Data on the Pivot Table Data Sheet to a Frequency Chart:

Follow these steps to get the Frequency chart. The following website also has instructions to create bins.

https://www.statisticshowto.datasciencecentral.com/choose-bin-sizes-statistics/

14)

Step One: (Find the lowest and highest numbers in the data.) Use the Excel sheet titled Pivot Table Data

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)

Step Two: (Find the range by subtracting the lowest number from the highest number.)

Subtract the lowest from the highest to find the range of the Domestic Gross take for the Movies

The range of Total Domestic Gross for these movies is

16)

Step Three: (Find the bin widths by dividing the range by the number of bins that you want to have.)

We will use 10 bins so divide the range by 10:

Each bin will be :

wide. Do not round.

Step Four: (Find the highest number for the first bin by starting with the lowest number in the data set and adding the bin width.)

Start with the minimum number:

Add the width of the bins

This number is the highest number that is used in the first bin.

Excel will use this number when it counts the number of pieces of data in the raw data set that is lower than this number.

Put this number in cell C38 for the first bin.

Step Five: (Find the rest of the highest bin numbers. Start with the highest number in the first bin. Add the bin width. This number is the highest total for bin 2.)

The next bin's's 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

Place this number in cell C39.

Successive bins start with the previous bin's highest number and adds the width of thebins. The last bin will have the maximum number in the data set as its highest value.

Therefore, continue adding to get the Bins array for the =FREQUENCY() function.

The last bin number in cell C47 will equal the highest Domestic Gross movie total

17)

Here are the highest numbers for each bin:

Bins:

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:

Sheet 7:Q5 - Charts

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

Bins:

Frequency:

19)

Histogram

a Histogram of the Bins and Frequency data by first creating a column Chart and then removing the spaces between the columns.

20)

Format the historgram so there are no spaces between the bars. Histograms do not have 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 raw frequency data with a title and Legend: