Calculating the Frequency
In our example from Part 1 of this series, we entered raw data taken from a seed collection experiment into an Excel spreadsheet. We could just calculate the frequency for each individual seed count in our example, but most times it is better to group the data into appropriate intervals. In this example, all of our values are between 2 and 38, so one grouping possibility is to break up the data where the intervals consist of seed counts between 0 and 5, 6 and 10, and so forth.
We’ll start creating a new table in Excel to hold this information. To begin, we’ll create columns for the Interval, Upper Limit of the Interval, Frequency, and Cumulative Frequency. Before entering any formulas, we’ll manually input the information for the interval length and upper limit of the interval into the table. (Click the image for a larger view.)
Next, although this may seem a little backward, we’ll construct a formula to calculate the cumulative frequency of each interval. Go to the first cell in the Cumulative Frequency column of your table. In our example, this would be cell G4. Enter the formula:
In this formula, the range B:B denotes the column that contains all of the seed count values. If the data in your spreadsheet is in a different column, make the appropriate substitutions. The second item, E4, in our formula specifies the upper limit of the interval for which we are calculating the cumulative frequency.
This formula will scan through the values in our data column and count the number of items that are less than or equal to the upper limit of the interval.
Next, copy the frequency formula from the cell and paste it into the other cells in the Cumulative Frequency Column.
Now, we want to use the results from the cumulative frequency values to calculate the frequency for each interval. First, since the frequency and cumulative frequency are the same thing for the first item in the list, enter the following formula into the first cell of the Frequency column:
The value G4 in this formula denotes the first cell in the Cumulative Frequency column in our example. You may need to modify this in your spreadsheet, depending on where your data is entered.
And here's what the table looks like after entering the formula.
For the rest of the cells, we will calculate the frequency by subtracting the cumulative frequency of the previous interval from the cumulative frequency of the present interval using the formula:
Again, you may have to make adjustments based on which cells contain the data in your worksheet.