Examples The COUNT function counts numeric values and ignores text values: Typically, the COUNT function is used on a range. For example, to count numeric values in the range A1:A10: In the example shown, COUNT is set up to count numbers in the range B5:B15: COUNT returns 6, since there are 6 numeric values in the range B5:B15. Text values and blank cells are ignored. Note that dates and times are numbers, and therefore included in the count....

The database argument is a range of cells that includes field headers, field is the name or index of the field to query, and criteria is a range of cells with headers that match those in database. Using the example above, you can get the standard deviation of heights for the group “Fox” with either of these formulas: The standard deviation for all heights in C8:C13 is calculated in F5 with the STDEV....

Example In the example shown, we want to find the effective annual interest rate for a bond with a price of $895.00 and a redemption value of $1000. The settlement date is 6-Jul-2017 and the maturity date is 15-Jan-2020. There are no periodic interest payments, and the day count basis is US (NASD) 30/360. The formula in F5 is: With these inputs, the INTRATE function returns 4.65%, with percentage number format applied....

rate (required) - the interest rate per period. For example, if the annual interest rate is 6% and periods are one month, then the interest rate is =6%/12 = 0.5% (0.005). You can enter rate as 6%/12 as a reminder of how it is derived. nper (required) - The total number of payment periods in the annuity. For example, a 5-year car loan with monthly payments has 60 periods. You can enter nper as 5*12 to note how the number was determined....

Examples In the example below, we extract the state code “OR” (Oregon) from the string “Portland, OR” If the optional argument num_chars is not provided, it defaults to 1: If num_chars exceeds the string length, LEFT returns the entire string: When LEFT is used on a numeric value, the result is text: The RIGHT function is often combined with other functions like LEN and FIND to extract text in more complex formulas....

On the Mac, this shortcut works directly on the worksheet, and adds a border to each cell in the selection. 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: In Mac 2016, Control - also works (same as Windows). 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 UNIQUE function takes three arguments: array, by_col, and exactly_once. The first argument, array, is the array or range from which to extract unique values. This is the only required argument. The second argument, by_col, controls whether UNIQUE will extract unique values by rows or by columns. By default, UNIQUE will extract unique values in rows. To force UNIQUE to extract unique values by columns, set by_col to TRUE or 1....

where data is the named range B5:B15 and n is 3. Working from the inside out, we use the SEQUENCE function to construct a row number value for INDEX like this: We are asking SEQUENCE for an array of 3 rows x 1 column, starting at 1, with a step value of 1. The result is an array like this: which is returned directly to the INDEX function as the row_num argument: To construct the array for INDEX, we use the FILTER function to retrieve a list of non-blank entries from the named range data (B5:B15) like this: The array argument is data, and the include argument is the expression data<>""....

Note references are mixed to lock rows and columns as needed in order to test each cell in the grid correctly. The calendar header (row 4) is a series of valid Excel times, formatted with the custom number format “hh”. This makes it possible to set up a conditional formatting rule that compares the time associated with each column in row 4 with the times entered in columns B and C....

where data is the named range B5:D10. Note: In Excel 365, the syntax is slightly different, see below. First cell To get the first cell in the range, we use this formula: The ROW function returns the first row number associated with the range, 5 and the COLUMN function returns the first column number associated with the range, 2. With abs_num set to 4 (relative), ADDRESS returns the text “B5”....

This date goes into WORKDAY function as the “start date”, along with -1 for “days”. The WORKDAY function automatically steps back 1 day, taking into account any weekends. The result is the last workday of the month. Holidays To get the last working day of the month, taking into account holidays, just add the range that contains holiday dates to the formula like this: Custom weekends The WEEKDAY function assumes weekends are Saturday and Sunday....

The results in column E are decimal values with the percentage number format applied. Profit margin is the ratio of profit divided by revenue. The general formula where “x” is profit margin is: In the table shown, we have price and cost, but profit is not broken out separately in another column, so we need to calculate profit by subtracting Cost from Price: and profit is calculated by subtracting Cost from Price....

All Excel Tables come with a built-in Total Row feature. The total row allows you to easily show summary calculations below a table. You can use this total row to calculate counts, sums, min and max, averages, and more. There are a couple ways you can display a total row for a table. First, with any cell selected in the table, you can use the Design tab of the ribbon, under Table Tools....

Have you ever had to check to make sure certain lines of text aren’t too long? For example, maybe you need to check values being imported to a database or website that only allows a certain number of characters per field? In a case like this, what’s a good way use Excel to flag values that need to be shortened? One approach I like is to use conditional formatting with simple functions....

Let’s take a look. The easiest way to create a custom format in Excel is to first apply a format that is similar to the format you want to create. For example, suppose we want to create a custom format that shows positive numbers with a positive sign and negative numbers with a negative sign. In addition, we want this format to have a single decimal place and to display negative numbers in red....

One of the best things about the new dynamic array formula engine in Excel is that it’s much easier to see and visualize arrays. Let’s take a look at a few examples. The first way to see arrays is to enter formulas or expressions directly on the worksheet. For example, here we have some numbers in the range B5:B14. In cell E5, I have a formula that squares and then sums the values in the range....

Excel contains a special function for repeating text named REPT which stands for “repeat.” The REPT function takes two arguments: the text to repeat, and the number of times to repeat the text. So, if I enter an upper case “A” and the number “3,” we’ll get a triple A. If I expose these arguments on the worksheet, I can quickly add a few more examples. In each case, Excel simply repeats the text you provide as many times as you specify....

In the example shown, the formula in C5 is: This formula generates a working hyperlink to the first blank cell in column C. Basically, we are giving match a “big number” it will never find in approximate match mode. In this mode match will “step back” the last numeric value. Note: this works in this case because all values in C are numeric, and there are no blanks. For other situations (text values, etc....