way to effectively draw a frequency distribution graph.
You do this by making use of the frequency function in Excel and then drawing a graphs with two series of the same data.
First, you decide which frequency groups you want to plot. My data fell in the range of 1 to over 1500 minutes, with a lot of the data towards lower numbers. I decided to group my data in blocks of 25 minutes. This means that I want to see how many of the processes I measured took between 0 and 25 minutes, how many took between 25 and 50 minutes, etc. I set a maximum of 450 for my analysis, so the final group was how many of the processes took 450 minutes or more.
I then setup by data groupings as numbers in successive cells of a column in a spreadsheet:
Next, I used the frequency function to have Excel count the number of occurrences of my data values that fall within each group. When you use the function across a range of cells like this, you have to enter it as an array function. Array functions in Excel are very powerful, but somewhat difficult to understand at first. Essentially they let you do operations on a group of cells at one time, rather than just one cell at a time.
The formula I will use is the following, where the DataSeries!B2:B42 is the group of cells that contain my actual data, and the A7:A25 is the range of cells that contains the frequency groupings I just talked about above:
=FREQUENCY(DataSeries!B2:B42,A7:A25)To enter this, you first highlight all the cells in the output range - B6 to B25 in my case above. Note that I highlight one cell past the last number in my grouping range so that Excel will put the number of instances greater than the last number in the grouping range.
After you highlight all the cells in the output range, you then enter the frequency formula above in the data entry form at the top of the Excel window:
Now, rather than pressing <ENTER> after typing in the formula, press <CTRL><SHIFT><ENTER>. This enters the formula as an array formula and tells Excel to apply the formula to all the highlighted cells. After doing this, I had the counts of data that I wanted:
You can now quite easily create a graph with this data that almost is what we’re after.
To change an array formula into a normal formula, select the whole array, hit F2 to edit the formula, and then Ctrl+Enter
Tags : Excel
