Posts

Purpose Return value Syntax =PRODUCT(number1,[number2],...) number1 - The first number or range to multiply. number2 - [optional] The second number or range to multiply. Using the PRODUCT function The Excel PRODUCT function returns the product of numbers provided as arguments. Because it can accept a range of cells as an argument , PRODUCT is useful when multiplying many cells together The PRODUCT function takes multiple arguments in the form number1 , number2 , number3 , etc....

Purpose Return value Syntax =QUOTIENT(numerator,denominator) numerator - The number to be divided. denominator - The number to divide by. Using the QUOTIENT function The QUOTIENT function returns the integer portion of division without the remainder. You can use QUOTIENT to discard the remainder after division. To perform division with a remainder, use the division operator “/”. To return only the remainder, use the MOD function . QUOTIENT function takes two arguments, numerator and denominator ....

Explanation In this example, the goal is to create a list of 10 random names from a larger list of 100 names. In other words, we want to select a random subset of names from a larger list. The names to select from are in column B, starting in row 5. The formula should handle any number of names in the input list and handle the number of names to select as a variable....

About This Shortcut This shortcut will select the entire pivot table, excluding report filters. On Windows, Ctrl Shift * also works to select the entire pivot table About This Shortcut This shortcut will toggle check and uncheck the checkbox for fields listed in the pivot table field list. Use to add or remove a field from a pivot table. On Windows, you can use the spacebar to toggle items that appear in row and column label filters as well....

Explanation In this example, the goal is to return the total for an entire column in an Excel worksheet. One way to do this is to use a full column reference. Full column references Excel supports " full column " like this: =SUM(A:A) // sum all of column A =SUM(C:C) // sum all of column C =SUM(A:C) // sum all of columns A:C You can see how this works yourself by typing A:A or C:C into the name box (left of the formula bar ) and hitting return....

Transcript In this video, we’ll look at some examples where we need to use the TEXTAFTER function together with the TEXTBEFORE function. When splitting text with a formula in Excel you’ll often find that you need to use more than one function. In this first example, we have a list of codes in column B and the goal is to extract just the color into column D. Each code has 3 parts, and the delimiter is a hyphen (-)....

Explanation In this example, the goal is to use a formula to check if a specific value exists in a range. The easiest way to do this is to use the COUNTIF function to count occurrences of a value in a range, then use the count to create a final result. COUNTIF function The COUNTIF function counts cells that meet the supplied criteria. The generic syntax looks like this: =COUNTIF(range,criteria) Range is the range of cells to test, and criteria is a condition that should be tested....

Purpose Return value Syntax =VAR(number1,[number2],...) number1 - First number or reference. number2 - [optional] Second number or reference. Using the VAR function The VAR function estimates the variance for a sample of data. Variance provides a general idea of the spread of data. In the example shown, the formula in F4 is: =VAR(C5:C10) VAR ignores text and logicals passed into as cell references. For example, VAR will ignore FALSE when it appears in a range like A1:A10....

Purpose Return value Syntax =XLOOKUP(lookup,lookup_array,return_array,[if_not_found],[match_mode],[search_mode]) lookup - The lookup value. lookup_array - The array or range to search. return_array - The array or range to return. if_not_found - [optional] Value to return if no match found. match_mode - [optional] 0 = exact match (default), -1 = exact match or next smallest, 1 = exact match or next larger, 2 = wildcard match, 3 = regex match. search_mode - [optional] 1 = search from first (default), -1 = search from last, 2 = binary search ascending, -2 = binary search descending....

Explanation The goal in this example is to calculate total wins, losses, and ties for each team listed in column G. The problem is complicated somewhat by the fact that a team can appear in either column B or C, so we need to take this into account when calculating wins and losses. For convenience and readability only, the formula uses the following named ranges : team1 (B5:B14), team2 (C5:C14), score1 (D5:D14), and score2 (E5:E14)....

Explanation In this example, the goal is to clean up telephone numbers with inconsistent formatting and then reformat the numbers in the same way. In practice, this means we need to start by removing the extra non-numeric characters, including spaces, dashes, periods, and parentheses. Once these characters are removed, we can use Excel’s number format system to format the numbers consistently. In the worksheet shown, the formula used to learn the numbers looks like this:...

Explanation In this example, the goal is to join two one-dimensional ranges together horizontally. This can be done with the CHOOSE function and array constant . The CHOOSE function The CHOOSE function is used to select arbitrary values by numeric position. CHOOSE is a flexible function and accepts a list of text values, numbers, cell references, in any combination. For example, if we have the colors “red”, “blue”, and “green”, we can use CHOOSE like this:...

Explanation Note: this formula depends on values in the Time column (C), and values in the Start and End columns (F, G) being valid Excel times . The data is in an Excel table called table . By creating a proper Excel table , we make the formulas easier to read and write. In addition, any new data that is added to the table will be automatically picked up by the formulas in columns H and I....

Explanation The goal in this example is to count cells in a range that do not contain a given number of strings. The cells to evaluate are in the named range data (B5:B14) and the strings to exclude are listed in the named range exclude (D5:D7). If your needs are simple, you can use the COUNTIFS function to solve this problem. In more complicated scenarios, you can use the SUMPRODUCT function in combination with ISNUMBER and SEARCH....

Explanation B4 is the cell we’re counting words in, and C4 contains the substring (word or any substring) you are counting. SUBSTITUTE removes the substring from the original text and LEN calculates the length of the text without the substring. This number is then subtracted from the length of the original text. The result is the number of characters that were removed by SUBSTITUTE. Finally, the number of characters removed is divided by the length of the substring....

Transcript In this video we’ll look at how to create a dynamic reference to a named range using the INDIRECT function . Let’s take a look. Here we have a simple table that summarizes sales by salesperson over a four-month period. What we’re going to do is use the INDIRECT function to make a dynamic reference to a named range that corresponds to each month. This is a useful technique when you want to provide an interactive way to change a reference on a worksheet....

About This Shortcut Normally drag and drop only works in the same worksheet. If you want to drag a selection to a different worksheet, use this shortcut. About This Shortcut Normally, when you click and drag worksheet tabs at the bottom of a workbook, you are reordering the tabs. Use this shortcut when you want to duplicate a worksheet. The cursor will change to a small plus (+) sign inside a document....

Explanation In this example, the goal is to create a due date based on category, where each category has a different number of days allocated to complete a given task, issue, project, etc. The amount of time available to resolve each category is shown in column H, and categories is the named range G5:H7. The named range is for convenience and readability only. You can also use the absolute reference $G$5:$H$7....

Explanation The Effective Annual Rate (EAR) is the interest rate after factoring in compounding. In other words, the EAR is the rate actually earned due to the effect of compounding more frequently than once a year (annually). The EFFECT function calculates the effective annual interest rate based on the nominal annual interest rate, and the number of compounding periods per year. To demonstrate how this works, the table shown in the example is set up with various compounding periods in column C....

About This Shortcut Use this keyboard shortcut to evaluate parts of a formula. This is not really a keyboard shortcut per se, but rather just a trick you can use to find out how Excel evaluates parts of a formula as it calculated. This is a great way to check a formula to find out how it works, or to debug a formula when it isn’t working properly. See this video for a demonstration of F9 to evaluate parts of a formula ....