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.

**The Problem**

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:

`=AVERAGEIF((B3:B7,D3:D7,F3:F7),"<>0")`

To get an average from a non-contiguous set of ranges we could usually use the `SUM()`

and `COUNT()`

worksheet functions like so:

`=SUM(B3:B7,D3:D7,F3:F7)/COUNT(B3:B7,D3:D7,F3:F7)`

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 `COUNT()`

, but `COUNTIF()`

can’t be used on non-contiguous ranges either so this will also return `#VALUE!`

:

`=SUM(B3:B7,D3:D7,F3:F7)/COUNTIF((B3:B7,D3:D7,F3:F7),"<>0")`

So the question is how can we get a count of non-zero values from those cells?

**The Solution**

The solution Barry posted to this quandary is as follows:

`=SUM(B3:B7,D3:D7,F3:F7)/INDEX(FREQUENCY((B3:B7,D3:D7,F3:F7),0),2)`

Note that this formula does not need to be CTRL+SHIFT+ENTER’d.

The `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.

`FREQUENCY((B3:B7,D3:D7,F3:F7),0)`

The key point is that `FREQUENCY()`

can be used on non-contiguous ranges: the `data_array`

is `(B3:B7,D3:D7,F3:F7)`

. The `bins_array`

is `0`

, so `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:

`FREQUENCY((B3:B7,D3:D7,F3:F7),0)`

evaluates to

`{8;7}`

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:

`INDEX({8;7},2)`

evaluates to

`{7}`

and then we divide the sum of the values by 7 to get the correct answer of 56.

`SUM(B3:B7,D3:D7,F3:F7)/{7}`

evaluates to:

`392/{7}`

evaluates to our final answer:

`{56}`

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!

LikeLike

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”

=AVERAGE(IF(B2:G2=”ACD”,IF(B3:G7>0,B3:G7)))

LikeLike

Pingback: Averaging cells with values

10 Many IF’s Barry, 01 Should Do :-)

Array enter

=AVERAGE(IF((B3:G7>0)*(B2:G2=”acd”),B3:G7))

LikeLike

Pingback: Ignoring N/As (excel 2010)

Is possible to use COUNTIF in non-continuous ranges.

The formula below worked for me

=SUM(B3:B7,D3:D7,F3:F7)/SUM(COUNTIF(INDIRECT({“B3:B7″,”D3:D7″,”F3:F7″}),”0”))

LikeLike

The criteria in COUNTIF should be

“0”

LikeLike

ooops…

Something is going wrong. I can’t post the correct criteria.

Different than 0 is the proper criterium

LikeLike