Explanation In geometry, the area enclosed by a circle with radius (r) is defined by the following formula: πr 2 The Greek letter π (“pi”) represents the ratio of the circumference of a circle to its diameter. In Excel, π is represented in a formula with the PI function , which returns the number 3.14159265358979, accurate to 15 digits: =PI() // returns 3.14159265358979 To square a number in Excel, you can use the exponentiation operator (^):...

Explanation The NPER function returns the number of periods for loan or investment. You can NPER to get the number of payment periods for a loan, given the amount, the interest rate, and periodic payment amount. An annuity is a series of equal cash flows, spaced equally in time. The goal in this example is to calculate the years required to save 100,000 by making annual payments of $5,000 where the interest rate is 5% and the starting amount is zero....

Explanation Dates and times in Excel are stored as serial numbers and converted to human-readable values on the fly using number formats. When you enter a date in Excel, you can apply a number format to display that date as you like. Similarly, the TEXT function allows you to convert a date or time into text in a preferred format. For example, if the date January 9, 2000, is entered in cell A1, you can use TEXT to convert this date into the following text strings as follows:...

Explanation Note: there are many ways to summarize data with COUNTIFS, SUMIFS, etc. This example shows one specific and arbitrary way. Before you go the formula route, consider a pivot table first , since pivot tables are far simpler to set up and do most of the hard work for you. The table in B3:F11 is a log of course data where dates in columns D:F indicate course completion. The summary table in H3:K5 summarizes course completion by group instead of user....

Explanation In this example, the goal is to mark dates in column D with an “x” when they have the same year and month as the date in cell B5. We don’t care at all about the day. At a high level, we can easily use the IF function to return “x” for qualifying dates, so the challenge is in creating a logical test we can use inside IF that will properly test the dates....

Purpose Return value Syntax =DISC(settlement,maturity,pr,redemption,[basis]) settlement - Settlement date of the security. maturity - Maturity date of the security. pr - Security price per $100 face value. redemption - Security redemption value per $100 face value. basis - [optional] Day count basis (see below, default =0). Using the DISC function The Excel DISC function returns the discount rate for a security. A discounted security does not pay periodic interest, but has a specified redemption value at maturity....

About This Shortcut By default, when you drag a selection to a new location, the values in the selection will overwrite cells at the new location. Use this shortcut to insert cells at the new location. This also works when dragging entire rows or columns. About This Shortcut By default, drag and drop “cuts” cells like Edit > Cut, and overwrites values at the new location. Use this shortcut to copy values and to insert cells at the new location....

Explanation In this example, criteria are entered in the range F5:H6. The logic of the formula is: item is (tshirt OR hoodie) AND color is (red OR blue) AND city is (denver OR seattle) The filtering logic of this formula (the include argument) is applied with the ISNUMBER and MATCH functions, together with boolean logic applied in an array operation. MATCH is configured “backwards”, with lookup_values coming from the data, and criteria used for the lookup_array ....

Purpose Return value Syntax =FLOOR(number,significance) number - The number that should be rounded. significance - The multiple to use when rounding. Using the FLOOR function The Excel FLOOR function rounds a number down to a given multiple. The multiple to use for rounding is provided as the significance argument. If the number is already an exact multiple, no rounding occurs and the original number is returned. The FLOOR function takes two arguments , number and significance ....

Explanation Working from the inside out, the expression C5:G5<>"" returns an array of true and false values: {FALSE,TRUE,FALSE,FALSE,FALSE} The number 1 is divided by this array, which creates a new array composed of either 1’s or #DIV/0! errors: {#DIV/0!,1,#DIV/0!,#DIV/0!,#DIV/0!} This array is used as the lookup_vector. The lookup_value is 2, but the largest value in the lookup_array is 1, so LOOKUP will match the last 1 in the array. Finally, LOOKUP returns the corresponding value in result_vector, from the dates in the range C$4:G$4....

