At the beginning of the year I talked about the
FREQUENCY() worksheet function and gave a handful of examples on how it can be used. Today’s article is another, brilliant
FREQUENCY() example which was posted by Barry Houdini on the MrExcel board. I’ve picked up quite a few formula tricks from Barry over the years and this one is up there with the best of them. Barry humbly tells me that he in turn picked up the technique from Aladin Akyurek, another expert on the MrExcel board, so full credit to both of them.
We have some phone call information about three people working in a customer services team.
The count of phone calls is split into two types, ACD and AMS, for each person and we need to get the ACD mean average from the data set. We’ve also been told that the average should not take into account any 0 values in the cells so the correct answer would be 56:
(24 + 21 + 99 + 67 + 87 + 6 + 88) / 7 = 56
In this case we want to do a conditional average: we want to ignore cells which contain 0’s. Usually we could do this by using the
AVERAGEIF() function but we can’t do that here because the ACD data is in three separate, or non-contiguous, ranges. This formula will return a
#VALUE! error because
AVERAGEIF() can’t handle non-contiguous ranges:
To get an average from a non-contiguous set of ranges we could usually use the
COUNT() worksheet functions like so:
The problem is the
COUNT() will include the 0’s, so the formula returns a result of 26.13 which is incorrect. We could try to adjust the formula to ignore zeros by using
COUNTIF() instead of
COUNTIF() can’t be used on non-contiguous ranges either so this will also return
So the question is how can we get a count of non-zero values from those cells?
The solution Barry posted to this quandary is as follows:
Note that this formula does not need to be CTRL+SHIFT+ENTER’d.
SUM() part of the formula is straightforward enough, but let’s have a closer look at the rest of it. If you feel that you need a quick refresher on the
FREQUENCY() function then take a look at my previous blog entry.
The key point is that
FREQUENCY() can be used on non-contiguous ranges: the
FREQUENCY() will return an array of two values: a count of the ACD numbers which fall into the first bucket of 0, and a count of the ACD numbers which are greater than 0:
It’s a safe assumption that there will not be any negative numbers in this particular data set so the 8 is the count of values which are exactly equal to 0 and the 7 is the count of values which are greater than 0. The 7 is the number we’re interested in, so next we use the
INDEX() function to extract it from the array:
and then we divide the sum of the values by 7 to get the correct answer of 56.
evaluates to our final answer:
The formula will still work if there are empty cells or even if the non-contiguous ranges are different sizes. Pretty slick.
I liked the write up Colin – I agree, it is a very slick solution! Houdini-esque indeed!
Nice explanation Colin – I definitely like this use of FREQUENCY but another approach is possible, using the data in B2:G2 (header row), i.e. this “array formula”
Pingback: Averaging cells with values
10 Many IF’s Barry, 01 Should Do :-)
Pingback: Ignoring N/As (excel 2010)
Is possible to use COUNTIF in non-continuous ranges.
The formula below worked for me
The criteria in COUNTIF should be
Something is going wrong. I can’t post the correct criteria.
Different than 0 is the proper criterium