Posts

where data is the named range B5:B16. Note: this is an array formula and must be entered with control + shift + enter in Legacy Excel. where data is the named range B5:B16. Background study Below are related links to help you understand how this formula works: What is an array formula? - 3 min video What is an array? - 3 min video COUNTIF function Working from the inside-out, the core of the formula is based on the COUNTIF function: Here, data (B5:B16) is given for both range and criteria....

where “data” is the named range B5:B10. The results can be seen in the range D10:F10, which correctly contains 10, 15, and 20. However, if we wrap the formula in the SUM function: The final result is 10, while it should be 45, even if entered as an array formula. The problem is that INDEX only returns the first item in the array to the SUM function. To force INDEX to return multiple items to SUM, you can wrap the array constant in the N and IF functions like this: which returns a correct result of 45....

The value in B6 is 17 and the result is 15 since 15 is the nearest multiple of 5 to 17. Other multiples As you’d expect, you can use MROUND to round to other multiples as well: And so on. Force up or down You can use the CEILING function to force rounding up to the nearest multiple and the FLOOR function to force rounding down to the nearest multiple....

Let’s take a look. Normally, to add the same value to a group of cells, you’d add it to one cell, copy the value, then paste. Or, you could use the fill handle. But there’s a faster way to do it. Just select all the cells from the start and type the data you want to enter. Then, while holding down the control key, press the enter key. The value is then entered into all cells at once....

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....

While it’s quite easy to fill color in a cell in Excel (using the inbuilt option in the ribbon), it’s not the fastest way to do it. And if this is something you have to do multiple times in a day, knowing faster ways to fill color in a cell would make you more efficient and also save time. And needless to say, you can always use the shortcuts and tricks I cover in this tutorial to impress your boss and colleagues....

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....

Syntax DAYS(Start Date, End Date) Start Date (required) – This is the starting date of the period.End Date (required) – This is the ending date of the period. Example In this example we find the number of days between two dates. Notice that if the end date is older than the start date the result is a negative number. The same result can be obtained by subtracting the start date from the end date....

Conditional Data Entry in Excel Using Data Validation Here are a few examples of conditional data entry rules: Allow data entry from a pre-defined list only (using drop-down lists). Allow data entry only when the specified cell(s) are filled. Allow DATE entry between two specified dates only. You can also combine multiple conditions to create a data entry rule. This type of conditional data entry in excel can be done using the data validation feature in Excel....

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....