In this article, we are going to show you how to count the number of cells that is less than the average of that data set in Excel.
Formula
<numbers range>: The reference from the range of numbers
How it works
The formula relies on using the COUNTIFS and AVERAGE formulas. The COUNTIFS function can count cells that meet the given criteria. We can use a less than operator “<” (or any other operator) with the AVERAGE function as the criterion. Thus, there are two steps for counting the number of cells less than the average:
- Calculate the average of numbers (AVERAGE)
- Count the numbers less than the average (COUNTIFS)
A minor point to consider here is that you need to merge the less than operator with the AVERAGE formula as a string value “<”. Use ampersand “&” to merge.
Here is the formula for the range B5:B12:
SUMPRODUCT Alternative
Now, let's look at an alternative approach to count the number of cells less than the average. For this method, we are going to be using SUMPRODUCT. The SUMPRODUCT function can evaluate arrays without dealing with array functions. With this, you can use the logical statement directly in the SUMPRODUCT function.
=SUMPRODUCT(N(B5:B12<AVERAGE(B5:B12)))
The B5:B12<AVERAGE(B5:B12) returns an array of Boolean values which indicate whether a value is less than the average. To convert the logical values into numbers (1 for TRUE, 0 for FALSE) you can use the unary operator “--" or N function.
Finally, the SUMPRODUCT function sums the 1 and 0 values to find the count the number of cells less than the average.
