Excel’s DURATION function returns the Macauley duration for an assumed par value of $100. The Macaulay duration is the weighted average term to maturity of the cash flows from a security. The weight of each cash flow is determined by dividing the present value of the cash flow by the price. Excel also provides the MDURATION function for calculating modified duration. Example In the example shown, we want to calculate the duration of a bond with an annual coupon rate of 5% and semi-annual payments....

The FLOOR function takes two arguments, number and significance. Number is the numeric value to round down. The significance argument is the multiple to which number should be rounded. In most cases, significance is provided as a numeric value, but FLOOR can also understand time entered as text like “0:15”. See the example below. FLOOR works like the MROUND function, but unlike MROUND, which rounds to the nearest multiple, FLOOR always rounds down....

The FREQUENCY function always returns an array with one more item than bins in the bins_array. This is by design, to catch any values greater than the largest value in the bins_array. The general pattern for FREQUENCY is: where data_array and bins_array are typically ranges on the worksheet. Instructions To create a frequency distribution using FREQUENCY: In Excel 365, it is not necessary to enter FREQUENCY as an array formula....

When the function screen tip is visible, you can use it to precisely navigate the function arguments in a formula. The window is context-sensitive and its contents depend on the function the cursor resides in. As you move the cursor through a formula, the currently active argument is bolded. If the cursor is moved to a different function in the same formula, the arguments for that function are displayed. If you click directly on the argument name, the text that makes up that argument will be selected....

Examples If cell A1 contains the formula =2+2, the ISFORMULA function returns TRUE: If cell A1 contains the text “apple”, the ISFORMULA function returns FALSE: Count formulas To count cells in a range that contain formulas, you can use the SUMPRODUCT function like this: The double negative coerces the TRUE and FALSE results from ISFORMULA into 1s and 0s and SUMPRODUCT returns the sum. Notes You can temporarily display all formulas in a worksheet with a keyboard shortcut....

The MINIFS function takes three required arguments: min_range, range1, and criteria1. With these three arguments, MINIFS returns the minimum number in min_range where corresponding cells in range1 meet the condition set by criteria1. Additional conditions are applied using range/criteria pairs. The second condition is defined by range2 and criteria2, the third condition is range3 and criteria3, and so on. MINIFS can handle up to 126 range/criteria pairs. When using MINIFS, keep the following in mind:...

Examples With a single cell reference, ROW returns the associated row number: When a reference is not provided, ROW returns the row number of the cell the formula resides in. For example, if the following formula is entered in cell D6, the result is 6: When ROW is given a range, it returns the row numbers for that range: In Excel 365, which supports dynamic array formulas, the result is an array {4,5,6} that spills vertically into three cells, starting with the cell that contains the formula....

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.

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.

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.

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.

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.

Note: you can also use Control + mouse scroll wheel to zoom in and out on both Windows and Mac. On Windows, the shortcut appears to be new with Excel 2016, and decreases the zoom level shown in the lower right of the worksheet by 15% each time Control - is used. Use Ctrl Alt + to zoom out by 15%. On the Mac, there is no native keyboard shortcut to zoom in Excel, as far as we know....

Because TODAY always returns the current date, the formula will continue to calculate the correct age in the future. In the example shown, the goal is to calculate age in years. The formula in E5 is: The first two arguments for DATEDIF are start_date and end_date. The start date comes from cell D5 (May 15, 2001) in the example. The end date is generated with the TODAY function. TODAY always returns the current date in Excel....

The result is the number 365, since there are 365 days between Jan 1, 1999 and Jan 1, 2000. Note: To see the result as a number and not a date, format the result with the General number format. Working with today To count the number of days between an earlier date and today, you can use the TODAY function: To calculate the number of days between a later date and today, use: Note that TODAY will recalculate on an ongoing basis....

In the example the formula in D7 is: Note: this only works for text values, not numbers. 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.

The result is the monthly close price for each symbol listed in column B over the past 6 months. Note that STOCKHISTORY returns an array of values that spill onto the worksheet into multiple cells. The STOCKHISTORY is only available in Excel 365. Basic example STOCKHISTORY retrieves historical stock price information for a given symbol and date range. STOCKHISTORY takes up to 11 arguments, most of which are optional. The syntax for the first 5 arguments looks like this: The interval argument specifies the time period to use between prices....

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 For example, a time of 2:30 AM, needs to go into the 12:00 AM - 3:00 AM bucket. A time of 8:45 AM needs to go into the 6:00 AM-9:00 AM bucket, and so on....

In this video, we’ll show you how to add a message at the top of a filtered list that displays this information. Here we have a list of properties. If we enable “filtering,” and filter the list, Excel will display the current and total record count in the status bar below. However, if we click outside the filtered list, and back again, this information is no longer displayed. Let’s add our own message at the top of the list that stays visible....

The New menu on the File tab The keyboard shortcut Control-N The Quick Access toolbar Let’s take a look. When you first start Excel, you’ll see you have a new blank document ready to use. If you want to create your own new document, you have several options. First, you can create a new workbook by going to the File tab, and selecting New. You can then choose Blank Workbook, or choose from many available templates....