which removes all text up to and including the colon (:) and following space. In this case, we want to remove the labels that appear inside text. The labels vary in length, and include words like “Make”, “Model”, “Fuel economy”, and so on. Each label is followed by a colon and a space. We can use the colon as a “marker” to figure out where the label ends. Working from the inside out, we use the FIND function to get the position of the colon in the text, then add 1 to take into account the space that follows the colon....

In the example shown, we are using MROUND to round the price in column B using the multiple in column C. The formula in cell D6, copied down the table, is: This tells Excel to take the value in B6 ($63.39) and round it to the nearest multiple of the value in C6 (5). The result in D5 is $65.00, since 65 is the nearest multiple of 5 to 63....

and MIN returns the smaller value. Elegant alternative to IF The MIN function can be a compact and elegant replacement for the IF function. For example, the formula above can be written with IF like so: However, the MIN version is 6 characters shorter (11 vs. 17), and contains no redundant references, so it is less prone to errors. Dave Bruns Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa....

You can unsubscribe anytime by clicking the unsubscribe link at the bottom of one of the emails.

where sales is the named range C5:C16. The result is $5950, the sum of the values in the range C13:C16, which are created with a formula. Forecasted values The forecasted values in the range C13:C16 are created with a formula based on the MROUND function. The formula in C13, copied down, is: This formula is used to generate values that are 5% higher than the previous month, rounded to the nearest multiple of 25....

where region (B5:B20), quarter (C5:C20), and sales (D5:D20) are named ranges. The result is Q3 sales for the Central region, 127,250. The SUMIFS function is designed to sum numeric values based on one or more criteria. In specific cases however, you may be able to use SUMIFS to “look up” a numeric value that meets required criteria. The main reasons to do this are simplicity and speed. In the example shown, we have quarterly sales data for four regions....

1. Don’t add the final parentheses to a function Let’s start out with something really easy! When you enter one function on its own (SUM,AVERAGE, etc.) you don’t need to enter the final closing parentheses. For example, you can just enter: =SUM(A1:A10 and press return. Excel will add the closing parentheses for you. It’s a small thing, but convenient. Note: this won’t work if your formula contains more than one set of parentheses....

To solve for the interest rate, the RATE function is configured like this in cell C9: nper - from cell C7, 10. pmt - from cell -C6, -7500 pv - from cell C4, 0. fv - from cell C5, 100000 With this information, the RATE function returns 0.0624. When a percentage number format is applied, the result displays as 6.24%. Note payment is negative because it represents a cash outflow....

In the example, the active cell contains this formula: Note: the GCD function only works with integers. On the left, the GCD function is used to calculated the greatest common divisor (GCD) of the two numbers. Then the first number is divided by the GCD. On the right, the same operations are performed with the second number. Next, the result of the right and left operations are joined together using concatenation, with the colon (":") as a separator....

This formula above uses a series of nested SUBSTITUTE functions to strip out spaces, hyphens, periods, parentheses, and commas. You’ll need to adjust the actual replacements to suit your data. The inner most SUBSTITUTE removes the left parentheses, and the result is handed to the next SUBSTITUTE, which removes the right parentheses, and so on. Whenever you use the SUBSTITUTE function, the result will be text. Because you can’t apply a number format to text, we need to convert the text to a number....

To compare text strings in a case-sensitive way, you can use the EXACT function. The Excel EXACT function compares two text strings, taking into account upper and lower case characters, and returns TRUE if they are the same, and FALSE if not. If we use EXACT to compare A1 and A2 as above, the result is FALSE: EXACT with IF You can use this result inside the IF function to display a message or make a conditional calculation....

where data is the named range B5:B15. Not equal to The not equal to operator in Excel is <>. For example, with the number 10 in cell A1: The first formula returns TRUE since A1 is indeed not equal to 5. The second formula returns FALSE since A1 is equal to 10. COUNTIFS function The COUNTIFS function counts the number of cells in a range that meet one or more supplied criteria....

Example In the example shown, an asset was purchased on June 30, 2019 at an initial cost of $10,000. The end of the first period is December 31, 2019 and the depreciation rate is 20% per year. The salvage value is $1000, and the basis is European 30/360. The formula in F7, copied down the table is: Notice with the exception of the period numbers in column E, the other arguments use absolute references to prevent changes when copying....

The COUNTA function returns the count of values in the list of supplied arguments. COUNTA takes multiple arguments in the form value1, value2, value3, etc. Arguments can be individual hardcoded values, cell references, or ranges up to a total of 255 arguments. All values are counted, including text, numbers, percentages, errors, dates, times, fractions, and formulas that return empty strings (""). Empty cells are ignored. Examples In the example shown, COUNTA is set up to values in the range B5:B15: COUNTA returns 9, since there are 9 non-empty cells in the range B5:B15....

Video: What is a number format What can you do with custom number formats? Custom number formats can control the display of numbers, dates, times, fractions, percentages, and other numeric values. Using custom formats, you can do things like format dates to show month names only, format large numbers in millions or thousands, and display negative numbers in red. Where can you use custom number formats? Many areas in Excel support number formats....

The DATEVALUE function takes just one argument, called date_text. If date_text is a cell address, the value of the cell must be text. If date_text is entered directly into the formula it must be enclosed in quotes. Examples To illustrate how the DATEVALUE function works, the formula below shows how the text “3/10/1975” is converted to the date serial number 27463 by DATEVALUE: Note that DATEVALUE returns a serial number, 27463, which represents March 10, 1975 in Excel’s date system....

Sometimes, when the result is an array, Excel won’t return the top-left value in array (step #3) unless the formula is entered an array formula with control + shift + enter. In other words, entering a formula with control + shift + enter disables implicit intersection. Example Implicit intersection can occur when a formula is entered next to vertical data, or above or below horizontal data. For example, the formula in D6 in the example shown is: In this case, Excel resolves the range B4:B8 to the value in B6 (3) and returns a result of 4....

Excel uses iteration to arrive at a result, starting with the guess (if provided) or with .1 (10%) if not. If an accurate IRR can’t be calculated after a fixed number of iterations, the #NUM error is returned. A better guess will prevent this error. Notes The values array must contain at least one positive value and one negative value. Values should be in chronological order. If IRR returns the #NUM!...

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.