solution - advanced filter w/ stock data


The Advanced Filter criteria range is shown in green.


Table 1


xlf-advanced-filter-1.1
Fig 1: Show only the records with NA errors
xlf-advanced-filter-1.2
Fig 2: Hide all the records with NA errors
xlf-advanced-filter-1.3
Fig 3a: Show all the records with NA errors, plus preceding and succeeding rows

Note: isNA before and after could be written as:

The filter criteria shown in figure 3a is simpler.


Discussion


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 =OR(ISNA(B3),ISNA(B4),ISNA(B5)).


With formula line breaks, this is equivalent to:

  1. =OR(
  2. ISNA(B3),
  3. ISNA(B4),
  4. ISNA(B5)
  5. )

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.


xlf-filter-helper-column
Fig 3b: Filter helper column for use with AutoFilter - show all the records with NA errors, plus preceding and succeeding rows

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:


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.


Table 2


xlf-advanced-filter-2.1
Fig 4: Show Monday AND Friday. Assume that Monday is WEEKDAY number 1

xlf-advanced-filter-2.2
Fig 5: Hide all records where the market closed lower than the open

xlf-advanced-filter-2.3b
Fig 6: Show all records where the daily volume is greater than the average daily volume for the month

Details of selected Excel function used in this module.


Excel functionDescription
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
[return_type]:
   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)