Posts

Purpose Return value Syntax =COUPDAYSNC(settlement,maturity,frequency,[basis]) settlement - Settlement date of the security. maturity - Maturity date of the security. frequency - Number of coupon payments per year (annual = 1, semi-annual = 2, quarterly = 4). basis - [optional] Day count basis (see below, default =0). Using the COUPDAYSNC function Historically, bonds were printed on paper with detachable coupons. The coupons were presented to the bond issuer by the bondholder to collect periodic interest payments....

Explanation Data validation rules are triggered when a user adds or changes a cell value. In this formula, the FIND function is configured to search for the text “XST” in cell C5. If found, FIND will return a numeric position (i.e. 2, 4, 5, etc.) to represent the starting point of the text in the cell. If the text is not found, FIND will return an error. For example, for cell C5, FIND will return 5, since “XST” starts at character 5....

Explanation In this example, the goal is to create a data validation rule that will only accept numbers that are a specific multiple of another number. In the worksheet shown, the multiple is 100. Data validation rules are triggered when a user adds or changes a cell value. When a custom formula returns TRUE, validation passes and the input is accepted. When the formula returns FALSE, validation fails and the input is rejected....

Explanation Data validation rules are triggered when a user adds or changes a cell value. The AND function takes multiple arguments (logical expressions) and returns TRUE only when all arguments return TRUE. In this case, we need two conditions: Logical 1 tests if the input is a number using the ISNUMBER function: ISNUMBER(B5) The ISNUMBER function returns TRUE when a value is numeric and FALSE if not. Logical 2 tests checks that the input doesn’t already exist in the named range “ids”:...

About This Shortcut This shortcut will toggle the formula bar between a collapsed and uncollapsed state. It’s useful when you have a very long formula, or have added line breaks to a formula for readability . About This Shortcut This shortcut displays the function arguments dialog box after a valid function has been typed in the formula bar. On the Mac, it displays the Formula Builder dialog box.

Purpose Return value Syntax =FIELDVALUE(value,field_name) value - The data type with field values. field_name - The field name provided as a text value. Using the FIELDVALUE function The Excel FIELDVALUE function extracts a given field value from a Data Type . The field is specified by name and provided as a text value. Use the FIELDVALUE function to retrieve a field value by name from linked data types like Stocks, Geography, Food, Currency, and more....

Transcript In this video, we’ll set up the FILTER function with two criteria. The FILTER function is designed to extract data from a list or table using supplied criteria. In this worksheet, we have data that contains an order number, amount, name, and state. Our goal is to use the FILTER function to extract orders from Texas with an amount of at least $100. To explain how this logic works, I’m going to test the criteria first in a helper column, then copy it into the FILTER function....

Details The context A couple weeks ago, I had an interesting question from a reader about tracking weight gain or loss in a simple table. The idea is to enter a new weight each day, and calculate the difference from the previous day. When every day has an entry, the formula is straightforward: The difference is calculated with a formula like this, entered in D6, and copied down the table:...

Explanation In this example, the goal is to retrieve the current stock price for the companies listed in Column B. Note these cells in the range B5:B16 have already been converted to the “Stocks” Data Type . Once the Stocks Data Type is available on the worksheet, you can retrieve various information using the simple formulas described below. These formulas follow a “dot” syntax like this: =A1.field With a valid Data Type in A1, Excel will automatically display available fields once the “....

Explanation In Excel, dates are simply serial numbers. In the standard date system for windows, based on the year 1900, where January 1, 1900 is the number 1. Dates are valid through 9999, which is serial number 2,958,465. This means that January 1, 2050 is the serial number 54,789. In the example, the date is March 9, 2016, which is the serial number 42,438. So: = B4-TODAY() = January 1 2050 - April 27, 2014 = 54,789 - 42,438 = 12,351 This means there are 13,033 days before January 1, 2050, when counting from March 9, 2016....

Explanation In this example, the goal is to get the next specified weekday, starting on a given start date. So for example, with a valid start date in column B, we want to be able to ask for the next Monday, the next Tuesday, the next Wednesday, and so on. This article describes two different ways of solving this problem. The first way, which appears in the worksheet shown above, is based on the TEXT function and the MATCH function....

About This Shortcut Move to the next workbook window. This shortcut “cycles” through open Excel workbooks in a “forward” direction. On Windows, you can also use Ctrl + F6 to move to the next workbook and Shift + Ctrl + F6 to move to the previous open workbook. On a Mac, the shortcut for cycling in a forward direction between open windows in an application is Command + Shift + ....

Transcript In this video we’ll look at how to calculate the number of days between dates. To get started, let’s first set up some dates, so we have a visual representation to refer to. In C5, I’ll add a start date. Then, I’ll add and copy a formula below that simply adds “1” to each date above. The result is that we get a list of 14 consecutive dates. Now let’s show the day of the week for each date....

Transcript As you start to work with more formulas in Excel, you’ll find you often want to replace formulas with the values they generate. One common situation is that your formula has calculated a result, and you want to stop Excel from calculating a new result. Let’s take a look. To illustrate converting formulas to values, let’s look at an example that uses the RANDBETWEEN function to assign a list of people randomly to four different groups....

Transcript In this lesson, we’ll look at how to enter numbers as text. The most common reason to enter numbers as text is to maintain formatting in things like part numbers, zip codes, credit card numbers, or telephone numbers. Let’s take a look. First, let’s look at the problem. Here’s a list of numbers that need to be formatted as shown in column C. When we try to enter the values directly, we can see that Excel just strips off the leading zeros....

Transcript In this video, we’ll look at how to use the RIGHT and LEFT functions to extract specific text from data. Let’s take a look. Here we have some customer data. To illustrate how to extract text using the LEFT and RIGHT functions, I’ll use them both to pull out just certain parts of this information. For the first example, I’ll extract the area code from the phone number . This is a perfect application of the LEFT function ....

Transcript One of the more confusing aspects of Pivot Tables is layout options. When you create a pivot table, you have your choice of three layouts. In this video, we’ll look at each layout. Once you have a pivot table, you can change layouts using the Report layout menu, on the Design tab of the pivot table tools ribbon. By default, each new pivot table you create will use the Compact layout....

Purpose Return value Syntax =IFS(test1,value1,[test2, value2],...) test1 - First logical test. value1 - Result when test1 is TRUE. test2, value2 - [optional] Second test/value pair. Using the IFS function The IFs function evaluates multiple expressions and returns a result that corresponds to the first TRUE result. You can use the IFS function when you want a self-contained formula to test multiple conditions at the same time without nesting multiple IF statements....

Purpose Return value Syntax =IMCSC(complex_num) complex_num - The complex number in the form “x+yi”. Using the IMCSC function The Excel IMCSC function returns the cosecant of a complex number. For example, given “4+3i” as input, the function returns “-0.075489833+0.064877471i” as output. =IMCSC("4+3i") // returns -0.075489833+0.064877471i Explanation In math, the complex cosecant function is the reciprocal of the complex sine function . In Excel, the complex cosecant function is equivalent to the following formula....

About This Shortcut This shortcut converts a range to an Excel Table. To use this shortcut, first select the range of data you want to convert. Note: in Mac Excel 365, you can also use Control + T to convert a range to a table. About This Shortcut This shortcut toggles filters on and off for a range of data. It works on tables or filtered lists, and it’s a fast way to clear filters - just turn autofilter off, then on again to reset all filters....