Setting Up a Condition as the Result of a Formula

Amazingly, there is an incredibly obscure version of Advanced Filter criteria that can replace the 44,000-row criteria range in the case study. In the alternate form of criteria range, the top row is left blank. There is no heading above the criteria. The criteria set up in Row 2 are a formula that results in True or False. If the formula contains any relative references to Row 2 of the input range, then Excel compares that formula to every row of the input range, one by one.

For example, if we wanted all records where the Gross Profit Percentage was below 53%, the formula built in J2 would reference the Profit in H2 and the Revenue in F2. We would leave J1 blank to tell Excel that we are using a formula-based criterion. Cell J2 would contain the formula =(H2/F2)<0.53. The criteria range for the advanced filter would be specified as J1:J2.

As Excel performs the Advanced Filter, it logically copies the formula and applies it to all rows in the database. Anywhere that the formula evaluates to True, the record is included in the output range.

This is incredibly powerful and it runs remarkably quickly. You can combine multiple formulas in adjacent columns or rows to join the formula criteria with AND or OR, just as you do with regular criteria.

0 0

Post a comment