Explanation The goal is to lookup a feed rate based on material, hardness, and drill bit diameter. Feed rate values are in the named range data (D6:H16). This can be done with a two-way INDEX and MATCH formula. One MATCH function works out the row number (material and hardness), and the other MATCH function finds the column number (diameter). The INDEX function returns the final result. In the example shown, the formula in K8 is:...

Explanation XLOOKUP can handle arrays natively, which makes it a very useful function when constructing criteria based on multiple logical expressions. In the example shown, we are looking for the order number of the first order to Chicago over $250. We are constructing a lookup array using the following expression and boolean logic : (D5:D14="chicago")*(E5:E14>250) When this expression is evaluated, we first get two arrays of TRUE FALSE values like this:...

Hi, I’m Dave Bruns. I founded Exceljet in 2012, and I’ve spent more than a decade writing about Excel, with a special focus on functions and formulas. My goal with Exceljet is simple: I want to help ordinary people learn Excel with practical examples that are quick, clean, and to the point. Exceljet has grown to include thousands of articles and videos created with that idea in mind. Excel is an incredibly powerful application, but it’s also complicated....

Explanation In this example, the goal is to calculate the time remaining before an expiration date. There are many ways to do something like this in Excel, and in this article, we’ll look at three different approaches: Calculating the remaining time in days Calculating the remaining time in years, months, and days Calculating the percentage of shelf life remaining. Each approach includes an explanation of how the formula works. Important notes Examples use the named range “cdate”, which points to a cell that contains the “current date”....

Purpose Return value Syntax =COLUMNS(array) array - A reference to a range of cells. Using the COLUMNS function The COLUMNS function returns the count of columns in a given reference as a number. For example, COLUMNS(A1:C3) returns 3, since the range A1:C3 contains 3 columns. COLUMNS takes just one argument, called array , which should be a range or array . Examples Use the COLUMNS function to get the column count for a given reference or range....

Transcript In this video we’ll look at how to build a formula that calculates a 401k match using several nested IF statements . In the US, many companies match an employee’s retirement deferral up to a certain percent. In this example, the match has two tiers: In Tier 1, the company matches 100% up to 4% of the employee’s compensation. In Tier 2, the company matches 50% on deferrals between 4% and 6%....

Purpose Return value Syntax =COMPLEX(real_num,i_num,[suffix]) real_num - The real part of the complex number. i_num - The imaginary part of the complex number. suffix - [optional] The suffix for the imaginary unit, either “i” or “j”. Using the COMPLEX function The COMPLEX function returns the string representation of a complex number. For example: =COMPLEX(4,3) // returns "4+3i" To use the “j” instead of “i”: =COMPLEX(4,3,"j") // returns "4+3j" To enter the value of a complex number without using the COMPLEX function, write it in a formula like this:...

Explanation Consider for a moment how overlapping dates work. For a project to overlap the dates of other projects, two conditions must be true: The start date must be less than or equal (<=) to at least one other end date and the list. The end date for the project must be greater than or equal to (>=) at least one other start date in the list. If both of these conditions are true, the project dates must overlap with another project in that list....

Explanation In this example, the goal is to count cells in the range D5:D15 that contain “red” or “blue”. For convenience, the D5:D15 is named color . Counting cells equal to this OR that is more complicated than it first appears because there is no built-in function for counting with OR logic. The COUNTIFS function will allow multiple conditions, but all conditions are joined with AND logic. The article below explains several options....

Explanation SUMPRODUCT accepts the range B3:B6 as an array of four cells. For each cell in the array, LEN calculates the length of the text as a number. The result is an array that contains 4 numbers. SUMPRODUCT then sums the items in this array and returns the total. Explanation In this example, the goal is to count the total number of words in a cell. Excel doesn’t have a dedicated function for counting words....

Explanation This formula is more complicated than a similar formula that uses FREQUENCY to count unique numeric values because FREQUENCY doesn’t normally work with non-numeric values. As a result, a large part of the formula simply transforms the non-numeric data into numeric data that FREQUENCY can handle. Working from the inside out, the MATCH function is used to get the position of each item that appears in the data: MATCH(B5:B14,B5:B14,0) The result from MATCH is an array like this:...

Explanation The NOW function takes no arguments; it is entered with empty parentheses. When you enter the NOW function in a cell, it will display the current date and time. Each time the worksheet is recalculated or opened, the date and time will be updated. To display only the time component, format the cell using a time format. If you want to display the only the date, you can format the cell with a date format that does not display time, or you can use the TODAY function , which only inserts the date component....

Transcript In this video, we’ll look at how to highlight high and low values in an Excel chart using data labels. This worksheet contains daily sales numbers for a small online store. I’ll plot the data in a basic column chart. Let’s say we want to highlight the highest and lowest values by showing these values directly over the bars. This may seem tricky, but we can build a very straightforward solution using only data labels with a simple formula....

About This Shortcut This shortcut is an alternative to pressing enter/return when entering data. It’s useful when the next value you want to enter is to the left. About This Shortcut With a single cell selected, this shortcut will enter a value and leave the same cell selected.

The term “eta lambda” is short for “eta reduced lambda”. Although the name sounds complicated, the intent is actually the reverse. Eta lambda refers to a simpler syntax for passing in calculations to newer functions like BYROW, BYCOL, SCAN, REDUCE, etc., which are designed to accept a lambda expression. The concept of eta reduction In lambda calculus, “eta” refers to the simplification of a function (more technically, the “eta-reduction of a function”), when it behaves identically for all inputs....

About This Shortcut If the active cell is empty, Excel will extend the selection to the first non-empty cell above the active cell. If the active cell is non-empty, Excel will extend the selection on the last non-empty cell above the active cell. On a Mac, the command key (⌘) can be used instead of the control key. About This Shortcut If the active cell is empty, Excel will extend the selection to the first non-empty cell below the active cell....

A function argument is a specific input to a function. For example, the VLOOKUP function takes four arguments as follows: =VLOOKUP (value, table, col_index, [range_lookup]) Note most arguments are required, but some are optional. In Excel, optional arguments are denoted with square brackets. For example, the fourth argument in VLOOKUP function, range_lookup, is optional and appears in square brackets as shown above. Finally, many Excel functions accept multiple optional arguments, which are denoted with an ellipses (…)...

Explanation The goal is to retrieve the value of a cell at a given row and column location, which are entered in cells G5 and G4, respectively. There are several ways to go about this, depending on your needs. See below for options. ADDRESS and INDIRECT In the example worksheet, the ADDRESS function is combined with the INDIRECT function to solve this problem with the following formula: =INDIRECT(ADDRESS(G4,G5)) The ADDRESS function returns the address for a cell based on a given row and column number like this:...

Transcript There may be times when you want to add the same field to a Pivot Table more than once. For example, you might want to show a sum with a count, or a sum with a percentage. Let’s take a look. Let’s start off by adding Product as a Row Label. Then let’s add Total Sales as a Value. As usual, we get the sum of Total Sales. If we add Total Sales again to the Values area, we get two instances of the field, both summed....

Transcript In this video we’ll look at how to calculate minimum and maximum values in Excel. Let’s take a look. Here we have a list of properties, that includes an address, a price, and a variety of other information. Let’s calculate the maximum and minimum values in this list. First, I’m going to create a named range for the prices in the list. You don’t need to do this, but it makes the formulas easier to read and copy....