where location (B5:B12) and distance (C5:C12) are named ranges. Working from the inside out, we are using the XMATCH function to find the position of the nearest location: We do that by setting lookup value to zero (0), lookup array to the distance (C5:C12), and match mode to 1. A match mode value of 1 tells XMATCH to find an exact match or next largest value. Since lookup value is provided as zero (0), XMATCH will find the first distance greater than zero....

which trims " miles" from each value returning just the number. The VALUE function converts text to a numeric value. The challenge, for values with variable length, is that we don’t know exactly how many characters to extract. That’s where the LEN function is used. Working from the inside out, LEN calculates the total length of each value. For D6 (736 miles) the total length is 9. To get the number of characters to extract, we subtract 6, which the length of " miles", including the space character....

which generates a series of 12 dates, incremented by one year, beginning with May 1, 2019. SEQUENCE can generate results in rows, columns, or both. In this case, SEQUENCE is configured to output an array of numbers that is 12 rows by 1 column: The starting number is the year value from B5, and the step value defaults to 1, so SEQUENCE outputs an array like this: This array is returned to as the year argument inside the DATE function, which causes results to spill into the range D5:D16....

Note: these is an experimental formula that uses a hard coded array constant, set down here for reference and comment. Casually tested only, so take care if you use or adapt. To split a number from a unit value, you need to determine the position of the last number. If you add 1 to that position, you have the start of the unit text. This formula uses this concept to figure out where the unit of measure begins....

The result is the square root of each number in column B. To get the square root of 16: To get the square root of a number in cell A1: Negative numbers If you give SQRT a negative number, it returns a #NUM! error: To use the SQRT function with negative numbers you nest the ABS function inside SQRT like this: The ABS function converts the negative number to a positive number and returns the result to the SQRT function, which calculates a final result....

which calculates the volume of a rectangular prism, given the length in column B (l), the width in column C (w) and the height in column D (h). Units are indicated generically with “u”, and the resulting volume is in units cubed (u3). This formula can be represented in Excel with the multiplication operator (*) like this In the example shown, the formula in E5 is: As the formula is copied down the column, it calculates a new volume in each row, using the length in column B (l), the width in column C (w) and the height in column D (h)....

The result is that negative values are forced to zero, values over 1 are capped at 1, and values between 0 and 1 are unaffected. Note: all values formatted with percentage number format. The goal of this example is to limit incoming percentage values so that they fall within an upper and lower threshold. Negative values and values over 100% are not allowed, so the final result must be a number between zero and 1 (0-100%), inclusive....

How to make this chart 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.

which returns 3, since there are three unique names in B6:B15 associated with Omega. Note: this formula requires Dynamic Array Formulas, available only in Excel 365. With an older version of Excel, you can use more complex alternative formulas. At the core, this formula uses the FILTER function to apply criteria, and the UNIQUE function to extract the unique values that remain. Working from the inside out, the FILTER function is used to apply criteria and extract only names that are associated with the “Omega” project: Notice the if_empty argument in FILTER is set to an empty string (""), which is important due to the way we count final results....

Examples The formulas below show how the AREAS function can be configured. Notice the first example does not need an extra set of parentheses, since there is just one reference). However, the examples following do need the extra set of parentheses. 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 DEVSQ function takes multiple arguments in the form number1, number2, number3, etc. up to 255 total. Arguments can be a hardcoded constant, a cell reference, or a range. A single range or array can be provided instead of multiple arguments. Examples In the example shown, the formula in G5 is: The formulas in C5 and D5 are, respectively: The value in D12 (34) is simply the sum of D5:D10, and agrees with the value calculated directly by DEVSQ in G5....

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 Excel 2016 on a Mac, you can actually type letters to select menu items in the right-click menu. For example “e” will select “Edit comment”. However, there doesn’t seem to be a key that selects the Delete comment item. On a Mac, you can use arrow keys: 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....

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.

If you use TechSmith’s Snagit 12+ on Windows, be aware that it’s default shortcut keys may conflict with this Excel shortcut. 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.

To move to the last tab/worksheet in a workbook, hold down the control key and click the right navigation arrow in the lower left corner of the workbook. On a Mac, you can also use Option + right arrow. 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....

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.

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.

Using Degrees To supply an angle to TAN in degrees, multiply the angle by PI()/180 or use the RADIANS function to convert to radians. For example, to get the TAN of 60 degrees, you can use either formula below: Explanation The graph of the tangent function, shown above, visualizes the output of the function for angles from 0 to a full rotation corresponding to the range [0, 2π]. The function has two vertical asymptotes within the range [0, 2π] where the output diverges to infinity....

where trip (B5:B14) and cost (C5:C14) are named ranges. In F5, F6, and F7, the formula returns the trip closest in cost to 500, 1000, and 1500, respectively. Note: this is an array formula and must be entered with control + shift + enter, except in Excel 365. Taking things step-by-step, the lookup value is calculated with MIN and ABS like this: First, the value in E5 is subtracted from the named range cost (C5:C14)....