Build a Better Process
Build a Better Process
/ factorpad.com / fin / glossary / frequency-distribution.html
An ad-free and cookie-free website.
Beginner
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
For context, it is descriptive statistics, like variance and standard deviation, that allow you to understand the absolute risk of an investment based on observations from the past. These calculations can hide outliers, so it is a good idea for the financial modeler to utilize a frequency distribution and histogram to be certain that takeaways associated with descriptive statistics are valid.
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
F8:I13.
Sixth, type =FREQUENCY open
parenthesis, select the data_array, comma, the bins_array, closed
parenthesis, then the Ctrl+Shift+Enter
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.
False, the bins array is for the breakpoints and has one less row.
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. Subscribe now.
/ factorpad.com / fin / glossary / frequency-distribution.html
A newly-updated free resource. Connect and refer a friend today.
by