Purpose Return value Syntax =WRAPCOLS(vector,wrap_count,[pad_with]) vector - The array or range to wrap. wrap_count - Max values in each column. pad_with - [optional] Value to use for unfilled places. Using the WRAPCOLS function The WRAPCOLS function converts a one-dimensional array into a two-dimensional array by wrapping values into separate columns. The length of each column is given as the wrap_count argument: when the count is reached, WRAPCOLS starts a new column....

Purpose Return value Syntax =YIELDMAT(sd,md,id,rate,pr,[basis]) sd - Settlement date of the security. md - Maturity date of the security. id - Issue date of the security. rate - Interest rate of security. pr - Price per $100 face value. basis - [optional] Coupon payments per year (annual = 1, semiannual = 2; quarterly = 4). Using the YIELDMAT function The YIELDMAT function returns the annual yield of a security that pays interest at maturity....

About This Shortcut This shortcut aligns text in selected cells to the center. About This Shortcut This shortcut aligns text in selected cells to the left.

Purpose Return value Syntax =ATANH(number) number - The number to get the inverse hyperbolic tangent of. Using the ATANH function The Excel ATANH function returns the inverse hyperbolic tangent of a number. Given 0.5 as input, the function returns 0.549306144 as output. =ATANH(0.5) // returns 0.549306144 Explanation The hyperbolic arc tangent function is the inverse of the TANH function. =ATANH(TANH(x)) // returns x The plot below shows the output of the ATANH function in Excel....

