Note: This example assumes the start date will be provided as the first of the month. See below for a formula that will dynamically return the first day of the current month.
With the layout of the grid as shown, the main problem is to calculate the date in the first cell in the...Read more
Dates in Excel are just serial numbers, formatted to display as dates. This means you can perform math operations on dates to calculate days in the future or past.
In the example shown, the date in the named range "start" is provided by the TODAY...Read more
The example on this page shows a simple array formula. Working from the inside out, the expression:
C5:C12-D5:D12
Results in an array containing seven values:
{17;19;32;25;12;26;29;22}
...Read moreIt is surprisingly tricky to get INDEX to return more than one value to another function. To illustrate, the following formula can be used to return the first three items in the named range "data", when entered as a multi-cell array formula.
{=INDEX(data,{1,...Read moreThe heart of this formula is a basic INDEX and MATCH formula, used to translate text values into numbers as defined in a lookup table. For example, to translate "EX" to the corresponding number, we would use:
=INDEX(value,MATCH("EX",code,0))
...Read moreIn this example, the goal is to return the most frequently occurring text based on one or more supplied criteria. Working from the inside out, we use the MATCH function to match the text range against itself, by giving MATCH the same range for lookup value...Read more
The MODE function has no built-in way to apply criteria. Given a range, it will return the most frequently occurring number in that range.
To apply criteria, we use the IF function inside MODE to filter values in a range. In this example, the IF function filters values by group with an...Read more
In this example, the goal is to create a data validation rule that will only accept numbers that are a specific multiple of another number. In the worksheet shown, the multiple is 100. Data validation rules are triggered when a user adds or changes a cell value. When a custom formula returns...Read more
Excel contains many built-in "presets" for highlighting values with conditional formatting, including a preset to highlight cells greater than a specific value. However, by using your own formula, you have more flexibility and control.
In this example, a conditional formatting...Read more
There is no built-in function to perform a series of find and replace operations in Excel, but you can create a formula that does the same thing. The goal in this example is to replace multiple ("find") values with corresponding ("replace") values in a single "replace all" operation. The...Read more