Explanation In this example, the goal is to split a text string at the underscore("_") character with a formula. Notice the location of the underscore is different in each row. This means the formula needs to locate the position of the underscore character first before any text is extracted. There are two basic approaches to solving this problem. If you are using Excel 365, the best approach is to use the TEXTBEFORE and TEXTAFTER functions....
Purpose Return value Syntax =SQRT(number) number - The number to get the square root of. Using the SQRT function The Excel SQRT function returns the square root of a positive number. SQRT returns an error if number is negative. The SQRT function takes one argument, number , which must be a numeric value. If number is not numeric, SQRT returns a #VALUE! error. If number is negative, SQRT returns a #NUM!...
Explanation In this example, the goal is to sum the Price in column C when the Product in column B begins with “sha”. To solve this problem, you can use either the SUMIF function or the SUMIFS function with the asterisk (*) wildcard, as explained below. Wildcards Certain Excel functions like SUMIF and SUMIFS support the wildcard characters “?” (any one character) and “*” (zero or more characters), which can be used in criteria....
Explanation In this example, the goal is to sum the largest n values in a set of data after applying specific criteria. In the worksheet shown, we want to sum the three largest values, so n is equal to 3. At a high level, this problem breaks down into three separate steps: Apply criteria to select specific values Extract the 3 largest values Sum the 3 extracted values This problem can be solved with a formula based on the FILTER function , the LARGE function , and the SUM function ....
I first saw a reference to this data on the interesting News, in Data site. The data cited comes from the drugabuse.gov website , and is presented in a combo chart with columns showing overall heroin deaths and lines showing the male and female breakdown. As the site says, from 2002 to 2015 there was a 6.2-fold increase in the total number of deaths. Here’s one of the original charts:...
Explanation First, it’s important to understand that the values in the Month column (B) are actual dates, formatted with the custom number format “mmm”. For example, B4 contains January 1, 2014, but displays only “Jan” per the custom number format. The formula itself is based on the NETWORKDAYS function, which returns the number of working days between a start date and end date, taking into account holidays (if provided). For each month, the start date comes from column B and the end date is calculated with the EOMONTH function like so:...
Purpose Return value Syntax =WRAPROWS(vector,wrap_count,[pad_with]) vector - The array or range to wrap. wrap_count - Max values in each row. pad_with - [optional] Value to use for unfilled places. Using the WRAPROWS function The WRAPROWS function converts a one-dimensional array into a two-dimensional array by wrapping values into separate rows. The length of each row is provided as the wrap_count argument: when the count is reached, WRAPROWS starts a new row....
Explanation XLOOKUP offers several features that make it exceptionally good for more complicated lookups. In this example, we want the latest price for an item by date . If data were sorted by date in ascending order, this would be very straightforward . However, in this case, data is unsorted . By default, XLOOKUP will return the first match in a data set. To get the last match , we can set the optional argument search_mode , to -1 to cause XLOOKUP to search “last to first”....
Transcript Excel charts can have a surprisingly large number of parts, each with its own name. In this lesson we’re going to break down a sample chart into parts and give you the name for each. Let’s take a look. Here we have a basic column chart. The chart itself—the rectangle that holds everything else—is referred to as the “chart area.” When you select a chart, Excel will wrap the chart in a frame that contains eight handles....
Purpose Return value Syntax =AVERAGEA(value1,[value2],...) value1 - A value or reference to a value that can be evaluated as a number. value2 - [optional] A value or reference to a value that can be evaluated as a number. Using the AVERAGEA function The AVERAGEA function returns the average of a set of supplied values. AVERAGEA will include the logical values TRUE and FALSE, and numbers represented as text in the calculation....
Transcript In this video, we’ll look at a basic example of sorting with the SORTBY function . In this worksheet, we have a list of names, scores, and groups. Currently, the data is not sorted. Our goal is to sort this data by group, then by score in descending order. I’ll start off by placing the cursor in cell F5, then typing an equals sign (=) and the first few letters of “SORTBY”....
Explanation CAGR stands for Compound Annual Growth Rate. CAGR is the average rate of return for an investment over a period of time. It is the rate of return required for an investment to grow from the starting balance to the ending balance, assuming profits are reinvested each year, and interest compounds annually. There are several ways to calculate CAGR in Excel. CAGR with the RRI function In Excel 2013 and later, you can use the RRI function to calculate CAGR with a simple formula....
Explanation In this example, the goal is to build a formula that will return TRUE if a given cell contains all values that appear in a given range. We could build a formula that uses nested IF statements to check for each value, but that won’t scale well if we have a lot of values to test because each new value will require another nested IF. The article below explains a more scalable approach based on the SEARCH function....
Explanation The TEXT function applies the number format you specify to a numeric value, and returns a result as text. In this case, the number format provided is “yyyymm”, which joins a 4-digit year with a 2-digit month value. Display only option If you only want to display a date with the year and month, you can simply apply the custom number format “yyyymm” to the date(s). This will cause Excel to display the year and month together, but will not change the underlying date....
Explanation In this example, the goal is to create a formula that converts a numeric value in inches to a format that displays inches and feet, as seen in the table below: Input Output 9 0’ 9" 12 1’ 0" 30 2’ 6" 75 6’ 3" The math for this problem is fairly simple, but the problem is more complex because we need to assemble a text string that includes a single quote for feet (’) and a double quote (") for inches....
Explanation This formula relies on the CONVERT function , which can convert a number in one measurement system to another. To perform the conversion, CONVERT relies on “from” and “to” units entered as text. As long as the units specify valid options, CONVERT will automatically perform a conversion and return a numeric result. To convert pounds to kilograms, the formula used is in C5, copied down, is: =CONVERT(B5,"lbm","kg") Note: CONVERT is case-sensitive, so the text values used for units must match exactly....
Explanation In this example, the goal is to count codes in a case-sensitive way. The COUNTIF function and the COUNTIFS function are both good options for counting text values, but neither is case-sensitive, so they can’t be used to solve this problem. The solution is to use the EXACT function to compare codes and the SUMPRODUCT function to add up the results. EXACT function The EXACT function’s sole purpose is to compare text in a case-sensitive manner....
Explanation For each cell in the range, SUBSTITUTE removes all the o’s from the text, then LEN calculates the length of the text without o’s. This number is then subtracted from the length of the text with o’s. Because we are using SUMPRODUCT, the result of all this calculation is a list of items (an array), where there is one item per cell in the range, and each item a number based on the calculation described above....
Explanation In this example, the goal is to count the total number of words in a cell. Excel doesn’t have a dedicated function for counting words. However, with a little ingenuity, you can create a formula to perform this task using a combination of built-in functions. In newer versions of Excel, the best approach is to use the TEXTSPLIT and COUNTA functions. In older versions of Excel, you can use a more complicated formula based on the SUBSTITUTE and LEN functions....
Explanation There is no direct way to detect a hidden column with a formula in Excel. You might think of using the SUBTOTAL function , but SUBTOTAL only works with vertical ranges. As a result, the approach described in this example is a workaround based on a helper formula that must be entered in a range that includes all columns in the scope of interest. In this example, this range is the named range “key”....
