Frequency Distribution Table in Excel

Updated on October 18, 2018
Joshua Crowder profile image

Joshua has work experience in manufacturing, distribution, and aerospace. He received his BBA in accounting from Kent State University.

Frequency Distribution

In the field of statistics, a frequency distribution is a data set or graph that is organized to show frequencies of the occurrence outcomes. Each outcome from the data set that forms a frequency distribution is a repeatable event and can be observed multiple times. A simple example is the frequency of the purchases of books in a bookstore. Since the shelves of a books store have multiple books for sale, the frequency of the outcome of a purchase is repeatable. Frequency distributions can be shown in the form of a table and also in graph form. Frequency distributions have proved to be most useful particularly in summarizing large data sets and assigning probabilities to attributes of that data.

Excel Frequency Function

The Excel frequency function calculates the frequency of values that occur within a range of values. So, if one of the ranges in a table is 15-20 and only 2 numbers in the data set are between this range, then the frequency will show up as 2. Since this function returns an array (data set) as a result, the formula must have a corresponding array for an input. The Syntax of the frequency function is shown in the table below.

Characteristics of Frequency Function

FREQUENCY(data_array, bins_array)
data_arrry: reference to the data set that is counted
bins_array: reference to intervals to group the data

Frequency Function Example

It would be best to go right into an example as this function can cause some confusion in a general explanation. Suppose you have a list of numbers and you need to know the frequency of the numbers in multiple ranges or class intervals. Consider the following data set: 17, 20, 25, 27, 19, 19, 20, 32, 26, 23, 24, 23, 27, 38, 21, 23, 22, 28, 33, 18, 27, 20, 23, 27, 31. Before creating a table you need to decide how many classes of intervals you will have in the frequency table that you are creating. The best way to find this out is by taking the square root of the amount of numbers that you have in the data set. Since I have 25 numbers in this set it would be suitable to separate the data into 5 classes. Next, to get an idea where the minimum class should start you need to look at the minimum value in the data set. Since the minimum is 17, I'll choose 15 to be the minimum of the first class interval. Now I can create all the class intervals based on this first minimum value. Observe the table below. While also considering my maximum value from the data set, I created five classes. It worked out this time, but if the high value was 41 I would have had to create another class interval.

Creating The Class Interval

Lower Class
Upper Class
15
20
20
25
25
30
30
35
35
40

Applying the Frequency Function

