Where scores is the named range C6:C13. RANK has two modes of operation: ranking values where the largest value is #1 (order = 0), and ranking values where the lowest value is #1 (order = 1). In this case, we are ranking test scores, so the highest value should rank #1, so we omit the order argument, which defaults to zero: The following formula, which includes order set to zero, is equivalent:...
Context - sample data The workbook contains 4 worksheets total. Sheet1, Sheet2, and Sheet3 each contain 1000 random first names that look like this: Working from the inside out, this expression is used to build a full sheet reference: The single quotes are added to allow sheet names with spaces, and the exclamation mark is a standard syntax for ranges that include a sheet name. The text “1:1048576” is a range that includes every row in the worksheet....
When working with financial data and creating accounting reports, one common number format that needs to be used is to show negative numbers in brackets (parentheses). While the default setting in Excel is to show negative numbers with a minus sign, it’s quite easy to change the format to show negative numbers in brackets/parenthesis. In this tutorial, I will show you different ways to show negative numbers in brackets. I’ll also cover all the other formatting options that are available to you (such as adding or removing the – or changing the color of the negative or positive numbers)...
If you have an Excel workbook with many worksheets, there is no easy way to split each of these sheets into separate Excel files and save separately. This could be needed when you sheets for different months or regions or products/clients and you want to quickly get a separate workbook for each sheet (as an Excel file or as PDFs). While there is a manual way to split sheets into separate workbooks and then save it, it’s inefficient and error-prone....
The result is $30.45, the sum of Shampoo ($9.50), Shaving Cream (11.95), and Shaving Soap ($9.00). Note the SUMIF function is not case-sensitive. 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. These wildcards allow you to create criteria such as “begins with”, “ends with”, “contains 3 characters”, etc. as shown in the table below: Note that wildcards are enclosed in double quotes ("") when they appear in criteria....
where “times” is the named range C5:C14. This results in an array like this: The second expression is a logical test for all times greater than 30 minutes: This creates an array of TRUE FALSE values: Inside SUMPRODUCT, these two arrays are multiplied together to create this array: Notice negative values in the first array are now zeros. During multiplication, the TRUE FALSE values are converted to 1 and zero, so FALSE values “cancel out” times that are not greater than 30 min....
Syntax SUMIFS(Sum Range, Range 1, Criteria 1, Range 2, Criteria 2,…) Sum Range (required) – This is the range of numbers to sum.Range 1 (required) – This is the range to which the first criteria is applied.Criteria 1 (required) – This is the criteria Range 1 has to satisfy to be included in the sum.Range 2, Range 3,… (optional) – These are more criteria ranges that can be used.Criteria 2, Criteria 3,… (optional) – These are more criteria to which the associated range has to satisfy to be included in the sum....
This formula returns a count of rows where the value in column one is A or B and the value in column two is X, Y, or Z. ISNUMBER + MATCH Working from the inside out, each condition is applied with a separate ISNUMBER + MATCH expression. To generate a count of rows in column one where the value is A or B we use the following code: Notice the values “A” and “B” are supplied as an array constant and match_type is set to zero for an exact match....
One of the new functions that comes with the dynamic array version of Excel is SEQUENCE. The SEQUENCE function lets you generate numeric sequences, which can be used for dates, times, and more. The SEQUENCE function takes four arguments. The first argument, rows controls how many rows SEQUENCE returns. This argument is required. The second argument, columns, controls the number of columns returned by SEQUENCE. Columns is optional and defaults to 1....
There is a similar function in that also does the same – the UCase function. The VBA UCase function takes a string as the input and converts all the lower case characters into upper case. Syntax of the VBA UCASE Function Below is the syntax of the VBA UCase function ‘String’ is the text in which you want to convert all the lowercase to uppercase. You can use a text string, a range reference that contains the text string, or a variable that has the text string....
Which returns a match on the value in G5, with all 4 fields in a different sequence. The lookup_value comes from cell G5 The lookup_array is E5:E15 (codes) The return_array is B5:E15 (all fields) The match_mode is is not provided and defaults to 1 (exact match) The search_mode is not provided and defaults to 1 (first to last) The result is a match on “AX-160”, returned as an array of all four fields in the original order: This result is delivered directly to the second (outer) XLOOKUP as the return array argument....
where dob (H5), sign (B5:B16), symbol (D5:D16), start (E5:E15), and end (F5:F16) are named ranges. Using the lookup table as shown is somewhat challenging. See below for an alternative self-contained formula. Using the lookup table as shown is somewhat challenging. This article describes two ways to solve the problem. The first way uses INDEX and MATCH with the table as shown. The second way is a VLOOKUP formula with a hard-coded array constant....
Context Sometimes, when you use functions like SMALL, LARGE, or RANK to rank highest or lowest values, you end up with ties, because the data contains duplicates. One way to break ties like this is to add a helper column with values that have been adjusted, then rank those values instead of the originals. In this example, the logic used to adjust values is random - the first duplicate value will “win”, but you can adjust the formula to use logic that fits your particular situation and use case....
where start (H4), end (H5), age (D5:D16), and joined (E5:E16) are named ranges. At each new row, the formula returns the count of all rows between start and end dates (inclusive) that also fall into the age ranges shown in column G. Note: this is a somewhat advanced formula that demonstrates how the criteria for COUNTIFS can be extended, and quite a lot of the complexity comes from having to parse the age ranges in column G....
Matching any time greater than or equal to the time E5 (5:00). The second range/criteria pair is: Matching any time less than the time in E6 (6:30). With hard-coded values The formula in E7 could be written with hard-coded time values as follows: Excel translates a string like “5:00” into the correct numeric time value. With the TIME function The formula in E7 could be written with the TIME function like this: The TIME function provides a simple way to assemble a valid time using discreet hour, minute, and second values....
In the example above, we are using: The result of all this calculation is a list of items, where there is one item per cell in the range, and each item a number based on the calculation above. In other words, we have a list of word counts, with one word count per cell. SUMPRODUCT then sums this list and returns a total for all cells in the range. Note that the formula inside SUMPRODUCT will return 1 even if a cell is empty....
In the example shown, the data validation applied to C5:C7 is: The UPPER function changes text values to uppercase, and the EXACT function performs a case-sensitive comparison. The AND function takes multiple arguments (logical conditions) and returns TRUE only when all arguments return TRUE. The first logical condition compares the value input by the user to an uppercase version of the same value: The second logical condition tests that input to C5 is actually text If both conditions are TRUE, the AND function returns TRUE and input passes validation....
In this case, the MOD function is used to perform a modulo operation, which returns the remainder after division. The formula used to validate input is: The value in C5 is 500. The MOD function divides 500 by 100 and gets 5, with a remainder of zero. Since 0 = 0, The rule returns TRUE and the data validation passes: If a user enters, say, 550, the remainder is 50, and validation fails: Note: Cell references in data validation formulas are relative to the upper left cell in the range selected when the validation rule is defined, in this case C5....
which resolves to the range $A$2:$A$10. Note: this formula is meant to define a named range that can be used in other formulas. The INDEX function returns the value at a given position in a range or array. You can use INDEX to retrieve individual values or entire rows and columns in a range. What makes INDEX especially useful for dynamic named ranges is that it actually returns a reference....
Which returns the sum of Amounts for three tables named “West”, “Central”, and “East”. In the example shown, the formulas in L5:L7 behave like these simpler formulas: However, instead of hardcoding the table into each SUM formula, the table names are listed in column K, and the formulas in column L use concatenation to assemble a reference to each table. This allows the same formula to be used in L5:L7....
