In the example shown, the formula in C5 is: Note that we are also using the asterisk (*) as a wildcard for zero or more characters on either side of the substrings. This is what allows COUNTIF to count the substrings anywhere in the text (i.e. this provides the “contains” behavior). Because we are getting back an array from COUNTIF, we use the SUM function to sum all items in the array....
The result is the text in B5 joined to the text in C5, separated by a space. When you use concatenation in a formula, be sure to enclose literal text in double quotes (""). Do not enclose the ampersand (&) in quotes, unless you want it to appear in the final result. With CONCATENATE You can also use the CONCATENATE function to join text like this: The CONCATENATE function keeps things tidy, but the ampersand creates shorter, more flexible formulas....
In the example shown, the formula in C4 is: In this case, holidays are supplied as the named range holidays (E4:E8), so holidays are taken into account as well. Notice that Excel only cares about holiday dates, not holiday names. Flexible weekends WORKDAY defines a weekend as Saturday and Sunday. If you need more flexibility, use the WORKDAY.INTL function instead. Dave Bruns Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa....
Context There may be times when you need to remove some of the variability of text before other processing. One example is when you want to count specific words inside larger text strings. Because Excel doesn’t provide support for regular expressions, you can’t construct precise matches. For example, if you want to count how many times the word “fox” appears in a cell, you will end up counting “foxes”. You can look for “fox " (with a space) but that will fail with “fox,” or “fox....
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....
Excel chart data labels are quite boring. While there is not much you can do with these, a bit of excel trickery can add some glamor to it. In this post, I will show you how to show trends in chart labels in a bar chart. Something as shown below: Suppose I have the data as shown below: If I plot 2013 data only, it won’t tell us how much the sales of a product have increased/decreased over the last year....
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....
At some point in your career as an Excel user, you will need to delete a sheet from your workbook. Maybe it’s no longer needed, or maybe you made a mistake and need to start over. In any case, deleting a sheet is a fairly straightforward process that can be accomplished in just a few simple steps. This post will show you how to delete a sheet in Excel using several different methods....
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....
