Dave Bruns

Data validation rules are triggered when a user adds or changes a cell value.

This custom validation formula simply checks the year of any date against a hard-coded year value using the YEAR function.

When a user enters a value, the YEAR function extracts and compares the year...Read more

Data validation rules are triggered when a user adds or changes a cell value.

The TODAY function returns today's date (recalculated an on on-going basis). The AND function takes multiple logical expressions and returns TRUE only when all expressions return TRUE. In this case, we need to...Read more

Data validation rules are triggered when a user adds or changes a cell value.

The AND function takes multiple arguments (logical expressions) and returns TRUE only when all arguments return TRUE. In this case, we need two conditions:

Logical 1 tests if the input is a number...Read more

Data validation rules are triggered when a user adds or changes a cell value.

The AND function takes multiple arguments (logicals) and returns TRUE only when all arguments return TRUE. The DATE function creates a proper Excel date with given year, month, and day values.

Because...Read more

Data validation rules are triggered when a user adds or changes a cell value. Cell references in data validation formulas are relative to the upper left cell in the range selected when the validation rule is defined.

The ISTEXT function returns TRUE when a value is text and FALSE if not...Read more

Data validation rules are triggered when a user adds or changes a cell value.

The ISNUMBER function returns TRUE when a value is numeric and FALSE if not. As a result, all numeric input will pass validation.

Be aware that numeric input includes dates and times, whole numbers, and...Read more

In this example, the goal is to sum the last n columns in a set of data, where n is a variable that can be changed at any time. In the latest version of Excel, the easiest way to solve this problem is with the TAKE...Read more

In this example, the goal is to create a reference to an external workbook with variable information. The easiest way to do this is to assemble  the reference to a range or cell in another workbook as a text value, then use the INDIRECT function to convert the...Read more

The goal is to calculate a tax amount with both fixed and variable components according to the following logic:

1. If the amount is less than $1000, only the base tax applies.
2. If the amount is $1000 or greater, the result is the base tax + 15% * the amount over $
...Read more

In this example, the goal is to look up a given income value in a tax table and return the correct tax rate for that income. The tax rate is organized into 5 tiers in the range F5:F9 with the corresponding tax rate in the range G5:G9. For convenience, the range F5:G9 is named ...Read more