Advanced On-the-Fly Filters in Excel Functions

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:

advanced-on-the-fly-filters-in-excel-functions-data

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:

advanced-on-the-fly-filters-in-excel-functions-results-table-empty

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:

AND and OR functions do not support arrays.

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:

  1. TRUE can be represented as 1 and FALSE as 0
  2. An AND binary operation, or logical conjunction, is equivalent to a numeric multiplication (anything AND FALSE is FALSE, similarly to anything multiplied by zero being zero)
  3. An OR binary operation, or logical disjunction, is equivalent to a numeric addition in case you operate with more than one bit (anything OR TRUE is 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 = 102 = 210 > 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:

Advanced On-the-Fly Filters in Excel Functions - Results Table.PNG

Advertisements

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s