Step 1: Download the Excel Spreadsheet
First, download the Excel spreadsheet below.
Notice the data consist of values from 1 to 4. The data is discrete meaning you can list all the values.
Step 2: List all the possible values
To create a frequency table, starting in cell D1 enter the values 1, 2, 3, and 4.
Step 3: Select the Data Analysis option
Then click the Data tab on the Main Menu, and locate the Data Analysis option. Click the Data Analysis option.
Step 4: Locate the Histogram option
Select the Histogram option and click the OK button.
Step 5: The Histogram options
When the dialog box appears, fill in the information. The data is in cells A1 thru A60; therefore, in the Input Range enter $A$1:$A$60. Next, the list of possible values is in cells D1 thru D4. Enter $D$1:$D$4 in the Bin Range. Select the output range to start at G1; therefore, enter $G$1 for that option. Finally, click the Chart Output to view the Bar chart. Then hit the OK button.
Step 6: View the results
When you click the OK button in the dialog box above, the data will be displayed on the Excel spreadsheet starting at cell G1.
Step 7: Finishing the Frequency table
The Data Analysis tool will create the Frequency column for you; however, you must complete the table to answer questions on the homework. First, move the chart down. To move it down, click on the edge of the chart, and drag it down to row 14.
In cell I1, enter "Cum Freq." In cell I2, enter the formula =H2. In cell I3, enter the formula = H2+I3. Copy the formula in cell I3 down to I6. You now have the Cumulative Frequencies for the table.
In cell J1, enter "Rel Freq." In cell J2, enter the formula = h2/sum($h$2:$h$6). To format the value so it rounds to 4 decimal places, change the formula to =round(h2/sum($h$2:$h$6), 4), and copy the formula in cell J2 down to J6.
Cumulative Relative Frequency
In cell K1, enter "Cum Rel Freq." In cell K2, enter the formula = I2/sum($h$2:$h$6). To format the vale so it rounds to 4 decimal places, change the formula to =round(I2/sum($h$2:$h$6), and copy the formula in cell K2 down to K6.
Step 8: Format the Bar Chart
To format the Bar chart, click on the word Bin. Then change it to a title that is representative of the values 1 - 4. For this example, change the word to High School Class.
Then change the Title name to Frequency of Student by class.
You can get rid of the Frequency label on the right-hand side by clicking the word Frequency and uncheck the box next to Legend.