Frequency
The FREQUENCY function in Excel calculates how often values occur within the ranges you specify in a bin table. You can also use the COUNTIFS function to create a frequency distribution.
1. First, enter the bin numbers (upper levels) in the range C4:C8.
2. Select the range D4:D9 (extra cell), enter the FREQUENCY function shown below (without the curly braces) and finish by pressing CTRL + SHIFT + ENTER.
Note: the formula bar indicates that this is an array formula by enclosing it in curly braces {}. To delete this array formula, select the range D4:D9 and press Delete.
3. Hide the column with the bin numbers (upper levels) and insert a column with proper bin labels.
Explanation: 1 value is less than or equal to 20, 8 values are greater than or equal to 21 and less than or equal to 25, etc. At step 2, we selected the range D4:D9 (instead of the range D4:D8). As a result, the FREQUENCY function also counts the number of values that are greater than 40.
4. You can also use the Analysis Toolpak to create a histogram.
5. Change the bin numbers. Select the range D4:D9 (no extra cell), enter the FREQUENCY function shown below (without the curly braces) and finish by pressing CTRL + SHIFT + ENTER.
Explanation: the last bin number is greater than or equal to the maximum value (52). As a result, we don’t need an extra cell to count the number of values that are greater than 60.
6. You can also use the COUNTIFS function to create a frequency distribution.
Explanation: the COUNTIFS function in Excel counts cells based on two or more criteria. The COUNTIFS function shown above has 2 range/criteria pairs. The & operator joins “>=” with the value in cell C4 and “copy this formula to the other cells.
Next Chapter: Round
Floating Point Errors
Excel stores and calculates floating point numbers. Sometimes, the result of a formula is a very close approximation.
1. For example, take a look at the formulas below. At first glance, everything looks alright.
2. However, if we show 16 decimal places, we can see that one result is a very close approximation.
You don’t have to worry about floating point errors. They are rare.
3. Even if your worksheet contains a floating point error, in most cases, this causes no problems. However, if you compare the value in cell C8 with another value, the following problem can occur.
4. Use the ROUND function to fix this.
Next Chapter: Array Formulas
