Purpose Return value Syntax =ODDFPRICE(sd,md,id,fd,rate,yld,redem,freq,[basis]) sd - Settlement date of the security. md - Maturity date of the security. id - Issue date of the security. fd - First coupon date. rate - Annual coupon rate of security. yld - Annual required rate of return. redem - Redemption value per $100 face value. freq - Coupon payments per year (annual = 1, semiannual = 2; quarterly = 4). basis - [optional] Day count basis (see below, default =0)....
Explanation This formula concatenates the original value in column B to a string of asterisks (*) assembled with the REPT function so that the final result is always 12 characters: REPT("*",12-LEN(B5)) Inside the REPT function, the text to repeat is provided as a single asterisk ("*"). The number of asterisks needed for each value is determined with the LEN function in this bit of code here: 12-LEN(B5) We start with 12, then subtract the length of the text in column B....
Purpose Return value Syntax =PERCENTILE.EXC(array,k) array - Data values. k - A value between 0 and 1 that represents the k:th percentile. Using the PERCENTILE.EXC function The Excel PERCENTILE.EXC function calculates the “kth percentile” for a set of data. The kth percentile is a value below which k percent of values in the data set fall. A percentile calculated with .4 as k means 40% percent of values are less than or equal to the calculated result, a percentile calculated with k = ....
Explanation The SUBSTITUTE function is full automatic. All you need to do is supply “old text” and “new text”. SUBSTITUTE will replace every instance of the old text with the new text. If you need to perform more than one replacement at the same time, you’ll need to nest multiple SUBSTITUTE functions. See this " clean telephone numbers " formula for an example. If you need to replace a character at a specific location, see the REPLACE function ....
Purpose Return value Syntax =SECOND(serial_number) serial_number - A valid time in a format Excel recognizes. Using the SECOND function The SECOND function extracts the second component from a time as a number between 0-59. For example, given a time of “12:15:01”, SECOND will return 1. The SECOND function takes just one argument , serial_number , which must be a valid Excel date , a valid Excel time , or a text value Excel can interpret as a time (e....
Transcript In this video, we’ll look at shortcuts to apply and remove borders. When you working with borders in Excel, you may want to turn off gridlines so you can more easily see what you’re doing. In Windows, you can turn gridlines on and off with Alt W + VG On a mac, tick the setting on the Layout tab of the ribbon. So, first off, you can remove all borders with Control + Shift + _ in Windows, Command + Option + _ on a Mac....
Purpose Return value Syntax =SIN(number) number - The angle in radians for which you want the sine. Using the SIN function The SIN function returns the sine of an angle provided in radians. In geometric terms, the sine of an angle returns the ratio of a right triangle’s opposite side over its hypotenuse. For example, the sine of PI()/6 radians (30°) returns the ratio 0.5. =SIN(PI()/6) // Returns 0.5 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....
Purpose Return value Syntax =SINH(number) number - The hyperbolic angle. Using the SINH function The SINH function returns the hyperbolic sine of a number. Given the input 1, the function returns the value 1.175201194. =SINH(0) // returns 1.175201194 Explanation The hyperbolic sine function returns the vertical component of the point on the right branch (x ≥ 1) of the unit hyperbola corresponding to the hyperbolic angle given as input. Together with hyperbolic cosine , the functions parameterize the right branch of the unit hyperbola given by the equation x² - y² = 1....
Explanation The SMALL function is meant to extract the “nth” smallest value from a set of data. The value for N is supplied as the second argument. To get the smallest value with SMALL, supply 1, to get the second smallest value, supply 2, and so on. =SMALL(data,1) // 1st smallest =SMALL(data,2) // 2nd smallest =SMALL(data,3) // 3rd smallest In the example shown, “data” is the named range B5:B14. In this example, the main challenge is to increment a value for nth....
Shortcut recipes can save you hours of drudge work, because they can almost eliminate certain kinds of manual effort. In this way, shortcut recipes are the most powerful kind of shortcut in Excel. In the video below, I explain shortcut recipes, and show you 3 tasty recipes I think you’ll like. Also see: 30+ Popular Excel Shortcuts Resources to help you with Excel shortcuts 200 Excel shortcuts for Win and Mac The 54 Excel shortcuts you really should know Laminated quick reference cards (no wifi needed) How to Use Mac Function Keys in Excel Excel shortcuts course - learn 200 Excel shortcuts
A used range in an Excel worksheet is a concept that defines which cells in a worksheet have been used. In any given worksheet, the current used range can impact how many cells are involved in certain calculations. To find the last cell used in a worksheet (the lower right corner of the used range) you can use keyboard shortcuts: Go to the first cell in the worksheet (Ctrl + Home) Go to the last cell (Ctrl + End) The used range is continually updated as changes are made to a worksheet, and includes any cell that has ever been used....
A few days ago, I posted this conditional formatting puzzle : Given a date, what formula will highlight other dates in the same month and year? Here’s the list: Option #1 - Extract with MONTH and YEAR and test with AND Option #2 - force dates to first of month and compare Option #3 - force dates to last of month and compare Option #4 - concatenate year and month and compare Option #5 - concatenate year and month with TEXT and compare...
Explanation In this example, the goal is to create initials or acronyms with a formula using the data in column B as the source text. The formula should parse the text in column B, build a list of capital letters used to start words and join the capital letters together in a single text string. The article below explains 3 ways to do this. The first two methods require a current version of Excel....
Purpose Return value Syntax =BASE(number,radix,[min_length]) number - The number to convert to a given base. radix - The base to convert to. min_length - [optional] The minimum string length to return, achieved by padding with zeros. Using the BASE function The BASE function converts a number to a given base and returns the result as a text string . Base is specified with the radix argument. The BASE function takes three arguments : number , radix , and min_length ....
Explanation This example shows one way to calculate BMI (Body Mass Index) in Excel. The standard BMI formula is: BMI = weight (kg) / height (m) 2 The approach used here is to first convert height in inches and feet to meters, and weight in pounds to kilograms, then use the standard metric formula for BMI. This makes it easy to collect height and weight in commonly used units (in the United States), and also shows the metric amounts used in the calculation....
Explanation In this example, we want to apply three different colors, depending on how much the original date varies from the current date: Green if the variance is less than 3 days Yellow if the variance is between 3 and 10 days Red if the variance is greater than 10 days For each rule, we calculate a variance by subtracting the original date from the “current” date (as explained above). Then we check the result with a logical expression....
Explanation Conditional formatting rules are evaluated in order. For each cell in the range B5:B12, the first formula is evaluated. If the value is greater than or equal to 90%, the formula returns TRUE and the green fill is applied. If the value is not greater than or equal to 90%, the formula returns FALSE and the rule is not triggered. For each cell in the range B5:B12, the second formula is evaluated....
You can use the form below to contact us. If you have a question about Excel, please try a search or browse our list of topics . Unfortunately, we are not able to reply to every question. If you have made a purchase and are not receiving emails from us, please check your spam folder!
Purpose Return value Syntax =COSH(number) number - The hyperbolic angle. Using the COSH function The Excel COSH function returns the hyperbolic cosine of a number, which represents a hyperbolic angle . Given the input of 1, the function returns the value 1.543080635. =COSH(1) // returns 1.543080635 Explanation The hyperbolic cosine function returns the horizontal component of the point on the right branch (x ≥ 1) of the unit hyperbola corresponding to the hyperbolic angle given as input....
Explanation In this example, the goal is to count cells in a range that are not blank (i.e. not empty). There are several ways to go about this task, depending on your needs. The article below explains different approaches. COUNTA function While the COUNT function only counts numbers, the COUNTA function counts both numbers and text. This means you can use COUNTA as a simple way to count cells that are not blank....
