Here we have a list of 36 people. Let’s say we want to randomly assign each person to a team of 4 people so that we have a total of 9 teams with 4 people in each. I’m going to solve this problem in small steps, with helper columns, then bring things together in the end. This is a great way to solve more complicated problems in Excel. I’ll start with an Excel Table, to make the formulas very fast to enter....

where 70 represents the passing score for all subjects. The result in I5 is 3, in I6 is 5, and in I7 is 6. It’s a good idea to use COUNTIF alone as you start this formula, to make sure you are getting the results you expect before adding more logic. The number returned by COUNTIF is then checked against 4 with the greater than or equal to operator (>=), and the expression returns TRUE or FALSE for the logical test inside the IF function....

which rounds the value in B6 to the nearest whole dollar, then subtracts .01. For rounding, we use the ROUND function, with the num_digits argument set to zero (0) for no decimal places: The ROUND function with a zero will round to the nearest whole dollar. Once rounded, the formula simply subtracts 0.01 to get a .99 value. The formula in C6, copied down, is: With the value in B6 of 63....

where D5:D11 is formatted with the “Wingdings” font. which displays a checkmark when the font is “Wingdings”. When a cell contains any other value, IF returns an empty string (""). Note the IF functions is not case-sensitive. Important: cells in column D must be formatted with the “Wingdings” font for this to work. Excel’s CODE and CHAR functions allow you to work with ASCII codes for characters directly in a formula....

Stacked column charts can show change over time because it’s easy to compare total column lengths. However, except for the first series of data (next to the x-axis) and total bar length, it’s difficult to compare the relative size of the components that make up each bar. As categories or data series are added, stacked column charts quickly become complicated. Pros Multiple categories and data series in compact space Can show change over time...

The data used to plot this chart is shown below: See also: Same data in a clustered column chart. How to make this chart At this point, you can finalize the chart by setting a title, and adjusting the overall chart size and font size for text objects. 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....

This formula sums the quantity in column C when the text in column B contains “hoodie”. Note that SUMIFS is not case-sensitive. However, see below for a case-sensitive option. Note: this example embeds wildcards together with the search substring to keep things simple. However, you can also use wildcards with text in another cell, as explained in this more advanced example. Wildcards Excel functions like SUMIF and SUMIFS support the wildcard characters “?...

To calculate total hours worked, cell E5 contains: This is simply end time minus start time, multiplied by 24 to convert to decimal hours. If you need to calculate elapsed time that crosses midnight, see this page for options and general explanation. To calculate regular time, F5 contains: This is an example of using MIN instead of IF to simplify. The result is the smaller of two options: 8 hours, or regular time as calculated above....

ROWS counts the number of rows in any supplied range and returns a number as a result. For example, if we provide all of column A in a range, Excel returns 1,048,576 the total number of rows in an Excel worksheet. To count columns in a range, see the COLUMNS function. 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....

where months (C4:E4) and names (B5:B13), and data (C5:E13) are named ranges. Note: XLOOKUP performs an exact match by default, so match mode is not set. Working from the inside out, the inner XLOOKUP is used to retrieve all data for “Frantz”: XLOOKUP finds “Frantz” in the named range names (B5:B13). Frantz appears in the fifth row, so XLOOKUP returns the fifth row of data (C5:E13). The result is an array representing a single row of data for Frantz, containing 3 months of sales: This array is returned directly to the outer XLOOKUP as the return_array: The outer XLOOKUP finds the value in H5 (“Mar”) inside the named range months (C4:E4)....

where “tax_table” is the named range C5:D8. Note: this formula determines a single tax rate. To calculate tax based in a progressive system where income is taxed across multiple brackets at different rates, see this example. Lookup value itself comes from G4 Table array is the named range tax_table (C5:D8) Column index number is 2, since tax rates are in the second column of C5:D8 Range_lookup argument is set to TRUE = approximate match...

If you are calculating a date n days in the future, you can add days directly as in the first two formulas. If you want to count by months, you can use the EDATE function, which returns the same date n months in the future or past. If you need an expiration date at the end month, use the EOMONTH function, which returns the last day of the month, n months in the future or past....

Extracting feet To extract feet and convert them to inches, we use the following snippet: Working from the inside out, the FIND function is used to locate the position of the single quote (’) in the string: We then subtract 1 (-1) and feed the result into the LEFT function as the number of characters to extract from the left: For cell B5, LEFT returns “8,” which is then multiplied by 12 to get 96 inches....

where data is the named range B5:B15. The result is 5, since there are five cells in B5:B15 that do not 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: Note this is an exact match. To be included in the count, a cell must contain “apple” and only “apple”....

The result is 9 because there are nine values in the range B5:B15 that match values in D5:D15 in corresponding rows. Note: this formula counts matches in corresponding rows. To count all matches in two ranges, regardless of row, see this example. SUMPRODUCT function The SUMPRODUCT function is a versatile function that handles array operations natively without any special array syntax. Its behavior is simple: it multiplies, then sums the product of arrays....

where data is the named range B4:D15. The result is 5, the number of rows that contain the number 19. Note: this is an array formula and must be entered with control shift enter in Legacy Excel. See below for a formula that performs the same task with the newer BYROW function. Background study The double negative in Excel formulas Boolean operations in array formulas Boolean algebra in Excel...

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. So, if a substring is 5 characters long, and there are 10 characters missing after it’s been removed from the original text, we know the substring appeared twice in the original text....

The result is 7, since there are 7 rows visible out of 10 rows total. Count with SUBTOTAL Following the example in the worksheet above, to count the number of non-blank rows visible when a filter is active, use a formula like this: The first argument, function_num, specifies count as the operation to be performed. SUBTOTAL ignores the 3 rows hidden by the filter and returns 7 as a result, since there are 7 rows visible....

Availability Dynamic arrays and the new functions below are only available Excel 365 and Excel 2021. Excel 2019 and earlier do not offer dynamic array formulas. For convenience, I’ll use “Dynamic Excel” (Excel 365) and “Legacy Excel” (2019 or earlier) to differentiate versions below. New functions As part of the dynamic array update, Excel now includes 8 new functions which directly leverage dynamic arrays to solve problems that are traditionally hard to solve with conventional formulas....

Using Degrees To supply an angle to COT in degrees, multiply the angle by PI()/180 or use the RADIANS function to convert to radians. For example, to get the COT of 60 degrees, you can use either formula below: Explanation The graph of COT, shown above, visualizes the output of the function for angles from 0 to a full rotation. The function has vertical asymptotes at the points 0, π, and 2π where the output of the function diverges to infinity....