Posts

where data is the named range D5:D16. With n hardcoded into the formula as 3, FILTER function returns every 3rd row in the data. Working from the inside out, the first step is to generate a set of row numbers. This is done with the SEQUENCE function like this: The ROW function returns the count of rows in the named range data. Using the count of rows, SEQUENCE returns an array of 12 numbers in sequence: This array is returned directly to the MOD function as the number argument, with the number 3 hardcoded as the divisor....

where data (B5:D16) and score (D5:D16) are named ranges. The LARGE function is a simple way to get the nth largest value in a range. Simply provide a range for the first argument (array), and a value for n as the second argument (k): In this example, the logic for FILTER is constructed with this snippet: which returns TRUE when a score is greater than or equal to the 3rd highest score....

where data (B5:D16), group (C5:C16) and score (D5:D16) are named ranges. The FILTER function applies criteria with the include argument. In this example, criteria are constructed with boolean logic like this: The left side of the expression targets scores greater than or equal to the 3rd highest score in group B: The IF function is used to make sure LARGE is only working with group B scores. Because we have 12 scores total, IF returns an array with 12 results like this: Notice the only scores that survive the operation are from Group B....

This is an example of embedding text into a custom number format, and this text must be surrounded by an extra pair of double quotes. Without the extra double quotes, the custom text format looks like this: The value for days is calculated with the INT function, which simply returns the integer portion of the end date minus the start date: Note: Although you can use “d” in a custom number format for days, the value will reset to zero when days is greater than 31....

In the example, the active cell contains this formula: The LEFT function extracts characters from the full name starting on the left and continuing up to the number of characters determined in the previous step above. In the example, 5 is the total number of characters, so the result is “Susan”. Note: this formula does not account for titles (Ms., Mr., etc) in the full name. If titles exist, they should be removed first....

In the example formula, B6 is the date 1/16/2015, and the formula in C6 is: The number 7 (the number argument in the MOD function) represents the day of week (dow) that you want. To solve this formula, Excel first subtracts the dow (7 in this case) from the date, then feeds the result into the MOD function as the number. MOD returns the remainder of dividing that number by 7, which is then subtracted from date....

The results in column C are fractional values with the percentage number format applied. *By default, the YEARFRAC function uses a 30/360 day convention, assuming a year contains twelve 30-day months = 360 days. This is controlled by an optional argument called “basis”. The core of the formula is the YEARFRAC function, which returns the fractional years between two dates as a decimal value. The formula in C5, copied down, is: YEARFRAC takes two dates: a start date and an end date: We calculate the start date with the DATE function and YEAR function like this: The YEAR function extracts the year from the date in B5: The number is returned directly to the DATE function with the number “1” hard-coded for both month and day arguments: The end date is the date given in column B....

The result is the name of the workbook only, “Test workbook name.xlsx” in the example shown. Note: TEXTAFTER and TEXTBFORE are only available in the latest version of Excel. In Legacy Excel, you can use a more complicated formula based on the MID and FIND functions. Read below for details. Get workbook path The first step in this problem is to get the workbook path, which includes the workbook and worksheet name....

This is a surprisingly tricky problem in Excel. The “obvious” answer is to use the FIND function to “look” for the text, like this: Then, if you want a TRUE/FALSE result, add the IF function: This works great if “apple” is found – FIND returns a number to indicate the position, and IF calls it good and returns TRUE. But FIND has an annoying quirk – if it doesn’t find “apple”, it returns the #VALUE error....

The result is TRUE for any date in the last complete 2 week period, where weeks begin on Monday. The TODAY function will return the current date on an ongoing basis, so this formula can be used create reports to show a rolling 4 weeks, rolling 6 weeks, etc. Excel dates are serial numbers, so they can be manipulated with simple math operations. The TODAY function always returns the current date....

where name (B5:B16) and drink (D5:D16) are named ranges. Note these conditions apply to all the data available for each person. To make the formulas easier to set up, we have two named ranges to work with: name (B5:B16) and drink (D5:D16). Also, note that we are using names as a unique identifier for convenience only. Normally, data like this will include some sort of unique ID for each person....

which replaces line breaks in B5 with commas. For a quick demo of CLEAN and TRIM, watch this video. In this case, however, we are removing line breaks and replacing them with commas, so we are using the SUBSTITUTE function instead of CLEAN. SUBSTITUTE can locate matching text anywhere in a cell, and replace it with the text of your choice. SUBSTITUTE can accept up to four arguments, but we are using only the first three like this: The text comes from cell B5....

Which removes a series of 4 invisible characters at the start of each cell in column B. How can you figure out which character(s) need to be removed, when they are invisible? To get the unique code number for the first character in a cell, you can use a formula based on the CODE and LEFT functions: Here, the LEFT function, without the optional second argument, returns the first character on the left....

Make sure you watch the first video if you haven’t already. In the example we have formulas that calculate a company match for an employer-sponsored retirement plan in two tiers. The calculations for both tiers use one or more IF statements, and the second formula is a bit complicated. Let’s look at how we can simplify these formulas. For Tier 1, the company match is capped at 4%. If the deferral is less than or equal to 4%, we can simply use it as is and multiply C5 by B5, but when the deferral is greater than 4%, we simply multiply 4% by B5....

where data is the named range B5:B16. The result is the sum of the largest 3 values (70, 65 55) which is 45. LARGE function The LARGE function is designed to return the nth largest value in a range. For example: Normally, LARGE returns just one value. However, if you supply an array constant (e.g. a constant in the form {1,2,3}) to LARGE as the second argument, k , LARGE will return an array of results instead of a single result....

Here we have a basic column chart. The chart itself - the rectangle that holds everything else - is referred to as the Chart Area. When you select a chart, Excel will wrap the chart in a frame that contains eight handles. These handles can be used to resize the chart. At the top of the chart is the Chart Title, and to the right is the Legend. A column chart like this has two axes: the horizontal axis, which is also referred to as the category axis, and the vertical axis....

You can use REPT to repeat any character(s) you like. In this example, we use the CHAR function to output a character with a code of 110. This character, when formatted with the Wingdings font, will output a solid square. To calculate the “number of times” for REPT, we scale values in column C by dividing each value by 100. This has the effect of outputting one full square per 100 dollars of sales....

where range1 (B5:B8) and range2 (D5:D9) are named ranges. The formula appends the second range to the first range and the result spills into F5:F13. See below for details. Note: While this is a good example of what can be done with dynamic array formulas, Excel now contains two new functions that make this approach unnecessary: the HSTACK function and the VSTACK function. Single column ranges The formula to combine single column ranges is based on INDEX function, the SEQUENCE function, the IF function, and the LET function....

The ribbon How the ribbon is organized, and why tabs on the ribbon mysteriously appear and disappear when you select certain objects. A simple way to add your own buttons to the ribbon, to keep frequently used commands at your fingertips. How to avoid using the ribbon for many commands, and how to quickly hide the ribbon when you just want it gone…this gives you space to see 4 extra rows of data....

Note: The formula on this page counts instances of a word in a range. For example, if a cell contains two instances of a word, it will contribute 2 to the total count. If you just want to count cells that contain a specific word, see this simple formula based on the COUNTIF function. In the example shown, B5:B8 is the range to check, and C2 contains the text (word or substring) to count....