In the example, the active cell contains this formula: Upper and lower case SUBSTITUTE is a case sensitive function, so it will match case when running a substitution. If you need to count both upper and lower case occurrences of a specific character, use the UPPER function inside SUBSTITUTE to convert the text to uppercase before running the substitution. Then supply an uppercase character as the text that’s being substituted like this:...

where “holidays” is the named range E5:E6. The formula above returns TRUE, since Monday, Dec. 21, 2015 is a workday. Since we want to check a single date and get a TRUE or FALSE result, we would ideally use WORKDAY with the simple formula below: However, this doesn’t work, since WORKDAY does not seem to evaluate a date when no offset is present. The solution is supply (date-1) for start_date, 1 for days, and the named range “holidays” (E5:E6) for holidays....

Where categories is the named range G5:H7, the result is a due date in column E that is based on the category assigned in column C. This kind of formula can be used to set due dates, shipping dates, and other end dates where a group or category determines how much time is allotted. Calculating days The first task in this problem is to calculate the number of days to use for each category, as shown in the range G5:H7....

Although new dynamic array functions will get a lot of attention, it’s important to understand that dynamic array behavior is native and deeply integrated. All formulas will now run on a new calculation engine. This means that when a formula returns multiple results, these results will spill into multiple cells on the worksheet. This includes older functions and formulas not originally designed to work with dynamic arrays. For example, the LEN function returns the number of characters in a string....

In other words, the general concept of nesting is that the “outer” formula is doing something useful and the “inner” formula is providing something the “outer” formula needs to do the job. Usually, the inner function(s) make it possible to avoid hard-coding information into the “outer” function, since hard-coding makes a formula less useful and portable. Example In the example shown, the formula in D4 uses nesting to calculate the current age of a person based on their birthdate: In this formula, the TODAY function is nested inside of the YEARFRAC function, which is nested inside the INT function....

As Timothy R. Mayes, author of Financial Analysis with Microsoft Excel, says on his website TVMCalcs.com: Net present value is defined as the present value of the expected future cash flows less the initial cost of the investment…the NPV function in spreadsheets doesn’t really calculate NPV. Instead, despite the word “net,” the NPV function is really just a present value of an uneven cash flow function. One simple approach is to exclude the initial investment from the values argument and instead subtract the amount outside the NPV function....

First, any expressions in parentheses are evaluated. Parentheses essentially override the normal order of operations to ensure certain operations are performed first. Next, Excel will resolve references. This involves replacing cell references like A1 with the value from the cell, as well as evaluating range references like A1:A5, which become arrays of values. Other range operations like union (comma) and intersection (space) also happen at this time. Next, Excel will perform exponentiation, negation, and percent conversions (in that order), followed by multiplication and division, addition and subtraction, and concatenation....

ROUNDDOWN takes two arguments, number and num_digits. Number is the number to be rounded, and num_digits is the place at which number should be rounded. When num_digits is greater than zero, the ROUNDDOWN function rounds on the right side of the decimal point. When num_digits is less or equal to zero, the ROUNDDOWN function rounds on the left side of the decimal point. Use zero (0) for num_digits to round to the nearest integer....

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.

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.

In the example shown, the UNIQUE function is used to extract unique values from a range of data. UNIQUE is only available in Excel 365, and was introduced in 2020. When the workbook is opened in an older version of Excel, the __xlfn prefix appears. Note that the original result is still displayed. However, the formula will not update to show a new result if data changes. Solution or workaround If you open the same workbook in a version of Excel that contains the missing function(s), the _xlfn prefix will be disappear and the function will calculate normally....

In the example shown, the formula in cell F5 is: where data is a named range for B5:D10 {2,3,4} If you want only the first column number, you can use the MIN function to extract just the first column number, which will be the lowest number in the array. Simple version Entered in a single cell, the COLUMN function will display only the first column number, even when it returns an array....

The results in column E are decimal values with the percentage number format applied. Converting this to an Excel formula with cell references, the formula in E5 becomes: As the formula is copied down, it returns the original for each item in the table, based on the percentages shown in column D. Formatting percentages in Excel In mathematics, a percentage is a number expressed as a fraction of 100. For example, 95% is read as “ninety-five percent” and is equivalent to 95/100 or 0....

The trick is to build a custom table that will map values to all the groups you need. In the example shown, the formula in F5 is: VLOOKUP simply looks up the value and returns the group name from the 2nd column in the table. Both columns in the table can contain any values that you need, and the table doesn’t need to be sorted since VLOOKUP is using exact match....

Conditional formatting is evaluated relative to every cell it is applied to, starting with the active cell in the selection, which is cell B5 in this case. To highlight the matching row, we use this logical expression: The reference to B5 is mixed, with the column locked and row unlocked, so that only values in column B (widths) are compared to the value in K6 (width). In the example shown, this logical expression will return TRUE for every cell in a row where width is 200, based on an approximate match of the value in K6 (width, 275) against all values in K6:B11 (widths)....

1. Search for existing solutions Try a search first to see if you can find an existing solution you can use or adapt. You can save a lot of time by finding a path others have already travelled. 2. State your goal clearly and briefly Be clear about what you want. For example: I need to sum sales by month I need to count orders on Wednesdays or Mondays I need C1 to be blank unless there are numbers in A1 and B1 I need a date 10 days in the future excluding weekends...

Let’s take a look. The LEN function takes just one argument: the text you want to count. If I supply the address B5, which contains the text “Susan,” I’ll get a result of five since there are five letters in the name. The same formula will return the number eight for the text “New York.” That’s because LEN also counts space characters. The abbreviation NY returns two, as you’d expect....

Let’s take a look. Here we have annual sales figures for a small company. We’ll use this data to build a basic column chart. The first step in creating a chart in Excel is to prepare the data. Charting works best if the data is structured in a simple grid that doesn’t include blank rows or columns. First, select the data you’d like to chart. If your data includes totals, don’t include these in your selection....