Skip to content
Skip to main content

About this free course

Download this course

Share this free course

Data analysis: visualisations in Excel
Data analysis: visualisations in Excel

Start this free course now. Just create an account and sign in. Enrol and complete the course for a free statement of participation or digital badge if available.

2.2.3 Grouped frequency distribution tables

When you are summarising large amounts of raw data, it is useful to represent the data in groups. The groups are commonly known as classes or class intervals. You might then want to determine the number of values belonging to each class or class interval; this is called class frequency. A tabular arrangement of data by class together with the corresponding class frequencies is called a grouped frequency distribution table. This is a more efficient way to find the trends within the data, but there is a possibility that the grouping process may sacrifice much of the original detail of the data.

In the following activity, you will learn how to make a grouped frequency table in Excel.

Activity 3 How to make a grouped frequency distribution table in Excel

Timing: Allow approximately 30 minutes to complete this activity

In this activity, you need to produce a grouped frequency table in Excel either by watching the screencast in Video 2 or by following the instructions given below. Once you have produced the grouped frequency table in Excel, you can check your answer by clicking ‘Reveal discussion’.

Download this video clip.Video player: Video 2
Copy this transcript to the clipboard
Print this transcript
Show transcript | Hide transcript
Video 2
Interactive feature not available in single page view (see it in standard view).
  • Open the Excel file JC Electrics [Tip: hold Ctrl and click a link to open it in a new tab. (Hide tip)] . This file contains quarterly data of the number of generators sold. Make sure that the data is arranged in columns.
  • Find the range which is the difference between the maximum and minimum value in the data set. You can do this either by entering the formula =MAX (A2:A25)-MIN (A2:A25), or by simply using the results you have calculated in Column H as, =H10-H11 (see Figure 11).
JC Electrics Excel sheet
Figure 11 Calculating the range
  • Decide the class interval width. There are no firm rules on how to choose the width. However, the following formula is the most common method to calculate the width:
width equals range over square root of sample size
  • You can round this value to a whole number or a number that is convenient to add (such as multiple of 10). For example, the width calculated in the given data set is 1.6, so will be taken as 2 (see Figure 12).
Table arranged in rows showing max, min, range, sample size and width
Figure 12 Calculating the width
  • Decide the number of groups or class intervals into which data is to be distributed. Each class interval is defined by a lower limit and an upper limit. The lower limit of first-class interval is the lowest value in the data set. Add the class interval width to find the upper limit of the first interval and the lower limit of the next class interval. Keep adding the interval width to calculate more class intervals until you exceed the highest value. For example, in the given data set, you determined the class intervals width equals 2, so you should make the class intervals as 7–8, 9–10, 11–12, 13–14, 15–16.
  • This means that the first-class interval has lower limit of 7 and upper limit of 8. See Figure 13 below.
Excel spreadsheet for JC Electrics
Figure 13 Maing the class intervals
  • The next step is to calculate the frequency. Select the range E2:E6 and enter FREQUENCY function as shown in the Figure 14 in the discussion.
  • Press CTRL + SHIFT + ENTER to submit the FREQUENCY formula above as an array formula. If it is entered correctly, you would see a formula wrapped in curly braces {}.
  • You should now save your file as you will return to this grouped frequency table in a later activity.

Comment

Excel spreadsheet for JC Electrics
Figure 14 Calculating the frequency by using an array formula

Figure 14 shows the results of using the array formula to calculate frequency. It is important to be aware that any error entered may result in an incorrect grouped frequency table and provide false information about the business.

As mentioned above, a grouped frequency table is the best option to visualise the frequency of values in a large data set. However, if you are interested to know the proportion of a particular value in relation to the total number of values in the data set, then a relative frequency distribution table is the better option. In the next section, you will learn how to produce a relative frequency table in Excel.