One challenge that comes up frequently when sorting is a need to sort in a custom order. For example, in this worksheet, we have a list of opportunities, and we want to sort the list in the order that stages appear in column H. How can we do this with a formula? Well, to start off, if we try to sort the data with the SORTBY function, and provide the list of stages for the sort_by array, I’ll get an error....
The result is a horizontal array with nine characters that spills into the range D5:L5. Typically, the MID function is used to extract one or more characters from a text string. So, for example, you could use MID like this: The trick in this formula is to ask MID for all characters in the text string by providing an array for the start_num argument. For example, to ask for the three letters in “red”, we can use an array like this: To generate the array we need for num_chars we use the SEQUENCE function....
As the formula is copied across and down, it returns a sum of values associated with “A” and “B” in the table to the left. Working from the inside out, SUMPRODUCT is configured with two arguments, array1 and array2. The first array, array1, is set up to act as a “filter” to allow only values that meet criteria: Note: both references above are mixed references. $B5:$H5 has the columns locked, so that the formula can be copied across....
where things is the named range G5:G7. The result is $19.50, the sum of Price where Item is “Apples”, “Pears”, or “Kiwis”. SUMIFS with SUMPRODUCT One way to accomplish this is to give the SUMIFS function all three values in the named range things (G5:G7) as criteria, then use the SUMPRODUCT function to calculate a total. This is the approach in the worksheet shown, where the formula in I5 is: Working from the inside out, the SUMIFS function takes three arguments: sum_range, range, and criteria....
which returns 12, the total cells in the range B5:C10. The ROWS and COLUMNS functions can work with any sized rectangular range. For example, the formula below uses the ROWS function to count all rows in the column A, and the COLUMNS function to count all columns in row 1: ROWS returns a count of 1,048,576 and COLUMNS returns a count of 16,384, to the final result is 17,179,869,184....
There are many situations in which you may want to use logical criteria to filter or limit the values processed by the UNIQUE function. In this first worksheet, we have a list of values, some of which are duplicates. Notice the list also contains empty or blank cells. If I use the UNIQUE function on the data as-is, we do get unique values, but we also get a zero, since the source data contains empty cells....
VLOOKUP retrieves the correct building for each employee from Sheet2 into the table on Sheet1. On Sheet1, we retrieve the building location for each team member using this formula: The lookup value is the employee ID, from cell B5. For the table array, we use the range $B$5:$C$104 qualified with a sheet name, and locked as an absolute reference, so that the range does not change as the formula is copied down: This is the only difference from a normal VLOOKUP formula – the sheet name simply tells VLOOKUP where to find the lookup table range, B5:C104....
Pivot charts let you rapidly analyze large amounts of unsummarized data in different ways. Unlike normal charts, Pivot charts can be used to plot data with hundreds or thousands of rows. For example, on this worksheet, I have order data from a wholesale chocolate company over a period of 2 years, in almost 2000 rows of data. To introduce the idea of a pivot chart, I’ll first create a normal chart....
In this case, we want to find out of a particular sheet exists in a workbook, so we construct a full reference by concatenating the sheet names in column B with an exclamation mark and “A1”: This returns the text: which goes into the INDIRECT function. INDIRECT then tries to evaluate the text as a reference. When INDIRECT succeeds, the reference is passed into ISREF which returns TRUE. When INDIRECT can’t create a reference, it throws a #REF error, and ISREF returns FALSE....
Prominent at the top of the Excel window, the ribbon is a kind of super toolbar. The ribbon contains every command you’ll ever need in Excel, grouped by logical function into tabs. It’s a great way to explore what Excel can do. Let’s take a look. The ribbon is divided into tabs. Each tab contains logically grouped commands, further sub-divided into Groups. In some cases, there are more commands than available space, and you’ll see a small arrow in the lower right....
In an earlier video, I showed how AND logic corresponds to multiplication and OR logic corresponds to addition. Let’s look at how to apply this in an array formula. In the first worksheet, we want to sum orders where the state is Texas or “tx” and the amount is greater than 125. I’ll work through the problem step-by-step. In column H, I’ll test if state is equal to “tx”. In column I, I’ll test if the amount is greater than 125....
where “key” is the named range B5:C9. Note: the lookup table must be sorted in ascending order, and VLOOKUP must be set to approximate match mode. In approximate match mode VLOOKUP assumes the table is sorted by the first column. With a score provided as a lookup value, VLOOKUP will scan the first column of the table. If it finds an exact match, it will return the grade at that row....
The most satisfying and powerful thing you can do with Excel is to model a real-world problem in a way that helps you make a better decision. For a model to be effective, it needs to expose inputs in a way that makes sense, and generate results that are easy to interpret. In this article, we’ll use the seldom-seen Data Table to build a simple model that helps you visualize the profitability of a Google Adwords campaign....
How is the course delivered? This course is delivered as videos streamed directly to your computer, 24 x 7. Each video is short – typically 3 minutes or less – and all videos come with a practice worksheet that you can download directly below the video. The course is is self-paced, so can move through it as fast or slow as you like. Will the course work for both Windows and Mac?...
Let’s take a look. The easiest way to apply conditional formatting is to apply rules directly to the cells you want to format. For example, if I want to highlight the average test scores in column H that are below 80, I can just select the cells and apply a Conditional Formatting rule with that logic. But what if you want to highlight the names in column B when the average is below 80 in column H....
Background Sometimes Excel ends up with text in a cell, when you really want a number. There are many reasons this might happen, and many ways to fix. This article describes a formula-based approach convert text values to numbers. The VALUE function will try to “coerce” a number stored as text to a true number. In simple cases, it will just work and you’ll get a numeric result. If it doesn’t work, you’ll get a #VALUE error....
This formula returns a number that Excel recognizes as 2:00 AM. We add the result to the starting time: To make sure we have a true time value, we need to ensure that we have only a decimal value. In other words, if we add 12 hours (.5) to 6 PM (.75) we’ll get 1.25, but we really only want .25. To make sure we get just the decimal value, we use the MOD function with a divisor of 1, as a clever way to keep the formula simple....
The result in D5 is 2, since there are two numbers in B5. Create the array The first step is to split the text string into an array of characters. This is done with MID, LEN, and SEQUENCE like this: In a nutshell, the LEN function returns the length of the text in B5, which is 9, to the SEQUENCE function as the rows argument. SEQUENCE then generates an a numeric array like {1;2;3;4;5;6;7;8;9}, which is returned to the MID function as the start_num argument: The MID function then extracts each of the 9 characters and returns an array like {“1”;“8”;" “;“a”;“p”;“p”;“l”;“e”;“s”}....
where data is the named range C5:I14. Note this is an array formula and must be entered with control shift enter. where data is the named range C5:I14. This generates a TRUE / FALSE result for every value in data, and the double negative coerces the TRUE FALSE values to 1 and 0 to yield an array like this: Like the original data, this array is 10 rows by 7 columns (10 x 7) and goes into the MMULT function as array1....
In the example, the active cell contains this formula: Because we are using SUMPRODUCT, the result of all this calculation is a list of items (an array), where there is one item per cell in the range, and each item a number based on the calculation described above. In other words, we have a list of character counts, with one character count per cell. SUMPRODUCT then sums the numbers in this list and returns a total for all cells in the range....
