Purpose Return value Syntax =YIELD(sd,md,rate,pr,redemption,frequency,[basis]) sd - Settlement date of the security. md - Maturity date of the security. rate - Annual coupon rate. pr - Security’s price per $100 face value. redemption - Redemption value per $100 face value. frequency - Coupon payments per year (annual = 1, semiannual = 2; quarterly = 4). basis - [optional] Day count basis (see below, default =0). Using the YIELD function The YIELD function returns the yield on a security that pays periodic interest....

Purpose Return value Syntax =ACCRINTM(id,sd,rate,par,[basis]) id - Issue date of the security. sd - Settlement date of the security. rate - Annual coupon rate. par - Par value of security. basis - [optional] Day count basis (see below, default =0). Using the ACCRINTM function In finance, bonds prices are quoted “clean”. The “clean price” of a bond excludes any interest accrued since the issue date, or most recent coupon payment. The “dirty price” of a bond is the price including accrued interest....

Explanation In this example, the goal is to calculate a workday n days in the future based on a 4-day workweek and, optionally, holidays. For convenience, start (B5), days (B8), and holidays (B11:B13) are named ranges . The dates in columns D and E are dynamically generated based on the start date in B5. Conditional formatting is used to shade excluded days in gray and to highlight the final calculated dates in yellow....

The Bar of Pie Chart is a built-in chart type in Excel. Pie charts are meant to express a “part to whole” relationship, where all pieces together represent 100%. Pie charts work best to display data with a small number of categories (2-5). The Bar of Pie Chart provides a way to add additional categories to a pie chart without generating a pie chart too complex to read. When configuring a Bar of Pie chart, Excel provides a setting that moves the smallest n slices of the pie to the bar, where n can be adjusted to suit the data....

About This Shortcut This shortcut will calculate the active worksheet only. About This Shortcut This shortcut will force calculation in all worksheets in all open workbooks even when cells have not been changed. We aren’t aware of a keyboard shortcut to do this on the Mac. If you know of one, please let us know.

Explanation In the example shown, the formula in B11 is: =COUNTIFS(OFFSET(B$5,0,0,ROW()-ROW(B$5)-1,1),"<>") Working from the inside out, the work of setting up a variable range is done by the OFFSET function here: OFFSET(B$5,0,0,ROW()-ROW(B$5)-1,1) // variable range OFFSET has five arguments and is configured like this: reference = B$5, begin at cell B5, row locked rows = 0, offset zero rows from starting cell cols = 0, offset zero columns starting cell height = ROW()-ROW(B$5)-1 = 5 rows high width = 1 column wide To work out the height of the range in rows, we use the ROW function like this:...

About This Shortcut This shortcut will create and insert a chart using data in current range as an embedded object in the current worksheet. Note: the Mac shortcut does not seem to be working on current versions of OS X and Excel 2011. One reason might be that Option + F1 activates Display Preferences. However, this shortcut does not appear under System Preferences > Keyboard > Keyboard Shortcuts so it’s not clear how to disable it and also not certain that disabling it would solve the problem....

About This Shortcut This shortcut will create a new blank workbook. About This Shortcut This shortcut display the Open File dialog box.

The Doughnut Chart is a built-in chart type in Excel. Doughnut charts are meant to express a “part-to-whole” relationship, where all pieces together represent 100%. Doughnut charts work best to display data with a small number of categories (2-5). For example, you could use a doughnut chart to plot survey questions with a small number of answers, data split by gender, Windows vs. Mac users, or other data where categories are limited....

Explanation The dropdown is set up with a simple data validation rule based on a “list”: Red,Blue,Green,All The named ranges “color” (C5:C15) and “qty” (D5:D15) are for convenience only. The formula in G5 performs a conditional sum based on the current dropdown selection in F5. The outermost function is an IF statement, which checks if the selection is “all”: =IF(F5="all",SUM(qty) If so, the formula returns the sum of quantity column as a final result....

Purpose Return value Syntax =DVARP(database,field,criteria) database - Database range including headers. field - Field name or index to count. criteria - Criteria range including headers. Using the DVARP function The Excel DVARP function calculates the variance of data that represents an entire population, extracted from records matching the given criteria, where values come from a given field. The database argument is a range of cells that includes field headers, field is the name or index of the field to get a max value from, and criteria is a range of cells with headers that match those in database....

Explanation In this example, the goal is to convert a space-separated sequence of Unicode code points into a readable text string. We can solve this using several Excel functions working together to split, convert, and reconstruct the text. This is the reverse operation of this formula that converts text into Unicode sequence. The formula explained The formula processes the Unicode sequence through several steps: =TEXTJOIN("",,UNICHAR(HEX2DEC(TEXTSPLIT("0061 0070 0070 006C 0065"," ")))) Working from the inside out:...

Explanation The goal is to extract records with dates that are greater than or equal to a start date in F5 and less than or equal to an end date in G5. You might think we can use the AND function inside FILTER to solve this problem. However, because AND returns just a single value, this won’t work. Instead, we use something called “Boolean logic” to validate the dates. Background study Use the links below to learn the concepts explained in this article....

Explanation The goal in this example is to display the last 3 valid entries from the table shown, where “valid” is defined as a temperature of less than 75 in the “Temp” column. At a high level, the FILTER function is used to filter entries based on a logical test, and the INDEX function is used to extract the last 3 entries from the filtered list. Working from the inside out, we use the SEQUENCE function to construct a row number value for the INDEX function like this:...

Purpose Return value Syntax =FV(rate,nper,pmt,[pv],[type]) rate - The interest rate per period. nper - The total number of payment periods. pmt - The payment made each period. Must be entered as a negative number. pv - [optional] The present value of future payments. If omitted, assumed to be zero. Must be entered as a negative number. type - [optional] When payments are due. 0 = end of period, 1 = beginning of period....

Purpose Return value Syntax =GAMMALN(x) x - A positive real number for which you want to calculate the natural logarithm of the gamma function. Using the GAMMALN function The GAMMALN function returns the natural logarithm of the gamma function, ln(Γ(n)), for a given number. This is especially useful in statistical calculations, such as those involving probability distributions, where the gamma function appears in the denominator and direct computation could result in very large or very small numbers....

Explanation In this example, the goal is to apply conditional formatting to cells that begin with specific text, which is entered in cell G2. The highlighting is done automatically with a conditional formatting rule applied to the range B4:G12. The rule type is “Use a formula to determine which cells to format”. The formula looks like this: =SEARCH($G$2,B4)=1 G2 contains the text to search for, and B4 is the cell being tested....

Explanation Note: later versions of Excel include a native histogram chart , which is easy to create , but not as flexible to format. The example on this page shows one way to create your own histogram data with the FREQUENCY function and use a regular column chart to plot the results. Because FREQUENCY is a formula, the results and chart will dynamically update if data changes. The FREQUENCY function returns a frequency distribution, which is a summary table that shows the count of each value in a range by “bin”....

Transcript Although you can use Excel’s AutoFill feature to fill in a series of dates and times, you can also do the same thing with formulas. The advantage of using a formula is that you can easily change the starting value and generate a new series. Let’s take a look. Often you’ll need to generate a series of dates separated by a certain interval of days, months, or years. You can easily do this with Excel’s Date functions....

Transcript In this lesson, we’ll look at how to insert and delete worksheets in an Excel workbook. Let’s take a look. The easiest way to add a new worksheet to a workbook is to click the Insert Worksheet tab that sits to the right of the last tab in the workbook. When you click this button, Excel will immediately add a new worksheet to the workbook to the right of the last worksheet....