Frequency distribution is a table showing the number, or frequency, of observations within ranges of data. Ranges may also be called bins or buckets. As an example, in a two-bin case, count the number of observations in each of two bins: a less-than-zero bin, and a zero-or-greater bin. The same tabular data can be presented in chart form using a histogram.
Synonym: frequency table
Joy: So to get a
frequency distribution in
Excel, it ends with Ctrl+Shift+Enter?
Doc: That's music to my ears. Exactly! It's called the =FREQUENCY function.
This video can be accessed in a new window or App , at the YouTube Channel or from below.
Frequency distribution definition for investment modeling (4:31)
The script includes two sections where we visualize and demonstrate the creation of a frequency distribution.
We're sitting right here in Excel and this is a snippet from our boot camp course.
Our goal is to analyze 60 monthly returns on four stocks, located on a tab called Returns.
And in this table we listed six ranges, or bins. So we now can see. There were 2 returns of less than -10% for Microsoft, and eBay returned greater than 10%, 12 times.
It can be easier to grasp this by using a histogram. This visual perspective will help you spot errors and trends. It will also let you see if the distribution is in alignment with your statistical tests. See how eBay, in orange, had more observations away from the center, than Microsoft and Abbott Labs? So eBay would have higher risk, if we measured it by standard deviation. Next, let's demonstrate.
There are eight steps to generate a frequency distribution and histogram in Excel. First, estimate breakpoints by reviewing the data. I sorted the returns in rather arbitrarily-selected buckets.
From there, set up labels and a blank table for the numeric output.
Third, set up the bins_array, or cut off points. Each horizontal line here, refers to a breakpoint for the bins, so you need one less than the number of bins, so 5 bins, instead of 6. These aren't used as labels, so no need to be fancy here.
Fourth, set up the data array. In this case we'll select the range
C7:F66 on the Returns tab.
Fifth, select and highlight the output block, or
parenthesis, select the data_array, comma, the bins_array, closed
parenthesis, then the
keystroke combination, telling Excel it is an array calculation,
meaning calculate everything at once. The array formula is identified
by curly braces. There you have it.
Seventh, create a row with the
=SUM function to verify totals.
Last, highlight the range of data, including labels, click Insert, then Column. Edit the histogram as you see fit.
Click box for answer.
False, the other way around
Still unclear on the frequency distribution? Leave a question in the comments section on YouTube or check out the Quant 101 Series, specifically Stock return frequency distributions and histograms in Excel.
Our trained humans found other terms in the category statistics basics you may find helpful.
Join the smart crowd at the FactorPad YouTube Channel.