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.

Bin Numbers

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.

Frequency Function in Excel

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.

Frequency Distribution

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.

Histogram in Excel

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.

No Extra Cell

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.

Countifs Function

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.

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.

Formula Results

2. However, if we show 16 decimal places, we can see that one result is a very close approximation.

Floating Point Error in Excel

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.

If Function

4. Use the ROUND function to fix this.

Round Function