solution - advanced filter w/ stock data
The Advanced Filter criteria range is shown in green.
isNA before and after could be written as:
isNA before and after
The filter criteria shown in figure 3a is simpler.
Advanced filter - complex criteria: to further explain the logic of the stacked ISNA structure in rows 63 to 65 of figure 3a, you need to realise that this is equivalent to an Excel OR function, and can be written as
With formula line breaks, this is equivalent to:
With the use of a helper column (label Helper),
=OR(ISNA(B3),ISNA(B4),ISNA(B5)) is added in the first data row of the table in figure 3b, then filled down. The worksheet OR function retrurns TRUE if any condition is true. Conditional formats are applied to emphasise the TRUE records. If the Helper field is included in the Advanced Filter List range: and the filter criteria set to
then the same result as in figure 3a would be returned.
But an important feature of the Advanced Filter is the ability to filter on a formula (complex criteria) without the need of helper fields.
Based on the helper cell logic, an alternate filter criteria is:
isNA before and after
The advanced criteria only refers to the first record of the data field (row 4). Reference to rows 3 and 5 should be viewed in the context of the
=ISNA(OFFSET(B4,... explanation provided earlier in the section.
Details of selected Excel function used in this module.
|ISNA(value)||Returns TRUE if value is a #N/A (value not available) error value|
|NOT(logical)||Returns the reverse the value of the logical argument|
|WEEKDAY(serial_number,[return_type])||Returns the day of the week, 1 to 7, corresponding to a date|
1 (default) Numbers 1 (Sunday) to 7 (Saturday)
2 Numbers 1 (Monday) to 7 (Sunday)
3 Numbers 0 (Monday) to 6 (Sunday)
11 Numbers 1 (Monday) to 7 (Sunday)
12 Numbers 1 (Tuesday) to 7 (Monday)
13 Numbers 1 (Wednesday) to 7 (Tuesday)
14 Numbers 1 (Thursday) to 7 (Wednesday)
15 Numbers 1 (Friday) to 7 (Thursday)
16 Numbers 1 (Saturday) to 7 (Friday)
17 Numbers 1 (Sunday) to 7 (Saturday)
- Other criteria formula specifications are possible
- The Excel solution file for this module: xlf-advanced-filter-solution-b.xlsm 20KB
- This module was developed in Excel 2016, 64 bit
- Published: 10 February 2015
- Revised: Friday 14th of February 2020 - 05:08 PM, [Australian Eastern Standard Time (EST)]