Explanation In this example, the goal is to extract the domain name from a list of URLs. In the current version of Excel, the easiest way to do this is to use a formula based on the TEXTAFTER and TEXTBEFORE functions. In older versions of Excel, you can use a more complicated formula based on the LEFT and FIND functions. Both approaches are explained below. TEXTAFTER with TEXTBEFORE The formula in the worksheet shown uses the TEXTAFTER and TEXTBEFORE functions to extract domain names from URLs....

Explanation The CELL function can return various information about a worksheet. CELL can get things like address and filename, as well as information about the formatting used in the cell. The type of information to be returned is specified by the info_type argument . In this example, we want the path, name, and sheet for the current workbook. To get this information, the info_type argument is set to “reference” in a formula like this:...

Explanation Note: the lookup_value of 2 is deliberately larger than any values in the lookup_vector, following the concept of bignum . Working from the inside out, the expression: (TEXT($B$5:$B$13,"mmyy")=TEXT(E5,"mmyy")) generates strings like “0117” using the values in column B and E, which are then compared to each other. The result is an array like this: {TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE} where TRUE represents dates in the same month and year. The number 1 is then divided by this array....

Explanation This formula uses the WORKDAY function to calculate an end date. WORKDAY can calculate dates in the future or past, and automatically excludes weekends and holidays (if provided). In the example shown, we have the project start date in column C, and days in column D. Days represents the duration of he project in work days. In column E, the WORKDAY function is used to calculate an end date. Holidays are provided as the named range “holidays”, G5:G9....

Explanation In this example, the goal is to extract the year number from a list of dates in column B. This can be easily achieved with the YEAR function . The YEAR function takes just one argument, the date from which you want to extract the year. For example, in the formula below, we pass the “12-Dec-1999” into the YEAR function, which returns 1999: =YEAR("12-Dec-1999") // returns 1999 In the worksheet shown, we use a cell reference instead of hard-coding the date....

Explanation When you use a formula to apply conditional formatting, the formula is evaluated relative to the active cell in the selection at the time the rule is created. So, in this case the formula =B4>100 is evaluated for each of the 40 cells in B4:G11. Because B4 is entered as a relative address, the address will be updated each time the formula is applied. The net effect is that each cell in B4:G11 will be compared to 100 and the formula will return TRUE if the value in the cell is greater than 100....

Transcript In this lesson, we’ll look at how to find things in Excel. Let’s take a look. To find a value in Excel, use the Find and Replace dialog box. You can access this dialog using the keyboard shortcut control-F, or, by using the Find and Select menu at the far right of the Home tab on the ribbon. Let’s try looking for the name Ann . Nothing happens until we click the Find Next button....

Transcript In this video, we’ll look at how to highlight approximate match lookups with conditional formatting. Here we have a simple lookup table that shows material costs for various heights and widths. The formula in K8 uses the INDEX and MATCH functions to retrieve the correct cost based on width and height values entered in K6 and K7. Note that the lookup is based on an approximate match. Since values are in ascending order, MATCH checks the values until a larger value is reached, and then steps back and returns the previous position....

Explanation In this example, the goal is to get the maximum value for a given group below a specific temperature. In other words, we want the max value after applying multiple criteria. The easiest way to solve this problem is with the MAXIFS function. However, if you need more flexibility (i.e., you need to work with arrays and not ranges), you can use the MAX function with the FILTER function. In older versions of Excel without MAXIFS or FILTER, you can use a traditional array formula based on the MAX function and the IF function....

Purpose Return value Syntax =MINIFS(min_range,range1,criteria1,[range2],[criteria2],...) min_range - Range of values used to determine minimum. range1 - The first range to evaluate. criteria1 - The criteria to use on range1. range2 - [optional] The second range to evaluate. criteria2 - [optional] The criteria to use on range2. Using the MINIFS function The MINIFS function returns the smallest numeric value in cells that meet multiple conditions, referred to as criteria . Each condition is provided with a separate range and criteria ....