which returns an array like {1;2;3;4;5}. Note: when entered in a single cell, Excel will display only the first item in the array. Use F9 in the formula bar to see the actual array result. Normally, you will use this formula inside a larger array formula, entered with control + shift + enter. The core of this formula is a string that represents rows. For example, to create an array with 10 numbers, you can hard-code a string into INDIRECT like this: The INDIRECT function interprets this text to mean the range 1:10 (10 rows) and the ROW function returns the row number for each row in that range inside an array....

which displays first item, based on the index provided in the helper column. The same approach is used to display associated sales in column G. For convenience, the worksheet contains the following named ranges: item = B5:B11, sales = C5:C11, sort = D5:D11. At the core, this is a simple INDEX and MATCH formula, where INDEX retrieves a value based on a specified row number: The trick is that the row is calculated with the MATCH function based on values in the sort column: The lookup value in match is generated with the ROWS function and an expanding reference....

The CELL function takes two arguments: info_type and reference. Info_type is a text string that indicates the type of information requested. See the table below for a full list of info types. Reference is a cell reference. Reference is typically a single cell. If reference refers to more than one cell, CELL returns information about the first cell in reference. For certain kinds of information (like filename) the cell address used for reference is optional and can be omitted....

The settlement date is the date the investor takes possession of a security. The maturity date is the date when the investment ends and the principle plus accrued interest is returned to the investor. The frequency is the number of interest payments per year. In the example shown, the formula in F4 is: COUPDAYS returns an integer, so use a number format (not a date format) to display properly....

Use the IFERROR function to trap and handle errors produced by other formulas or functions. IFERROR checks for the following errors: #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!. Example #1 In the example shown, the formula in E5 copied down is: This formula catches the #DIV/0! error that occurs when Qty is empty or zero, and replaces it with zero. Example #2 For example, if A1 contains 10, B1 is blank, and C1 contains the formula =A1/B1, the following formula will catch the #DIV/0!...

Notes Be consistent with inputs for rate. For example, for a 5-year loan with 4.5% annual interest, enter the rate as 4.5%/12. By convention, the loan value (pv) is entered as a negative value. Dave Bruns Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts....

The ISNONTEXT function takes one argument, value, which can be a cell reference, a formula, or a hardcoded value. Typically, value is entered as a cell reference like A1. When value is not text, the ISNONTEXT function will return TRUE. If value is text, ISNONTEXT will return FALSE. Examples The ISNONTEXT function returns TRUE for numbers and FALSE for text: If cell A1 contains the number 100, ISNONTEXT returns TRUE: If cell A1 is empty, ISNONTEXT returns TRUE: If a cell contains a formula, ISNONTEXT checks the result of the formula: Note: the ampersand (&) is the concatenation operator in Excel....

Times can be supplied to the MINUTE function as text (e.g. “7:45 PM”) or as decimal numbers (e.g. 0.5, which equals 12:00 PM). To create a time value from scratch with separate hour, minute, and second inputs, use the TIME function. The MINUTE function will “reset” to 0 every 60 minutes (like a clock). To work with minute values larger than 60, use a formula to convert time to decimal minutes....

To open the Name Manager, navigate to Formulas > Name Manager. Or, you can use the keyboard shortcut Control + F3. Names ranges A named range is one or more cells that have been given a name. Using named ranges can make formulas easier to read and understand. They also provide simple navigation via the Name Box. The screen below shows three of the four names seen above in in the Name Manager in use in a formula based on the FILTER function....

In the example shown, the formula in D6, copied down, is: Format the result as a percentage to display with % symbol. Notes Npery should be an integer (Excel will truncate if not). The effect_rate should be a number between 0 and 1. Dave Bruns Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts....

Explanation One radian is equal to the amount of rotation required to travel one radius along the circumference of the circle, as seen in the image above. A full-rotation or 360° in radians is equal to the value of 2π. A half-rotation or 180° in radians is equal to the value of geometric constant π (pi). Notes To convert radians to degrees, see the DEGREES function. A full-rotation (360°) in radians is equal to 6....

Note: the HYPERLINK function can be used to create a link with a formula. Dave Bruns Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.

In Mac Excel 2016, you can use the same shortcut as Windows, Alt ; Dave Bruns Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.

On Windows, Ctrl 3 also toggles italics. Dave Bruns Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.

VAR ignores text and logicals passed into as cell references. For example, VAR will ignore FALSE when it appears in a range like A1:A10. However, VAR will evaluate logical values, and text representations of numbers hardcoded directly as arguments. Note: Microsoft classifies VAR as a “compatibility function”, now replaced by the VAR.S function. Variation functions in Excel The table below summarizes the variation functions available in Excel. Notes VAR assumes arguments a sample of data, not entire population....

where “list” is the named range B5:B11. This is an array formula and must be entered using control + shift + enter in Legacy Excel. Note: In Excel 365 and Excel 2021, the UNIQUE function provides a better, more elegant way to list unique values and count unique values. These formulas can be adapted to apply logical criteria. In other words, give INDEX the list and a row number, and INDEX will retrieve a value to add to the unique list....

where “range1” is the named range B5:B8, “range2” is the named range D5:D7. The core of this formula is INDEX and MATCH. The INDEX function retrieves a value from range2 that represents the first value in range2 that is found in range1. The INDEX function requires an index (row number) and we generate this value using the MATCH function, which is set to match the value TRUE in this portion of the formula: Here, the match value is TRUE, and the lookup array is created with COUNTIF here: COUNTIF returns a count of the range2 values that appear in range1....

where “data” is an Excel table as shown, and project names in column G match those in column B. Note: MINIFS and MAXIFS are available only in Excel 365 and Excel 2019. In other versions of Excel, you can use a simple array formula, as explained below. Introduction The task here is to find the earliest and latest dates associated with a given project. The earliest dates come from the Start column, and latest dates come from the End column....

With the date “January 9, 2018” in cell B5, the formula returns 9, since January 9 is the 9th day of the year. The result is nth day of the year, based on the date in cell B5. Notice the day argument in the DATE function is supplied as zero. A nice feature of DATE is it can handle day values that are “out of range” and adjust the result appropriately....

In the example shown, the formula used to highlight multiples of 9 is: Note: formula is entered relative to the “active cell” in the selection, cell B4 in this case. When MOD returns zero, the expression returns TRUE and the conditional formatting applies. When MOD returns any other result, the expression returns FALSE conditional formatting is not applied. Note E2 is input as an absolute address ($E$2) to prevent the reference from changing as the conditional formatting rule is evaluated across the selection B4:G11....