Transcript Have you ever had to select the winners in a contest? It’s easy when you draw names out of a hat, but how would you do it in Excel? In this video we’ll show you a simple way to do it using the RAND function . Here’s a list of names that represent entries in a contest. Suppose we’d like to pick five winners. In a real drawing, we’d pick five random names out of a hat....
Transcript Moving fields around in a pivot table is fun and even addictive. Each time you move a field, you see another view of the same data, in a different orientation. This is where the idea of Pivot comes from. Let’s take a look. When you’re working with a pivot table, you can move fields from one area to another at any time. Each time you move a field, the pivot table will rebuild itself to respect the new field configuration....
Transcript Function screen tips are a great way to navigate and select function arguments. Let’s take a look. Whenever you enter or edit a formula that contains a function, the function tip window appears. The location that the function tip window appears in depends on where you’re editing the formula. If you’re editing a cell directly, it appears below the cell. If you’re working in the formula bar, it appears just below the formula bar....
Transcript Let’s look at how to use exponents in Excel. Exponents are entered using the caret symbol which is above the number 6 on the keyboard. For example, the formula for 3 squared is entered as = 3 ^ 2. Let’s take a look. Like previous examples, we have a simple worksheet that highlights several cell references. Following the instructions in the table, let’s create the formulas we need to use exponents....
Purpose Return value Syntax =LCM(number1,[number2],...) number1 - The first number. number2 - [optional] The second number. Using the LCM function The LCM function returns the least common multiple of two or more numbers. The least common multiple is the smallest positive integer that is a multiple of all numbers supplied. Least common multiple is also known as the “least common denominator”, and the “lowest common denominator”. The LCM function takes one or more arguments called number1 , number2 , number3 , etc....
Explanation The INDIRECT function tries to evaluate text as a valid reference. In this case, the sheet name is pulled from column B and concatenated with an exclamation point and the text A1: =B5&"!A1" ="Sheet1"&"!A1" ="Sheet1!A1" The INDIRECT function then coerces the text “Sheet1!A1” into a valid reference, which is passed into the SHEET function. The SHEET function then returns the current index for each sheet as listed. Explanation In this example, the goal is to generate a list of the sheet names in an Excel workbook with a formula....
An mixed reference in Excel is a reference where part of the reference is absolute and part is relative. For example, the following references have both relative and absolute components: =$A1 // column locked =A$1 // row locked =$A$1:A2 // first cell locked Mixed references can be used to set up formulas that can be copied across rows or columns without the need for manual editing. In some cases (3rd example above) they can be used to create a reference that will expand when copied....
Explanation In the worksheet shown, column B contains 12 dates. The goal is to calculate the previous working day before each date, taking into account weekends (Saturday and Sunday) and the holidays listed in column F. The formula should automatically skip weekends and any dates considered non-working days. WORKDAY function The WORKDAY function takes a date and returns the “next” working day n days in the future or the past ....
Explanation In this example, the goal is to use a formula to remove the time value from a timestamp that includes both the date and time. To solve this problem, it’s important to understand that Excel handles dates and time using a scheme in which dates are large serial numbers and times are fractional values . For example, June 1, 2000 12:00 PM is represented in Excel as the number 36678....
Purpose Return value Syntax =ROUND(number,num_digits) number - The number to round. num_digits - The place at which number should be rounded. Using the ROUND function The ROUND function rounds a number to a given number of places. ROUND rounds up when the last significant digit is 5 or greater, and rounds down when the last significant digit is less than 5. ROUND takes two arguments, number and num_digits . Number is the number to be rounded, and num_digits is the place at which the number should be rounded....
Purpose Return value Syntax =SCAN([initial_value],array,function) initial_value - [optional] The initial value of the accumulator. Default is zero. array - The array to be scanned. function - The function or custom LAMBDA to apply. Using the SCAN function The SCAN function applies a custom calculation to each element in a given array and returns an array that contains each intermediate value created during the scan. SCAN can generate running totals, running counts, and other calculations that create intermediate or incremental results....
Explanation The second sheet in the workbook, Sheet2, contains 1000 first names in the range B4:F203. The COUNTIF function takes a range and a criteria. In this case, we give COUNTIF a range equal to all rows in Sheet2. Sheet2!1:1048576 Note: an easy way to enter this range is to use the Select All button . For criteria, we use a reference to C4, which contains “John”. COUNTIF then returns 15, since there are 15 cells in Sheet2 equal to “John”....
Explanation The SORTBY function can sort values in a range with an array that doesn’t exist on the worksheet. In this example, we want to sort the values in B5:B15 by the number of characters each string contains. Working from inside out, we use the LEN function to get the length of each value: LEN(B5:B15) // get length of all strings Because we give LEN an array with 11 values, we get an array with 11 lengths:...
Purpose Return value Syntax =SUMPRODUCT(array1,[array2],...) array1 - The first array or range to multiply, then add. array2 - [optional] The second array or range to multiply, then add. Using the SUMPRODUCT function The SUMPRODUCT function multiplies arrays together and returns the sum of products. If only one array is supplied, SUMPRODUCT will simply sum the items in the array. Up to 30 ranges or arrays can be supplied. When you first encounter SUMPRODUCT, it may seem boring, complex, and even pointless....
Purpose Return value Syntax =TRIMMEAN(array,percent) array - Values to trim and average. percent - The number of data points to exclude from the calculation. Using the TRIMMEAN function The Excel TRIMMEAN function calculates mean (average) while excluding outliers. The number of data points to exclude is provided as a percentage. TRIMMEAN works by first excluding values from the top and bottom of a data set, then calculating mean. The number of data points is provided as a percentage....
A small number of Excel functions are “volatile”. Volatile functions trigger recalculation on every worksheet change, so they can have a drastic impact on worksheet performance. In workbooks that contain a small amount of data, the performance impact may not be noticeable. However, in workbooks with large data sets and lots of formulas, adding a volatile function can make the worksheet feel sluggish. In extreme cases, this can make a spreadsheet almost unusable....
Introduction What is an array formula anyway? In simple terms, an array formula is a formula that works with an array of values, rather than a single value. Array formulas can return a single result or multiple results. That sounds simple enough, and indeed many array formulas are not complex. However, because some array formulas need to be entered in a special way, and some don’t , array formulas live mostly in the geeky realm of super users....
Explanation This formula is based on the XLOOKUP function . Working from the inside out, we use the MAX function to calculate a lookup value: MAX(values) MAX is nested inside XLOOKUP, and returns a value directly as the first argument: =XLOOKUP(MAX(values),values,dates) The lookup_value is delivered by MAX The lookup_array is the named range values (C4:C15) The return_array is the named range dates (B4:B15) The match_mode is not provided and defaults to 0 (exact match) The search_mode is not provided and defaults to 1 (first to last) Without named ranges The example uses named ranges for convenience and readability....
Explanation Working from the inside out, XLOOKUP is configured to find the value in H4 in the Last name column, and return all fields. In order to support wildcards , match_mode is provided as 2: XLOOKUP(H4,D5:D15,B5:E15,2) // match Last, return all fields The lookup_value comes from cell H4 The lookup_array is the range D5:D15, which contains Last names The return_array is B5:E15, which contains all fields The not_found argument is set to “Not found” The match_mode is 2, to allow wildcards The search_mode is not provided and defaults to 1 (first to last) Since H4 contains “corr*”, XLOOKUP finds the first Last name beginning with “corr” and returns all four fields in a horizontal array :...
Explanation Note: this formula is the set-up for a formula that can extract and display data using a predefined sort order in a helper column. One example here . The core of this formula is the RANK function, which is used to generate a rank of sales values, where the highest number is ranked #1: =RANK(C5,sales) Here, RANK uses the named range “sales” (C5:C11) for convenience. By default, RANK will assign 1 to the highest value, 2 to the second highest value, and so on....
