Question

Friends
129
349
51
50
273
361
110
108
99
27
45
243
122
196
63
134
95
162
179
739
203
84
172
104
293
61
38
64
75
88
116
95
123
71
94
38
9
226
73
69
203
155
40
56
27
62
11
5

1. Create a histogram using Excel’s Data Analysis toolpack for Friends. Use the Sturges’ rule to find how many bins you should include. In your Word file, make sure to in- clude both a frequency table for the histogram with bin limits, frequencies and relative frequencies, and your histogram chart. What can you say about the distribution of Friends? All calculations should be shown in excel. (Along with the answer, can anyone give me the instruction on how to do the histogram and solve the questions using excel?).

Answer 1

To create a histogram using Excel’s Data Analysis toolpack, follow the steps

1. Type all your values in a column.

2. Find how many bins are required from Sturge's Rule. It states that

the number of groups or classes is 1 + 3.3 log n, where n is the number of observations.

Here n is 48. So the number of classes comes out to be 6.54 rounding to 7.

3. Range of observation is from 5-739. Number of groups to which it is divided is 7.

So the width of the group comes out to be 110.

4. Specifying the bin range in another column.

for example, 110 means observations less than or equal to 110 and greater than 0 will form a class.

5. Add the Data Analysis add-in to your Excel, by performing the following steps.

File > Options > Add-ins

At the bottom, click GO.

6.At the next window, select Analysis ToolPak and click OK.

7. On the data tab, click the data analysis button.

In the dialog box, select Histogram and click OK.

8. In the window opened, give the input range and bin range.

Both can be selected by clicking the collapse dialog button, selecting the range and again clicking the collapse dialog button to return.

Collapse dialog button

Output Range is the cell where we need the output. Select the Chart Output and click OK.

9. You'll get a histogram along with bin and frequency table.

Add one more column to the table named 'Relative Frequency'.It can be filled by (corresponding frequency)/(total number of observations). for example in the first row 29/48 where 48 is the n(total no of observations).

10. From the corresponding distribution, we can infer that the number of friends lying between 0 and 110 is much higher than any other class.