Question

Using Excel to Plot a Histogram

This exercise is to be completed using Microsoft Excel and will be turned in on Thursday, September

20th in class. You are to follow the steps we used in class on 9-13-2018 to construct the histogram

for the data consisting of the ages of the 50 most powerful women in the world from 2012 (Source:

Page 39 of textbook via Forbes Magazine). Your work should have columns for:

1. the raw data;

2. the data sorted in ascending order;

3. the maximum data value;

4. the minimum data value;

5. the range;

6. the class width using 7 classes;

7. the lower class limits;

8. the upper class limits;

9. the 7 classes themselves;

10. the frequency of the data within the 7 classes.

Your work should also include a picture of the corresponding histogram labeled as follows:

•Main Title:

“Age Distribution of the 50 Most Powerful Women for 2012 (new line) n = 50”

•Vertical Axis Title:

“Frequency”

•Horizontal Axis Title:

“Age Classes”

Please do not write on the paper to explain how to make this plot. PLEASE let me know the order what I should press to complete this plot in Excel. I need to finish this work in Excel, so I need easy and understandable explaination, such as what to press in to create class width in Excel something like that

This is Raw value to make this plot in Excel.

Raw Age Data
26
51
58
66
31
51
58
67
35
51
58
67
37
52
58
72
43
54
59
86
43
54
59
43
54
59
44
54
62
45
55
62
47
55
63
48
55
64
48
56
65
49
57
65
50
57
65
51
57
66

Answer 1

Solution :

1) The Raw data :

We have to just enter the data in the first column of Microsoft Excel. Range is A2 to A51.

2) Data sorted in ascending order :

3) The maximum data value :

We have to use the formula   on the starting cell.

4) The minimum data value :

We have to use the formula   on the starting cell.

5) The range :

We know that the Range = Maximum - Minimum. Thus , we have to use the formula  

on the starting cell.

6) The class width using 7 classes :

We know that the Class Width = Difference between the Class Limits.

This can be found out by the formula , where , Range is stored in E2. We round the Class Width to the nearest whole number. It comes as 9.

7) The lower class limits :

We write the Minimum Value in the first cell and add 9 to every cell.

8) The upper class limits :

We write the Maximum Value in the first cell and add 9 to every cell.

9) The 7 classes themselves :

We write the 7 class intervals by combining the lower and the upper class limits.

10) The frequency of the data within the 7 classes :

For Frequencies and Histogram , we follow these steps :

Then a dialogue box will appear where we have to enter the following details.

Input Range -- The range of the raw dataset (A2 : A51)

Bin Range -- The range of the upper class intervals (H2 : H8)

Output Range -- The range from which the output will start.

Chart Output -- Select the "Chart Output" to obtain the Bins and Frequencies.

The image is provided for clarity.

Thus , we will get the required histogram (in the form of a bar diagram) and the frequencies. We will just copy the frequencies beside the Class Intervals.

Now , we will update the Histograms Title and the Axis Titles as per required by double-clicking on them previously provided titles. Now , since in the Histograms , there should not be any space between the bars , so we have to do the following to get a proper histogram.

Now , to give it a proper look , we will add borders. For that , we will have to do the following.

Here ,  

The image is given for more clarity.

Thus , the outputs are given below !!

Thus , at last the histogram will look like this !!

----------------------------------------------------------------------------------------------------