Purpose Return value Syntax =AVERAGEIF(range,criteria,[average_range]) range - One or more cells, including numbers or names, arrays, or references. criteria - A number, expression, cell reference, or text. average_range - [optional] The cells to average. When omitted, range is used. Using the AVERAGEIF function The AVERAGEIF function calculates the average of the numbers in a range that meet supplied criteria. To apply criteria, the AVERAGEIF function supports logical operators (>,<,<>,=) and wildcards (*,?...

Purpose Return value Syntax =CHOOSE(index_num,value1,[value2],...) index_num - The value to choose. A number between 1 and 254. value1 - The first value from which to choose. value2 - [optional] The second value from which to choose. Using the CHOOSE function The CHOOSE function returns a value from a list using a given position or index. The values provided to CHOOSE can be hard-coded constants or cell references. The first argument for the CHOOSE function is index_num ....

Explanation This formula uses boolean logic to output a conditional message. If the value in column C is less than 100, the formula returns “low”. If not, the formula returns an empty string (""). Boolean logic is a technique of handling TRUE and FALSE values like 1 and 0. In cell C5, the formula is evaluated like this: =REPT("low",C5<100) =REPT("low",TRUE) =REPT("low",1) ="low" In other words, if C5 < 100, output “low” 1 time....

Explanation In the Excel time system, one 24-hour day is equal to 1. This means times and hours are fractional values of 1, as shown in the table below: Hours Time Fraction Value 1 1:00 AM 1/24 0.04167 3 3:00 AM 3/24 0.125 6 6:00 AM 6/24 0.25 4 4:00 AM 4/24 0.167 8 8:00 AM 8/24 0.333 12 12:00 PM 12/24 0.5 18 6:00 PM 18/24 0.75 21 9:00 PM 21/24 0....

Explanation This formula uses two expressions in a single array inside the SUMPRODUCT function. The first expression tests every holiday date to see if it’s greater than or equal to the start date in F5: (B4:B12>=F5) This returns an array of TRUE/FALSE values like this: {FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE;TRUE} The second expression tests every holiday date to see if it’s less than or equal to the end date in F6: (B4:B12<=F6) which returns an array of TRUE/FALSE values like this:...

Explanation In this example, the goal is to use the COUNTIFS function to count data with “OR logic”. The challenge is the COUNTIFS function applies AND logic by default. COUNTIFS function The COUNTIFS function returns the count of cells that meet one or more criteria, and supports logical operators (>,<,<>,=) and wildcards (*,?) for partial matching. Conditions are supplied to COUNTIFS in the form of range/criteria pairs — each pair contains one range and the associated criteria for that range:...

Explanation The cube root of a number can be calculated manually with the exponentiation operator (^) or with the POWER function . Manually with ^ The cube root of a number can be calculated manually by raising a number to the (1/3) using the exponentiation operator (^). In the example shown, the formula in C5 is: =B5^(1/3) Be sure to enclose 1/3 in parentheses to control the order of operations ....

Purpose Return value Syntax =DEC2HEX(number,[places]) number - The decimal number you want to convert to hexadecimal. places - [optional] Pads the resulting number with zeros up to the specified number of digits. If omitted returns the least number of characters required to represent the number. Using the DEC2HEX function The input must be a valid decimal number within the range [ -2^39, 2^39 - 1 ]. Negative Values Excel internally represents Hexadecimal numbers in binary using 40 bits....

Explanation Note: FILTER is a new dynamic array function in Excel 365 . In other versions of Excel, there are alternatives , but they are more complex. There are ten columns of data in the range C4:L6. The goal is to filter this horizontal data and extract only columns (records) where the group is “fox”. For convenience and readability, the worksheet contains three named ranges : data (C4:L6) and group (C5:L5), and age (C6:L6)....

Explanation In this example, the goal is to filter data based on multiple criteria with the FILTER function. Specifically, we want to select data where (1) the group = “A” and (2) the Score is greater than 80. At first glance, it’s not obvious how to do this with the FILTER function. Unlike older functions like COUNTIFS or SUMIFS , which provide multiple arguments for entering multiple conditions, the FILTER function only provides a single argument called “include” to filter data....

Purpose Return value Syntax =GAMMA.INV(probability,alpha,beta) probability - The probability associated with the gamma distribution (must be between 0 and 1). alpha - The shape parameter of the distribution. beta - The scale parameter of the distribution. Using the GAMMA.INV function GAMMA.INV is used to find the value at which the cumulative gamma distribution reaches a specified probability. In other words, it answers the question: “For a given probability, what is the corresponding value of x in the gamma distribution?...

Explanation There are certain functions in Excel that return a cell reference as a result rather than a value. Two of these functions are XLOOKUP and INDEX . The presence of the cell reference in the result is not obvious, because Excel immediately resolves the reference to the value in that cell. You can check the reference returned by XLOOKUP or INDEX with the CELL function . This can be useful when debugging a lookup formula to confirm the result being returned....

Explanation The general goal is to search through a cell for one of several specified values and return the first match found if one exists. The worksheet includes a list of colors in the range E5:E11 (which is named list ) and a series of short sentences in the range B5:B16. The task is to add a formula in column C that will search through each sentence in B5:B16 and extract the first color in E5:E11 that is found in each sentence....

Explanation In this example, the goal is to get the last day of the month based on any valid date. This problem can be solved most easily with the EOMONTH function. However, it can also be solved with the DATE function as explained below. The EOMONTH function The EOMONTH function returns the last day of the month, a given number of months in the past or future. For example, with a start date of January 15, 2024, EOMONTH will return the following results with months set to -1, 0, and 1:...

Explanation This formula is an interesting example of a “brute force” approach that takes advantage of the fact that TRIM will remove any number of leading spaces. Working from the inside out, we use the SUBSTITUTE function to find all spaces in the text, and replace each space with 100 spaces: SUBSTITUTE(B6," ",REPT(" ",100)) So, for example, with the text string “one two three” the result is going to look like this:...

Explanation If you need to group times into buckets (i.e. group by 6 hours, group by 3 hours, etc.) you can do so with a rounding function called FLOOR. In the example shown, we have a number of transactions, each with a timestamp. Let’s say you want to group these transactions into buckets of 3 hours like this: 12:00 AM-3:00 AM 3:00 AM-6:00 AM 6:00 AM-9:00 AM 9:00 AM-12:00 PM...