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

 

 

 

copyright 2004 Elisabeth Knowlton