Posts

Purpose Return value Syntax =MIN(number1,[number2],...) number1 - Number, reference to numeric value, or range that contains numeric values. number2 - [optional] Number, reference to numeric value, or range that contains numeric values. Using the MIN function The MIN function returns the smallest numeric value in the data provided. The MIN function can be used to return the smallest value from any type of numeric data. For example, MIN can return the fastest time in a race, the earliest date, the smallest percentage, the lowest temperature, or the bottom sales number....

When evaluating a formula, Excel follows a standard math protocol called “order of operations”. In general, Excel’s order of operation follows the acronym PEMDAS (Parentheses, Exponents, Multiplication, Division, Addition, Subtraction) but with some customization to handle the formula syntax in a spreadsheet. 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....

Ordinal data has a distinct order or natural sequence. An example is survey data collected in response to the question like this: How old are you? Under 18 19-25 26-40 41-60 60+ The chart above shows how the data can be plotted in an Excel column chart that follows the natural order. Another example of ordinal data is data collected with a rating scale like: How satisfied are you with your internet provider?...

Transcript Okay, so in this example we have 250 films…these are the top 250 films from the IMDB website for the year 2014. And you can see we got Rank, Title, Director, Rating, Genre, and Runtime. So let’s use a Pivot table to answer some questions about this data, and my first question is: What are the top 10 films by rating? Okay, so there are the top 10 films in that list by rating....

Pivot tables provide a built-in ranking feature, and can rank smallest to largest or largest to smallest. In the example shown, a pivot table is used to group and rank sales by item. The pivot table is also sorted by rank so that the top sales items appear first. Fields The source data contains six fields, but only two fields are used to build the pivot table, Item and Amount:...

Purpose Return value Syntax =RANK.AVG(number,ref,[order]) number - The number to rank. ref - A range that contains the numbers to rank against. order - [optional] Rank ascending or descending. Default is zero. Using the RANK.AVG function The RANK.AVG function returns the rank of a numeric value compared to a list of other numeric values. RANK.AVG can rank values from largest to smallest (i.e., the rank of highest test scores) and smallest to largest (i....

Explanation In this example, the goal is to remove the last word from the text strings in column B. This article explains two approaches: A modern formula based on the TEXTBEFORE function. A more traditional formula for older versions in Excel. The first option is much simpler, and you should use it if you have the TEXTBEFORE function. The second formula is significantly more complex and only makes sense if you don’t have TEXTBEFORE....

Explanation MROUND rounds to nearest values based on a supplied multiple. When you supply “0:15” as the multiple, Excel internal converts 0:15 into 0.0104166666666667, which is the decimal value that represents 15 minutes, and rounds using that value. You can also express 15 minutes in a formula with this formula: =15/(60*24) The formula above divides 15 by 1440, which is the number of minutes in one day. So, to Excel, these formulas are identical:...

About This Shortcut This shortcut will select all cells with formulas that refer directly or indirectly to cells in the current selection. About This Shortcut This shortcut will select only visible cells in the current selection. This means hidden cells in the current selection will not be selected. Watch our video tip on this shortcut for a demonstration. In Mac Excel 2016, you can use the same shortcut as Windows, Alt ;

Transcript In this video, we’ll take a look at some shortcuts for selecting “special cells”. Excel provides a dedicated dialog box to access special groups of cells, called “Go To Special”. To access this dialog with the keyboard, type Control + G, then click the Special button (or use Alt + S) on Windows. There you’ll find a large list of options. As with the Paste Special dialog, on Windows you can use the underlined letters to select different options....

Explanation In this example, the goal is to sum the amounts shown in column C by month using the dates in column B. The article below explains two approaches. One approach is based on the SUMIFS function , which can sum numeric values based on multiple criteria. The second approach is based on the SUMPRODUCT function , which allows a more flexible solution. For convenience, both solutions use the named ranges amount (C5:C16) and date (B5:B16)....

Purpose Return value Syntax =T(value) value - The value to return as text. Using the T function The Excel T function converts numbers, dates, and the logical values TRUE and FALSE into empty strings . Text values and errors are not converted and pass through unaffected. You can use the T function to remove values that are not text. The T function takes one argument, value , which can be a cell reference, a formula result, or a hardcoded value....

Purpose Return value Syntax =TEXTAFTER(text,delimiter,[instance_num],[match_mode],[match_end],[if_not_found]) text - The text string to extract from. delimiter - The character(s) that delimit the text. instance_num - [optional] The instance of the delimiter in text. Default is 1. match_mode - [optional] Case-sensitivity. 0 = enabled, 1 = disabled. Default is 0. match_end - [optional] Treat end of text as delimiter. 0 = disabled, 1 = enabled. Default is 0. if_not_found - [optional] Value to return when no match is found....

Explanation Note: it’s important to understand that Excel deals with time as fractions of a day . So, 12:00 PM is .5, 6:00 AM is .25, 6 PM is .75, and so on. This works fine for standard time and date calculations, but in many cases you’ll want to convert times to decimal hours to make other calculations more straightforward. In the example shown on this page, we capture time in native units, but then convert to decimal hours in column E....

About This Shortcut This shortcut toggles filters on and off for a range of data. It works on tables or filtered lists, and it’s a fast way to clear filters - just turn autofilter off, then on again to reset all filters. Note: in Excel 2016, this shortcut doesn’t appear to work if slicers are on the worksheet. About This Shortcut Use this shortcut to activate a filter that already exists....

Explanation We need a way to split text at a certain marker that corresponds to a certain number of words. Excel doesn’t have a built-in function to parse text by word, so are using the SUBSTITUTE function’s “instance” argument to replace an “nth space” character with the pound sign (#), then using FIND and LEFT to discard all text after the marker. Working from the inside out, SUBSTITUTE is configured to replace the nth occurrence of a space character, where n comes from column C, the text comes from column B, and “#” is hard coded....

Explanation In this example, the goal is to create a formula that will extract unique values from a range of data in a case-sensitive way. Normally, we would use the UNIQUE function to extract unique values. However, UNIQUE is not case-sensitive so it won’t work in this situation. One way to solve this problem is to use the REDUCE function with a custom LAMBDA function, as explained below. REDUCE function The REDUCE function applies a custom LAMBDA function to each element in a given array and accumulates results to a single value....

Explanation This example uses the UNIQUE function together with the FILTER function. Working from the inside out, the FILTER function is first used to remove limit data to values associated with group A only: FILTER(B5:B16,C5:C16=E4) Notice we are picking up the value “A” directly from the header in cell E4. Insider filter the expression C5:C16=E4 returns an array of TRUE FALSE values like this: {TRUE;FALSE;TRUE;FALSE;TRUE;FALSE;TRUE;FALSE;TRUE;FALSE;TRUE;FALSE} This array is used to filter data, and the FILTER function returns another array as a result:...

Purpose Return value Syntax =VARA(number1,[number2],...) number1 - First number or reference. number2 - [optional] Second number or reference. Using the VARA function The VAR function estimates the variance for a sample of data. Variance provides a general idea of the spread of data. Unlike the VAR function, the VARA function evaluates text values and logicals in references. Text is evaluated as zero, TRUE is evaluated as 1, and FALSE is evaluated as zero....

Explanation When XLOOKUP can’t find a value in a lookup array, it returns an #N/A error. You can use the IFNA function or IFERROR function to trap this error and return a different result. However, when the result is an empty cell , XLOOKUP does not throw an error. Instead, XLOOKUP returns an empty result, which behaves like a zero. This can make it look like the lookup result has a value even though the original cell is empty....