In the example shown, the formula in C5 is: The MID function has been configured to always start at 4, which effectively strips the starting tag from the value. The second argument, num_chars, is calculated by getting the total characters in the cell with LEN, then subtracting 7. We use 7 because the starting bold tag is 3 characters and the closing tag is 4 characters = 7 characters total....
The result is 23, the sum of numbers in C5:C16 when text in B5:B16 contains the substring “red” or the substring “blue”. SEARCH + ISNUMBER for substrings The core of this formula is based on the SEARCH function together with the ISNUMBER function. The SEARCH function is designed to find a specific substring in a text string. If SEARCH finds the substring, it returns a position of the substring in the text as a number....
which outputs the 3 unique values that appear more than once in the named range data (B5:B16). Note: In this example, we are extracting a unique list of values that appear more than once. In other words, we are creating a list of duplicates :) The language is somewhat confusing. The trick in this case is to apply criteria to the FILTER function to only allow values based on count of occurence....
On the Design tab of the Chart Tools menu of the ribbon, you’ll find controls for chart colors and chart styles. Once you’ve selected a chart type, you can use these tools to apply colors and styles. Similar to chart layouts, these controls let you quickly adjust a chart to follow a certain look or theme. The Change Color menu let’s you quickly experiment with multi-color schemes as well as monochromatic schemes....
In the chart shown, data is plotted in one series. Values come from a named range called “values”, defined with the formula provided below: Axis labels come come from a named range called “groups”, defined with this formula: This page explains dynamic named ranges created with INDEX in more detail. How to make this chart Create a normal chart, based on the values shown in the table. If you include all rows, Excel will plot empty values as well....
The result is 6, since there are six cells in B5:B15 that contain the letter “a”. COUNTIF function The COUNTIF function counts cells in a range that meet supplied criteria. For example, to count the number of cells in a range that contain “apple” you can use COUNTIF like this: Notice this is an exact match. To be included in the count, a cell must contain “apple” and only “apple”....
Next SUBSTITUTE removes all “line returns” from the text in B5 by looking for CHAR(10) which is the character code for the return character in Windows. LEN returns the result inside of a second LEN, which counts characters without carriage returns. The second count is subtracted from the first, and 1 is added to the final result, since the number of lines is the number of returns + 1....
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 show, the formula in F6 is: Entering dates In Excel, dates are serial numbers....
Example To use ENCODEURL, supply text or a cell reference that contains text. In the example below, ENCODEURL is used to encode the text “Hello World!” In the example at the top of the page, the formula in cell C5, copied down is: At each new row, ENCODEURL returns the encoded text from column B. Reserved characters The table below shows a list of reserved characters and their url-encoded equivalent....
XML is a text format for storing and transporting data. It is not dependent on any particular hardware or software. XML is extensible and is designed to transport data, as opposed to displaying data in a particular way. XML has strict syntax rules which allows software to traverse the structure of an XML document and perform various operations. XPath is a special query language for selecting the elements and attributes in an XML document....
Note: Starting with Excel 2016, the FORECAST function was replaced with the FORECAST.LINEAR function. Microsoft recommends replacing FORECAST with FORECAST.LINEAR, since FORECAST will eventually be deprecated. Note: Starting with Excel 2016, the FORECAST function was replaced with the FORECAST.LINEAR function. Microsoft recommends replacing FORECAST with FORECAST.LINEAR, since FORECAST will eventually be deprecated. In statistics, linear regression is an approach for modeling the relationship between a dependent variable (y values) and an independent variable (x values)....
In the example shown, the formula in D6, copied down, is: Notes: Only lowercase “j” and “i” are accepted by IMAGINARY. Other values will result in the #NUM error. 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....
Examples To extract the first three characters of “January”: 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 LEFT function is often combined with other functions like LEN and FIND to extract text in more complex formulas. For example, to split text at a specific character, use LEFT with the FIND function like this: FIND returns the position of the character, and LEFT returns all text to the left of that position....
Example 1 | Example 2 | Example 3 | Example 4 | More examples Variables are named and assigned values in pairs, separated by commas (name1,value1, name2,value2, etc). LET can handle up to 126 name/value pairs, but only the first name/value pair is required. The scope of each variable is the current LET function, and nested functions below. The final result is a calculation or a variable previously calculated. The result from LET always appears as the last argument to the function....
Examples The NA function returns the #N/A error: You can use the NA function in other formulas. For example, in the formula below, the IF function is configured to test if cell A1 is empty. If so, IF returns NA(), which returns the #N/A error. If A1 is not empty, IF returns A1*B1: You can use the NA function to indicate missing information. In the worksheet shown above, cells C9 and C13 contain the NA function: This indicates that cost is not available....
NORM.S.DIST expects Standardized Input NORM.S.DIST expects standardized input in the form of a z-score value. A z-score value represents how far a value is from the mean of a distribution in terms of the standard deviation of the distribution. To calculate the z-score, subtract the mean from the value and then divide by the standard deviation or use the STANDARDIZE function as shown in the two formulas below: Note, see the NORM....
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 formula used to highlight duplicate values is: Both ranges were selected at the same when the rule was created. The core of this formula is the COUNTIF function, which returns a count of each value in both range inside the AND function: COUNTIF will either return zero (evaluated as FALSE) or a positive number (evaluated as TRUE) for each value in both ranges. If both counts are positive (i....
