SubscribeStatistical Distributions in Excel [spreadcheats]
Excel has very powerful formulas and add-ins for performing almost any kind of statistical analysis. Today we will learn how you can make a statistical distribution of test scores using excel.
This is a part of our spreadcheats series of posts where we aim to solve 30 common work related excel problems, one at a time. Read the earlier spreadcheats here.
Just follow the below 2 steps to create statistical distribution / frequency of any set of values using excel. Also, download the statistical distributions example workbook and play with it.
1. Define the bands for distribution
Assuming the test scores range from 0 to 100, you can define score bands like 10,20,30,40,50,60,70,80,90,100
2. Create a frequency formula and array enter it in to the spreadsheet
This part is even easier.
Assuming the test scores are in the range B6:B105 and bands are in the range c6:c15:
First select the cells D6:D16 (10 cells, 1 each for the frequency between 0-10, 10-20, 20-30, … 90-100) and then enter the FREQUENCY() formula.
What is FREQUENCY() formula?
FREQUENCY is an excel function that takes a range of values and a range of bands and tells you how the values are distributed in the bands. As you can guess, the formula returns an array of frequencies, so it must be entered in a bunch of cells together.
How do you do that? Simple, select a range of cells, enter the formula in the first cell by start typing =frequency… and when you are done, just press ctrl+shift+enter and excel takes care of the rest.
The formula we need to enter in our case is, =FREQUENCY(B6:B105,C6:C15) and when you press ctrl+shift+enter instead of just enter. The frequency values for each band will be entered in the corresponding row.
See the screencast below to understand it better.
That is all. So simple isn’ t it?
Download the statistical distributions example workbook and play with the formulas yourself.
More on statistics and excel:
Shuffling a list of numbers in excel
Comments
RSS feed for comments on this post. TrackBack URI
Leave a comment
If you have a question, please ask in the forums
At Pointy Haired Dilbert, I have one goal, "to make you awesome in excel and charting". PHD is started in 2007 and today has 300+ articles and tutorials on using excel, making better charts. 
My pet peeve with Excel when it comes to frequency count is that it does not do a true frequency count. You have to specify bands rather like that of a histogram.
What does one do when all you want to do is check how many 80’s there are and how many 79’s there are. The thing is that be specifying the banding beforehand you have an idea of the distribution (or min/max values for example) – I’d prefer a brute force way of checking the counts of each number rather than having them banded.
I know Pivot Table is a workaround, but it’s strange that Excel has very useful functions – just not this elemental one.
Fine print: I know this is all achievable in stats related software. Just called it my rant with Excel.
@Adam: You can check the counts of individual items in two ways:
countif() for a specific number
or countif() with array formula so that you can check it for a range of numbers.
for eg. =countif(range, row(1:100)) would count all the frequencies for values between 1 to 100 and returns an array (you must ctrl+shift+enter it)
One of things that I couldnt solve is, passing this formula output to a chart without having to enter them in a range of cells. For eg. in excel you can define a named range like freqCounts and refer it to the above formula. But when you try to make a distribution chart and pass the named range freqcount as one of the series parameters, it returns an error..
Does any one know how to handle this?
Thanks Chandoo…this is an interesting hack! I have never thought to look at frequencies like this.
excel. Also, download the statistical distributions example workbook and play with it.
This download does not work. I get the following message when I click on it.
The compressed zipped folder is invalid or corrupt.
i don’t know why it is doing this, I have never had any probkem opening any of th folders before.
Thanks
Geeta
@Geeta.. it seems to work alright for me. Can you try downloading from another connection?
salam! am so upset 1 din k baad mera buisness math n statics ka paper hai mai BBA ker rhi hoon plz chandoo bhai saab agr aap meri help ker dain tu aapki buhat buhat mehrbani ho giii. buisness mathn statics (302). agr aap k pass solve MCQs hain tu plz woh bhi meri id pe send ker dain aapki buhat buhat nawazish ho gii
best n regardz
shazia hassan