Let’s take a look. Here we have the table of random numbers. Let’s first build a conditional format that uses a formula to highlight cells greater than 500. As before, select the full set of values, then choose New Rule from the Conditional Formatting menu, then choose the option for formula. To highlight number greater than 500, we’ll need a formula that returns true when a cell value is greater than 500....
where “group” is the named range B5:B14, and “data” is the named range C5:C14. Note: this is an array formula and must be entered with control + shift + enter. To apply criteria, we use the IF function inside MEDIAN to “filter” values. In this example, the IF function filters by group like this: This expression compares each value in the named range “group” against the value in E5 (“A”)....
The formula returns the word count in cell B5. The first part of the formula counts the characters in cell B5, after removing extra space: Inside LEN, the TRIM function first removes any extra spaces between words, or at the beginning or end of the text. This is important, since any extra spaces will throw off the word count. In this case, there are no extra space characters, so TRIM returns the original text directly to the LEN function, which returns 30: At this point, we have: Next, we use the SUBSTITUTE function to remove all space characters from the text: Notice SUBSTITUTE is configured to look for a space character (" “), and replace with an empty string (”")....
where “key” is the named range B4:F4, and all cells contain this formula, copied across: To see the count change, you must force calculation with F9, or perform another worksheet change that triggers recalculation. Below is the same worksheet with all columns visisble: Note: I ran into the core idea for this formula on the excellent wmfexcel.com site. In the example shown, columns C and E are hidden. The helper formula, entered in B4 and copied across B4:F4, is based on the CELL function: The CELL function will only return a width for a cell in a visible column....
In the example shown, the formula in cell E5 is: Note: the other examples in column E all use different text formats. End date missing If the end date is missing, the formula won’t work correctly because the hyphen will still be appended to the start date (e.g.“March 1 - “). To handle this case, you can wrap the concatenation and second TEXT function inside IF like so: This creates the full date range when both dates are present, but outputs only the start date when the end date is missing....
where “start” is the named range G4, and “offset” is the named range G5. Notes: (1) the offset represents days before the start date to display in the list. (2) the shading of the start date is done with conditional formatting as described below. In the example shown, the date in the named range “start” is provided by the TODAY function: The formula in B5 begins with the start date, and increments the date by one using an expanding range inside the ROWS function: ROWS returns the row count in a range....
Syntax The syntax for the AVERAGEIFS function depends on the criteria being evaluated. Each separate condition will require a range and a criteria. The generic syntax for SUMIFS looks like this: The first argument, avg_range, is the range of cells to average, which should contain numeric values. The second argument, range1, is the range to which the first condition should be applied. The third argument, criteria1, contains the condition that should be applied to range1, along with any logical operators....
The first argument in the CHOOSECOLS function is array. Array can be a range, or an array from another formula. Additional arguments are in the form col_num1, col_num2, col_num3, etc. Each number represents a specific column to extract from the array, and should be supplied as a whole number. Basic usage To get columns 1 and 3 from an array, you can use CHOOSECOLS like this: To get the same two columns in reverse order: CHOOSECOLS will return a #VALUE!...
The settlement date is the date the investor takes possession of a security. The maturity date is the date when the investment ends and the principle plus accrued interest is returned to the investor. The frequency is the number of interest payments per year. Basis specifies the method used to count days (see below). In the example shown, the formula in F6 is: COUPNCD will return a valid Excel date, so use a date format to display properly....
Although frustrating, formula errors are useful, because they tell you clearly that something is wrong. This is much better than not knowing. The most disastrous Excel mistakes usually come from normal-looking formulas that quietly return incorrect results. When you run into a formula error, don’t panic. Stay calm and methodically investigate until you find the cause. Ask yourself, “What is this error telling me?” Experiment with trial and error. As you gain more experience, you’ll be able to avoid many errors, and more quickly correct errors that do arise....
Notice a full column reference is entered like other ranges, with a colon (:) separating the starting point from the ending point. Since there are no row numbers in a full column reference, the literal translation the range A:A is “every cell in column A”. Like other ranges, a full column reference can include multiple columns. For example, to reference columns A through D: In the example shown above, the formula in cell G5, copied down, used two full column references: Here, B:B is the range, and C:C is the sum_range....
HLOOKUP searches for a value in the first row of a table. When it finds a match, it retrieves a value at that column from the row given. Use HLOOKUP when lookup values are located in the first row of a table. Use VLOOKUP when lookup values are located in the first column of a table. HLOOKUP takes four arguments. The first argument, called lookup_value, is the value to look up....
For example: In the example shown, the formula in D6, copied down, is: Notes: Only lowercase “j” and “i” are accepted by IMPOWER. Other values will result in the #NUM error. The number argument must be numeric 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....
What it Returns It returns a logical argument which is the reverse of the logical argument used within the NOT function. For example, =NOT(TRUE) returns FALSE and =NOT(FALSE) returns TRUE. Syntax =NOT(logical) Input Arguments logical – A value or expression that can be evaluated to TRUE or FALSE. Additional Notes: You can check expression with NOT function that evaluates to TRUE or FALSE. For example, =NOT(1+1=2) would return FALSE....
Notes pmt normally includes principal and interest but not taxes, reserve payments, or fees. Be sure you supply rate consistent with periods. For example, if an annual interest rate is 12 percent, use 12%/12 for rate to get NPER to return periods in months. 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 RATE function takes six separate arguments, three of which are required as explained below. 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 show how the number was determined. pmt (required) - The payment made each period. This number cannot change over the life of the annuity....
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: as of February 2022, the Mac shortcut doesn’t seem to work in Excel 365. 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.
SUBSTITUTE is case-sensitive. To replace one or more characters with nothing, enter an empty string (""). Examples Below are the formulas used in the example shown above: The SUBSTITUTE function cannot replace more than one string at a time. However, SUBSTITUTE can be nested inside of itself to accomplish the same thing. For example, with the text “a (dog)” in cell A1, the formula below will strip parentheses () from text: This same approach can be used in a more complex formula to normalize telephone numbers....
