Explanation In this example, the goal is to sum the numbers in column C when the text in column B contains specific pairs of colors. For example, the formula should sum a number when the text contains both “red” and “blue”. Order is not important; the two colors can appear anywhere in the cell. However, both colors must appear in the same cell. This problem can be solved with the SUMIFS function , which is designed to sum numbers based on multiple criteria....
Explanation In this example, one goal is to sum the numbers that appear in the range B5:B16. A second more challenging goal is to create the table of results seen in E7:F12. For convenience, data is the named range B5:B16. Total sum To sum all the numbers that appear in B5:B16, ignoring text, the formula in E5 is: =SUM(--TEXTAFTER(data," ")) Working from the inside out, the TEXTAFTER function is used to extract the numbers like this:...
Explanation In the example shown, the table in B4:C13 contains quantity-based discounts. As the quantity increases, the discount also increases. The table in E4:F10 shows the discount returned by XLOOKUP for several random quantities. XLOOKUP is configured to use the quantity in column E to find the appropriate discount. The formula in F5, copied down, is: =XLOOKUP(E5,qty,disc,-1) The lookup_value comes from cell E5 The lookup_array is the named range qty (B5:B13) The return_array is the named range disc (C5:C13) The not_found argument is not provided The match_mode is set to -1 (exact match or next smaller) The search_mode is not provided and defaults to 1 (first to last) Note: Be aware if you supply an empty string ("") for not_found....
Explanation The WORKDAY.INTL function is based on the WORKDAY function , which is designed to add work days to a date. WORKDAY automatically excludes Saturday and Sunday, and optionally can exclude a list of custom holidays. The WORKDAY.INTL does the same thing, but makes it possible to exclude any days of the week, in addition to holidays. To exclude specific days of the week you can either use a pre-configured code (see this page for a full list of presets) or provide your own “pattern code”....
About This Shortcut On Windows, this shortcut only works within the Format Cells dialog box, on the Borders tab. If you use this shortcut in Format Cells, you’ll see a top border added or removed from the border preview area. On the Mac, this shortcut works directly on the worksheet, and adds a top border to each cell in the selection. About This Shortcut On Windows, this shortcut only works within the Format Cells dialog box, on the Borders tab....
About This Shortcut This shortcut aligns text in selected cells to the left. About This Shortcut This shortcut aligns text in selected cells to the right. Note: As of February 2022, the Mac shortcut for Align Right appears to be overridden by the Fill from left shortcut.
About This Shortcut This shortcut will apply the Percentage format with no decimal places. Excel offers many types of number formatting . About This Shortcut This shortcut will apply the Scientific number format with two decimal places. Excel offers many types of number formatting .
Purpose Return value Syntax =ARRAYTOTEXT(array,[format]) array - The array or range to convert to text. format - [optional] Output format. 0 = concise (default), and 1 = strict. Using the ARRAYTOTEXT function The ARRAYTOTEXT function converts an array or range into a text string in a specific format that contains all values. Values are separated by commas (,) or semicolons (;), depending on the format requested and the structure of the array ....
Explanation In this example, the goal is to average the last 3 numeric values in a set of data. The best solution depends on the version of Excel you have available. In the current version of Excel, this can be nicely solved with a formula based on the AVERAGE function , the FILTER function , and the TAKE function . In older versions of Excel, you can use an alternative formula based on the LOOKUP function , the LARGE function , and the ROW function ....
Transcript In this video, we’ll set up the XLOOKUP function to perform an approximate match. In this worksheet, the table in B5:C9 contains quantity-based discounts. As the quantity increases, the discount also increases. Let’s set up the XLOOKUP function to calculate the correct discount for each quantity shown in E5:E11. Now, to make entering the formula a bit easier, I’m going to start by creating two named ranges . I’ll name B5:B9 quantity and I’ll name C5:C9 discount ....
Explanation In this example, the goal is to calculate a retirement date at age 60, based on a given birthdate. The simplest way to do this is with the EDATE function. The EDATE function will return a date n months in the future or past when given a date and the number of months to traverse. In this case, we want a date 60 years from the birthdate in column C, so the formula in D5 is:...
Transcript In this video, we’ll look at how to extend a conditional formatting formula so that so that it checks more than just one condition. Let’s take a look. Here we have the example we looked at previously. We have a single conditional formatting rule that uses a formula to highlight cells that have a value greater than the input cell. Let’s modify this rule to use two conditions so that we can format cells that are between two numbers, using a lower and upper limit....
Explanation The Unix time stamp tracks time as a running count of seconds. The count begins at the “Unix Epoch” on January 1st, 1970, so a Unix time stamp is simply the total seconds between any given date and the Unix Epoch. Since a day contains 86400 seconds (24 hours x 60 minutes x 60 seconds), conversion to Excel time can be done by subtracting the date value for the Unix Epoch and multiplying days by 86400....
Formulas are the key to getting work done in Excel. In this accelerated video course, you’ll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You’ll also learn powerful skills to troubleshoot, trace errors, and fix problems. This is the formula training you should have had to begin with....
Explanation Traditionally, counting unique items with an Excel formula has been a tricky problem, because there hasn’t been a dedicated unique function. However, that changed when dynamic arrays were added to Excel 365 , along with several new functions, including UNIQUE. Note: In older versions of Excel, you can count unique items with the COUNTIF function, or the FREQUENCY function, as explained below. In the example shown, each row in the table represents a stock trade....
Explanation Data validation rules are triggered when a user adds or changes a cell value. In this case, we need a formula that returns FALSE as long as entries in C6:C9 sum to a total equal to or below 1000. We use the SUM function to sum a fixed range and then simply compare the result to 1000 using less than or equal to. Note the range C6:C9 is entered as an absolute reference to prevent the reference from changing automatically for each cell that data validation is applied to....
If you’re used to working with Excel on Windows, one of the most confusing aspects of using Excel on a Mac is shortcuts. Even basic shortcuts you’ve been using for years in Windows may not work as you expect. After a few problems, you might wind up thinking that Mac shortcuts are “totally different” or somehow “broken”. In reality, Excel shortcuts on the Mac are quite capable, you just have to understand and adjust to certain differences....
Excel supports both full column, and full row references. A full column reference is a reference that refers to an entire column . For example, to sum all of the values in column A, you can use the SUM function like this: =SUM(A:A) Notice a full column reference is entered like other ranges, with a colon (:) separating the starting point from the ending point. Since there are no row numbers in a full column reference, the literal translation the range A:A is “every cell in column A”....
Explanation Excel contains a number of rounding functions. Two of these functions, the INT function and the TRUNC function will return the integer portion of a number that contains a decimal value. The INT function behaves a bit differently with negative values, so in this example we are using the TRUNC function. The TRUNC function simply truncates (i.e. removes) decimal values if they exist – it doesn’t do any rounding....
Explanation Inside the AND function there are two logical criteria. The first is straightforward, and ensures that only cells that match the color in E5 are highlighted: $B3=$E$5 The second test is more complex: $C3<=SMALL(IF(color=$E$5,amount),3) Here, we filter amounts to make sure that only values associated with the color in E5 (blue) are retained. The filtering is done with the IF function like this: IF(color=$E$5,amount) The resulting array looks like this:...