Now that the class intervals are created the frequency function can be applied. The area to the right of my class intervals is where the frequencies will appear. This whole area needs to be selected. Next, the formula need to be typed in. It will appear as =frequency(, then after the first parenthesis the data set needs to be selected. After the data set is selected a comma is added, then the array of high class values is selected. Next, add a parenthesis at the end and pause. To enable the function you need to press ctrl + shift + enter. After doing so, all of the frequencies will populate. The control shift enter task adds bracket to the ends of the function. Note that the function will not work properly without this last step. When using this function you have to be very specific about the intervals. For instance, if I had a value below 15 it would still show up in my first class interval because the function only knows that everything under the value of 20 goes in the lowest class interval. Same with the highest class. If I had numbers above 45, they would still be counted as being in the highest class.

Frequency Function With Data Selected

This is the point where you press shift+ctrl+enter. Doing so will add brackets to the function to enable it to print all of the frequency values.
This is the point where you press shift+ctrl+enter. Doing so will add brackets to the function to enable it to print all of the frequency values.

Results

Below the illustration show the results of the frequency function. Notice that the sum of the frequencies adds up to the total of 25, which is the total number of values in the example data set. Take notice that I added an extra class by accident and that the frequency for that class remains at zero. The way I used this formula every every value less than or equal to 20 appears in the first class. In the next class, all numbers greater than 19 and less than or equal to 25. If you need to exclude the higher interval value, label them as 19.99, 24.99, 29.99, 34.99, 39.99 when using this function.

Frequencies

Adding Relative Frequency And Cumulative Frequencies

Depending on how your data set needs to be analyzed, adding relative frequency and cumulative frequency columns to a frequency table can be beneficial. As shown in the illustration below I created an additional column to show the relative frequency. First, for the relative frequency column, I summed the frequencies, then divided each frequencies by the sum to show each proportion. Next, for the cumulative frequency column, I added the cumulative frequency for each consecutive class.

Frequency Distribution Table With Frequency, Relative Frequency, and Cumulative Frequency

It is crucial that the sum of the relative frequencies is 1 and that the cumulative frequency for the last class should be 1. If either is not 1 an error in calculation may have been made.
It is crucial that the sum of the relative frequencies is 1 and that the cumulative frequency for the last class should be 1. If either is not 1 an error in calculation may have been made.

References

Britannica, T. E. (2013, December 10). Frequency distribution. Retrieved October 16, 2018, from https://www.britannica.com/technology/frequency-distribution

FREQUENCY function. (n.d.). Retrieved October 16, 2018, from https://support.office.com/en-us/article/FREQUENCY-function-44E3BE2B-ECA0-42CD-A3F7-FD9EA898FDB9

Questions & Answers

    © 2018 Joshua Crowder

    Comments

      0 of 8192 characters used
      Post Comment

      No comments yet.

      working

      This website uses cookies

      As a user in the EEA, your approval is needed on a few things. To provide a better website experience, turbofuture.com uses cookies (and other similar technologies) and may collect, process, and share personal data. Please choose which areas of our service you consent to our doing so.

      For more information on managing or withdrawing consents and how we handle data, visit our Privacy Policy at: https://turbofuture.com/privacy-policy#gdpr

      Show Details
      Necessary
      HubPages Device IDThis is used to identify particular browsers or devices when the access the service, and is used for security reasons.
      LoginThis is necessary to sign in to the HubPages Service.
      Google RecaptchaThis is used to prevent bots and spam. (Privacy Policy)
      AkismetThis is used to detect comment spam. (Privacy Policy)
      HubPages Google AnalyticsThis is used to provide data on traffic to our website, all personally identifyable data is anonymized. (Privacy Policy)
      HubPages Traffic PixelThis is used to collect data on traffic to articles and other pages on our site. Unless you are signed in to a HubPages account, all personally identifiable information is anonymized.
      Amazon Web ServicesThis is a cloud services platform that we used to host our service. (Privacy Policy)
      CloudflareThis is a cloud CDN service that we use to efficiently deliver files required for our service to operate such as javascript, cascading style sheets, images, and videos. (Privacy Policy)
      Google Hosted LibrariesJavascript software libraries such as jQuery are loaded at endpoints on the googleapis.com or gstatic.com domains, for performance and efficiency reasons. (Privacy Policy)
      Features
      Google Custom SearchThis is feature allows you to search the site. (Privacy Policy)
      Google MapsSome articles have Google Maps embedded in them. (Privacy Policy)
      Google ChartsThis is used to display charts and graphs on articles and the author center. (Privacy Policy)
      Google AdSense Host APIThis service allows you to sign up for or associate a Google AdSense account with HubPages, so that you can earn money from ads on your articles. No data is shared unless you engage with this feature. (Privacy Policy)
      Google YouTubeSome articles have YouTube videos embedded in them. (Privacy Policy)
      VimeoSome articles have Vimeo videos embedded in them. (Privacy Policy)
      PaypalThis is used for a registered author who enrolls in the HubPages Earnings program and requests to be paid via PayPal. No data is shared with Paypal unless you engage with this feature. (Privacy Policy)
      Facebook LoginYou can use this to streamline signing up for, or signing in to your Hubpages account. No data is shared with Facebook unless you engage with this feature. (Privacy Policy)
      MavenThis supports the Maven widget and search functionality. (Privacy Policy)
      Marketing
      Google AdSenseThis is an ad network. (Privacy Policy)
      Google DoubleClickGoogle provides ad serving technology and runs an ad network. (Privacy Policy)
      Index ExchangeThis is an ad network. (Privacy Policy)
      SovrnThis is an ad network. (Privacy Policy)
      Facebook AdsThis is an ad network. (Privacy Policy)
      Amazon Unified Ad MarketplaceThis is an ad network. (Privacy Policy)
      AppNexusThis is an ad network. (Privacy Policy)
      OpenxThis is an ad network. (Privacy Policy)
      Rubicon ProjectThis is an ad network. (Privacy Policy)
      TripleLiftThis is an ad network. (Privacy Policy)
      Say MediaWe partner with Say Media to deliver ad campaigns on our sites. (Privacy Policy)
      Remarketing PixelsWe may use remarketing pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to advertise the HubPages Service to people that have visited our sites.
      Conversion Tracking PixelsWe may use conversion tracking pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to identify when an advertisement has successfully resulted in the desired action, such as signing up for the HubPages Service or publishing an article on the HubPages Service.
      Statistics
      Author Google AnalyticsThis is used to provide traffic data and reports to the authors of articles on the HubPages Service. (Privacy Policy)
      ComscoreComScore is a media measurement and analytics company providing marketing data and analytics to enterprises, media and advertising agencies, and publishers. Non-consent will result in ComScore only processing obfuscated personal data. (Privacy Policy)
      Amazon Tracking PixelSome articles display amazon products as part of the Amazon Affiliate program, this pixel provides traffic statistics for those products (Privacy Policy)