In a previous post, I explained how to apply filters to functions such as **MEDIAN**. The example in that post showed how to create a function with a *single* filter. What if instead we want to scale it to *several* filters within a single function?

I’ll use the following example to illustrate the approach:

This table, named **Data**, contains transactions of customers who bought various products. Each line shows how many items of a given product were purchased and the total payment for that transaction.

We want to create a table showing actual unit costs of products for three intervals of quantity purchased:

- less than 30 products
- between 30 and 60 products
- more than 60 products

In other words, we want to fill the following table:

We can use the **AVERAGEIFS** function here, but we’re uncertain about possible outliers and, hence, would prefer to apply the **MEDIAN** function instead.

Now, the formulae in the three columns of the results table will be clearly similar, so I’ll concentrate on the first column that is to be filled, with unit cost of products when less than 30 items are purchased.

After reading the post about on-the-fly filtering, you might be tempted to use the following solution:

{=MEDIAN(IF(AND(Data[Product] = [@Product], Data[Quantity] < 30), Data[Total Payment] / Data[Quantity], “”))}

However, you’ll receive a **#VALUE!** error here. You can spend hours trying to figure out why, but the reason is quite simple, though unexpected:

ANDandORfunctions dosupport arrays.not

So you have to find an alternative.

If you studied mathematical logic in a university or are otherwise familiar with Boolean (also known as binary) arithmetic, you’ll quickly recall the following rules:

- TRUE can be represented as 1 and FALSE as 0
- An
ANDbinary operation, or logical conjunction, is equivalent to a numeric multiplication (anythingAND FALSEis FALSE, similarly to anything multiplied by zero being zero)- An
ORbinary operation, or logical disjunction, is equivalent to a numeric addition in case you operate with more than one bit (anythingOR TRUEis not zero and, hence,TRUE).The remark about operating with more than one bit is in place for mathematics fans: if you operate with one bit, 1 + 1 = 0, which is a XOR operation. I’m talking about cases when 1 + 1 = 10_{2}= 2_{10}> 0.

Taking that into account, we have to convert the

AND(Data[Product] = [@Product], Data[Quantity] < 30)

part of the above formula to an equivalent expression with multiplication and, as we’re interested in testing whether the expression is true, comparing it with 1:

(Data[Product] = [@Product]) * (Data[Quantity] < 30) = 1,

so that the formula now looks as:

{=MEDIAN(IF((Data[Product] = [@Product]) * (Data[Quantity] < 30) = 1, Data[Total Payment] / Data[Quantity], “”))}

This works perfectly, and this approach can be readily applied to the remaining two columns. As a result, we get:

## 3 thoughts on “Advanced On-the-Fly Filters in Excel Functions”