Frequency Tables with EXCEL

U.S. PRESIDENTS

A

B

C

1

President

Age at Death

Bin

2

Washington

67

39

3

Madison

85

49

4

Jackson

78

59

5

Tyler

71

69

6

Fillmore

74

79

7

Lincoln

56

89

8

Hayes

70

100

9

Cleveland

71

10

Roosevelt

60

11

Harding

57

12

Roosevelt

63

13

Kennedy

46

14

Adams

90

15

Monroe

73

16

Van Buren

79

17

Polk

53

18

Pierce

64

19

Johnson

66

20

Garfield

49

21

Harrison

67

22

Taft

72

23

Coolidge

60

24

Truman

88

25

Johnson

64

26

Jefferson

83

27

Adams

80

28

Harrison

68

29

Taylor

65

30

Buchanan

73

31

Grant

63

32

Arthur

56

33

McKinley

58

34

Wilson

67

35

Hoover

90

36

Eisenhower

78

37

Nixon

81

Tabulate Frequencies

1.  Decide on the class intervals you will use to summarize

the data (use at least 5, but not more than 10 class intervals)

Let's assume you decide to create the following class intervals:

<40, 40-49, 50-59-, 60-69, 70-79, 80-89, and 90-99

2. Input Bin corresponding to your class intervals in the

column next to your data, i.e. column C. For example: input the

numbers starting with the smallest Bin 39, 49, 59, 69, 79, 89, 

Bin

Frequency

and 99.

39

0

  39 -  Number of Presidents who died at age 39 or earlier.

49

2

  49 -  Number of Presidents who died at age 40-49.

59

5

  59 -  Number of Presidents who died at age 50- 59.

69

12

  etc., etc., … until you reach:

79

10

 99 - Number of Presidents who died at age 90 - 99.

89

5

This will summarize the ages of presidents in intervals of 10 years,

99

2

i.e., 0-39, 40-49, 50-59, 60-69, 70-79, 80-89, 90-99.

More

0

3.  Select Tools.

4. Select Data Analysis.

5. Select Histogram and click OK.

6. Input Range of data:B2:B37 or highlight data with your cursor.

7. Input Bin Range: C2:C8 or highlight with your cursor.

8. Check Output Range and specify output range, if you would

like the output on the same spreadsheet.Output Range is A40:B50.

Choose a New Work Sheet Ply if you like a new sheet.

9. Select Chart Output to get the table.

This function will produce a table to the left or on the next page.

The output will also produce a chart on the next page or to the right

(you need to search for it!).

Create a Frequency Table

Now that EXCEL has found the frequency for each class interval, you need to manually create a

frequency table with the actual class intervals and the corresponding frequencies. Type the following

table.

Presidents

Age at Death

Frequency

39 or less

0

40 - 49

2

50 - 59

5

60 - 69

12

70 - 79

10

80 - 89

5

90 - 99

2

Format:  None

Format Table

 Choose a particular format from the selection in AutoFormat.

 1.  Highlight your table.

 2.  Click on Format.

 3. Select AutoFormat and you will see the choices you have.  

 4. For the table to the left the format 'None' was used.

Presidents

For the table on the left the format 'List 2' was used.

Age at Death

Frequency

90 - 100

2

80 - 89

5

70 - 79

10

60 - 69

12

50 - 59

5

40 - 49

2

39 or less

0

Format:  List 2