Posts

Compatibility functions can still be used in later versions of Excel to ensure backward compatibility. However, Microsoft recommends that users deploy the newer functions (compatibility function replacements) when backward compatibility isn’t required. Not all new functions with similar names indicate compatibility functions. For example, the NETWORKDAYS function, which calculates workdays between dates, is not listed as a compatibility function even though NETWORKDAYS.INTL is a newer function with the same purpose....

In the screen above, the formulas used for concatenation are: Note that in the last example, we are using the TEXT function to format the date in B8 as a date in “mmm d” format. Concatenation with functions Excel contains three functions you can also use for concatenation: the CONCATENATE function, the CONCAT function, and the TEXTJOIN function. CONCAT and TEXTJOIN are new functions available in Office 365 and Excel 2019....

The settlement date is the date the investor takes possession of a security. The maturity date is the date when the investment ends and the principle plus accrued interest is returned to the investor. The frequency is the number of interest payments per year. Basis specifies the method used to count days (see below). In the example shown, the formula in F6 is: COUPDAYBS returns an integer, so use a number format and not a date format to display properly....

Syntax The generic syntax for the IF function looks like this: The first argument, logical_test, is an expression that should return either TRUE or FALSE. The second argument, value_if_true, is the value to return when logical_test is TRUE. The last argument, value_if_false, is the value to return when logical_test is FALSE. Both value_if_true and value_if_false are optional, but at least one of them must be provided. For example, if cell A1 contains 80, then:...

When to use Excel IF Function IF function in Excel is best suited for situations where you check whether a condition is TRUE or FALSE. If it’s TRUE, the function returns a specified value/reference, and if not then it returns another specified value/reference. What it Returns It returns whatever value you specify for the TRUE or FALSE condition. Syntax =IF(logical_test, [value_if_true], [value_if_false]) Input Arguments logical_test – this is the condition that you want to test....

Examples The ISODD function returns TRUE or FALSE: If cell A1 contains 11, the formula below returns TRUE: Only the integer portion of value is tested. If value is a decimal number, the decimal portion is truncated: Notes If value is not numeric, ISODD will return the #VALUE error. Only the integer portion of value is tested; decimal values are truncated. Use the ISEVEN function to test for even numbers....

Dynamic array formulas are a huge change to the Excel formula engine because they let you easily work with multiple values at the same time in a formula. In practical terms, this means array formulas “just work”. In Legacy Excel, most array formulas still need to be entered with control + shift + enter. Example The LEN function returns the length of a text string as a number: To get a total of all characters in a range, you can wrap LEN inside the SUM function like this: This is a simple array formula, but it must be entered with control + shift + enter in Legacy Excel: Note: Excel will add the curly brackets automatically when the formula is entered with control + shift + enter....

The N function is provided for compatibility with other spreadsheet programs. In most cases, using the N function is unnecessary, because Excel automatically converts values when needed. However, the N function is a simple way to convert TRUE and FALSE to their numeric equivalents, 1 and 0, as mentioned below. Examples The N function converts text values to zero: Numeric values and errors are not affected: The N function converts TRUE to 1 and FALSE to zero: There are several ways to perform this conversion in Excel, which is useful in more advanced formulas....

To use PERCENTILE.INC, provide a range of values and a number between 0 and 1 for the “k” argument, which represents percent. For example: You can also specify k as a percent using the % character: PERCENTILE.INC returns a value greater than or equal to the specified percentile. In the example shown, the formula in G5 is: where “scores” is the named range C5:C14. PERCENTILE.INC vs. PERCENTILE.EXC PERCENTILE.INC includes the full range of 0 to 1 as valid k values, compared to PERCENTILE....

Using Degrees To supply an angle to SEC in degrees, multiply the angle by PI()/180 or use the RADIANS function to convert to radians. For example, to get the COT of 60 degrees, you can use either formula below: Explanation The graph of SEC, shown above, visualizes the output of the function for angles from 0 to a full rotation. The function has two vertical asymptotes at π/2 and 3π/2 respectively where the output of the function diverges to infinity....

Dave Bruns Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.

Using Degrees To supply an angle to SIN in degrees, multiply the angle by PI()/180 or use the RADIANS function to convert to radians. For example, to get the SIN of 30 degrees, you can use either formula below: Explanation The graph of sine, shown above, visualizes the output of the function for all angles from 0 to a full rotation. The function is periodic, so after a full rotation, the output of the function repeats....

Examples In the worksheet shown, the formula in H5, copied down, is: Notice that SUMSQ automatically ignores empty cells and text values. Notes Arguments can be a mix of constants, names, arrays, or references that contain numbers. Empty cells, logical values, and text values are ignored when they appear in arrays or references. The logical values TRUE and FALSE are evaluated as 1 and 0 respectively, but only when they are hardcoded as arguments....

TEXTSPLIT takes five arguments, only two of which are required. The first argument, text, is the text string to split. The second argument, col_delimiter is the delimiter to use for splitting text into separate columns. The third argument, row_delimiter, is the delimiter to use for splitting text into separate rows. Either col_delimiter or row_delimiter must be supplied along with text. The fourth argument, ignore_empty, controls TEXTSPLIT’s behavior with empty values (i....

The TIMEVALUE function takes just one argument, called time_text. If time_text is a cell address, the value in the cell must be text. If time_text is entered directly into the formula it must be enclosed in double quotes (""). Time_text should be supplied in a text format that Excel can recognize, for example, “6:45 PM” or “18:45”. TIMEVALUE ignores dates if present in a text string. The TIMEVALUE function creates a time in serial number format from a date and/or time in an Excel text format....

So, to perform this calculation in Excel, we need to divide the amount of the expense in column C by the percentage that expense represents in column D like this: In the example, the active cell E6, copied down, is: Excel simply divides the value in cell C6 by the percentage value in cell D6: The result is the number 1945 , which is the total of all expenses in this case....

where data (C5:C16) and bins (F5:F8) are named ranges. This formula is entered as a multi-cell array formula in the range G5:G8. The FREQUENCY function returns a frequency distribution, which is a summary table that shows the count of each value in a range by “bin”. FREQUENCY is a bit tricky to use, because must be entered as an array formula. On the other hand, once you set up your bins correctly, FREQUENCY will give you all counts at once!...

Slicers provide a powerful way to filter data interactively, and they are a key building block of many dashboards. To filter a chart with a slicer, you can add a slicer to either the chart or pivot table. Once you do that, the slicer will control both the pivot table and the pivot chart. To add a slicer, select either the pivot table or the pivot chart. You’ll find the Insert Slicer button on the Analyze tab for both....

Named ranges allow you to quickly name a cell or a range of cells so that instead of using the reference, you can use those names in the formulas. And when you get hooked to using named ranged, there is a possibility that you may end up creating a lot of named ranges and may want to delete some or all of them. In this short tutorial, I will show you two simple ways to delete named ranges in Excel....

Here we have a list of salespeople with monthly sales figures. What we want to do is add a formula in Q6 that looks up and retrieves a sales number based on the name and month above. To do this, we’ll use the INDEX and MATCH functions. First, I’ll name some ranges to make the formulas easier to read. I’ll name the entire table “data,” and then use “names” for the list of salespeople....