1.10 Using the Excel Spreadsheet provided - Frequency Table You Bin

Last updated
Save as PDF

Page ID: 19722

\( \newcommand{\vecs}[1]{\overset { \scriptstyle \rightharpoonup} {\mathbf{#1}} } \) \( \newcommand{\vecd}[1]{\overset{-\!-\!\rightharpoonup}{\vphantom{a}\smash {#1}}} \)\(\newcommand{\id}{\mathrm{id}}\) \( \newcommand{\Span}{\mathrm{span}}\) \( \newcommand{\kernel}{\mathrm{null}\,}\) \( \newcommand{\range}{\mathrm{range}\,}\) \( \newcommand{\RealPart}{\mathrm{Re}}\) \( \newcommand{\ImaginaryPart}{\mathrm{Im}}\) \( \newcommand{\Argument}{\mathrm{Arg}}\) \( \newcommand{\norm}[1]{\| #1 \|}\) \( \newcommand{\inner}[2]{\langle #1, #2 \rangle}\) \( \newcommand{\Span}{\mathrm{span}}\) \(\newcommand{\id}{\mathrm{id}}\) \( \newcommand{\Span}{\mathrm{span}}\) \( \newcommand{\kernel}{\mathrm{null}\,}\) \( \newcommand{\range}{\mathrm{range}\,}\) \( \newcommand{\RealPart}{\mathrm{Re}}\) \( \newcommand{\ImaginaryPart}{\mathrm{Im}}\) \( \newcommand{\Argument}{\mathrm{Arg}}\) \( \newcommand{\norm}[1]{\| #1 \|}\) \( \newcommand{\inner}[2]{\langle #1, #2 \rangle}\) \( \newcommand{\Span}{\mathrm{span}}\)\(\newcommand{\AA}{\unicode[.8,0]{x212B}}\)

Frequency Table You Bin

This section teaches you to create a frequency table using the Excel spreadsheet provided for this textbook section. There are four spreadsheets in the Excel Spreadsheet below. You can download the spreadsheet by clicking the download button ( ). It is the first button on the left in the right-hand corner of the Excel Spreadsheet screen. Another method of downloading the Excel Spreadsheet is to put the URL in a web browser to download the workbook.

To use the Excel spreadsheet below, you must type in the data. After you download the data, you can copy the data into the spreadsheet using copy and paste commands. To delete a cell in the spreadsheet below, double-click the cell and hit the backspace button. You delete it by double-clicking in the cell and highlight the data, and hit the backspace key.

You can enter information in the blue cells only. In the other cells, you will not be able to enter data. Some are blank, and others have formulas. The formulas will create the frequency table.

See instructions below on how to use this Excel Spreadsheet.

Suppose you have a problem that asks you to create a frequency table from the data below.

4, 8, 9, 11, 13, 14, 15, 18, 18, 19, 19, 20, 21, 23, 24, 25

Create a frequency table with the following bins.

2 - 6
7 - 11
12 - 16
17 - 21
22 - 26

Enter the data in cells A2 thru A17.

Enter 4 in cell A2.
Enter 8 in cell A3.
Enter 9 in cell A4.
Enter 11 in cell A5.
Enter 13 in cell A6.
Enter 14 in cell A7.
Enter 15 in cell A8.
Enter 18 in cell A9.
Enter 18 in cell A10.
Enter 19 in cell A11.
Enter 19 in cell A12.
Enter 20 in cell A13.
Enter 21 in cell A14.
Enter 23 in cell A15.
Enter 24 in cell A16.
Enter 25 in cell A17

Next enter the Bin values.

Enter 2 in cell D2.
Enter 6 in cell E8.
Enter 7 in cell D9.
Enter 11 in cell E9.
Enter 12 in cell D10.
Enter 16 in cell D16.
Enter 17 in cell D11.
Enter 21 in cell E11.
Enter 22 in cell D12.
Finally, enter 26 in cell E12.

In the frequency table, the Frequency column is calculated automatically. You need to enter some of the Cumulative Frequency values. The Cumulative Frequency column is computed below. Then the cumulative frequency is a running total of the frequency. The relative frequency is the Frequency/Total Frequency. The cumulative relative frequency is the cumulative frequency/Total frequency.

Frequency	Cumulative Frequency	Relative Frequency	Cumulative Relative Frequency
1	1	=1/16 = 0.0625 (6.25%)	=1/16 = 0.0625 (6.25%)
3	1+3=4	=3/16 = 0.1875 (18.75%)	=4/16 = 0.2500 (25.00%)
3	4+3 = 7	=3/16 = 0.1875 (18.75%)	=7/16 = 0.4375 (43.75%)
6	7+6=13	=6/16 = 0.3750 (37.50%)	=13/16 = 0.8125 (81.25%)
3	13+3=16	=3/16 = 0.1875 (18.755%)	=16/16 = 1.0000 (100.00%)

You will need to enter the following data values.

Cumulative Frequency Column

Enter 1 in cell G8.
Enter 13 in cell G11.

Relative Frequency Column

Enter 0.1875 in cell H9.
Enter 0.3750 in cell H11.

Cumulative Relative Frequency

Enter 0.0625 in cell I8.
Enter 0.8125 in cell I11.