Filtering Data Using Formulas

Formulas are used to create filters.

Creating a Simple Filter

1. Click Data and select Filter or click the Apply Filter button on the toolbar.
2. To use the Formula Builder, click the Simple tab.
3.  Select a column to filter.
4.  Select a expression to use.
5. Enter a value if needed.
6. Select between Constant, Formula, or Column from the drop-down list and build a formula.
7. Select the operator to be used, AND or OR. Only one operator can be used on a simple filter. If multiple operators are needed for filtering, use the advanced filter.

Creating an Advanced Filter

1. Click Data and select Filter or click the Apply Filter button on the toolbar.
2. Click the Advanced tab.
3. Enter a formula such as TIMESTAMP(#A) < TIMESTAMP(NOW()-200000). 

Advanced filter formulas are similar to those used in a workbook's formula bar though without column name completion and less error handling. 

Datameer recommends developing and testing your advanced filter formulas in a separate copied worksheet before applying to your actual data.

Other Filtering Options

You can create a formula that combines multiple conditions using multiple referenced columns, or use nested functions and constants.

Learn more about Datameer functions that can be used in advanced filtering. 

Text-based expressions must return a Boolean value and they are required to reference columns in the current sheet. When the expression, applied to the current record, returns false, the record is dropped. Otherwise it remains.

Examples

• YEAR(#A) >= 2004 && YEAR(#A) <= 2010
• SUM(#A;#C) == 100
• #A < NOW()-7d
• LEN(T(#A)) > 2
• (#A > 70 && #B == false) || (#A < 30 && #B == true)