In the example, we are using this formula: At the core, this formula is extracting characters from the URL, starting from the left, and using the FIND function to figure out how many characters to extract. First, FIND locates the “/” character in the URL, starting at the 9th character. This is the “clever” part of the formula. URLs begin with something called a “protocol” which looks like this: http:// https:// ftp:// sftp:// and so on....
Then n * 7 days is added the first of the month, to get a start date n weeks from the first of the month. In the generic form of the formula, n represents “nth”. The next step is to calculate the adjustment required to reach the final result. The adjustment is calculated using WEEKDAY and DAY: Inside WEEKDAY, the first of the month is again calculated, then 8 days are added and the value for dow (day of week) is subtracted....
Let’s take a look. Here we have a table that shows five test scores for a group of students. Let’s create a conditional formatting rule to highlight all test scores over 90 in green. To create a new conditional formatting rule, first select the cells you’d like to format. Then, select an option from the Conditional Formatting menu. When creating a new conditional format, you have two basic choices: you can start with one of the many presets that Excel includes, or you can define a new rule from scratch....
In this spreadsheet, we have a table of employees with a small amount of formatting. To get shading on every other row, we could just convert this table to a proper Excel table using Control + T, then customize the format as desired. This is a good option, especially if you want the table for other reasons as well. However, you can also apply dynamic zebra striping with conditional formatting and a formula....
To enter data in Excel, just select a cell and begin typing. You’ll see the text appear both in the cell and in the formula bar above. To tell Excel to accept the data you’ve typed, press enter. The information will be entered immediately, and the cursor will move down one cell. You can also press the tab key instead of the enter key. If you press tab, the cursor will move one cell to the right once the information has been entered....
Let’s take a look. Sometimes you need to group values into discreet categories that don’t exist in your data. For example, in this case, we have a list of employees and each employee is in one department. What if you need to group these employees into groups A and B, by department, as shown in this table? Well, you could use nested IF statements as we’ve covered in another video, and this works fine....
A good example is when you have first, last, and middle names in separate columns and you want to join these together into one name. This is referred to as “concatenation.” In this example, we have first, middle, and last names shown separately in a table. I’ll use simple concatenation to join these separate names together into a single name. The easiest way to concatenate is to use the operator for concatenation: the ampersand character....
Let’s take a look. Here we have the employee list we’ve looked at previously. This time, however, notice that the ID column has been moved into the data. It’s no longer the first column in the data, so we can’t use it to find values with VLOOKUP. This is actually a good example of a situation where you may need to use INDEX and MATCH instead of VLOOKUP if you need to match on the ID....
In the example shown, the formula in C5 is: This formula generates a working hyperlink to the first match found of the lookup value in the named range “data”. The MATCH function gets the position of the value in B5 inside the named range data, which for the lookup value “blue” is 3. This result goes into the INDEX function as row_num, with “data” as the array: This appears to return the value “blue” but in fact the INDEX function returns the address E6....
where “data” is the named range H5:J8 and “ids” is the named range H5:H8. Retrieving customer name Working from the inside out, the MATCH function is used to get a row number like this: The lookup value comes the customer id in C5, which is a mixed reference, with the column locked, so the formula can be easily copied. The lookup array is the named range ids (H5:H8), the first column in the customer table....
Where “impact” is the named range J6, and “certainty” is the named range J5 Context A risk matrix is used for risk assessment. One axis is used to assign the probability of a particular risk and the other axis is used to assign consequence or severity. A risk matrix can a useful to rank the potential impact of a particular event, decision, or risk. In the example shown, the values inside the matrix are the result of multiplying certainty by impact, on a 5-point scale....
The result is data sorted by group, then by score, with highest scores appearing first. array = B5:D14 by_array1 = D5:D14 sort_order1 = 1 The formula below will sort data by group A-Z: To extend the formula to sort next by score, in descending order, we need to add: by_array2 = C5:C14 sort_order2 = -1 With these arguments added, the complete formula is: Ascending vs. Descending Data is sorted in ascending order (A-Z) by default....
In this worksheet, we have an example we looked at previously. On the left, we have quantity-based discounts, and on the right, we have some random quantities. Let’s set up XLOOKUP to return all results in a single dynamic array. As a first step, I’m going to convert the table on the left into a proper Excel Table. This step isn’t necessary, but it will make it easy to add more discounts to the table without breaking the formula....
where data is an Excel Table in the range B5:B104. The result is a case-sensitive match for “JANET”, which appears as the second “Janet” in the list of names. The final result returned by INDEX is “Fulfillment”. Cell G5 contains a standard formula that is not case-sensitive. Note: this is an array formula and must be entered with Control + Shift + Enter, except in Excel 365. Since MATCH is not case sensitive, it returns the first match for “Janet” in row 2 of the table, even though the lookup value is “JANET” in uppercase....
Pros Allow direct comparison of multiple data series per category Can show change over time Cons More difficult to compare a single series across categories Become visually complex as categories or series are added Tips Limit data series and categories Avoid all 3d variants …Read more Author 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....
Inside INT, the value in B5 is divided by 12 and INT simply returns the integer portion of the result, discarding any decimal remainder. The result is then concatenated to a string with a single quote and space character. To get a value for inches, the MOD function is used like this: where number comes from B5 and the divisor is 12. Configured in this way, MOD returns the remainder after division....
How It Works The shift_amount can either be positive or negative. If a negative number is provided, the bits are shifted to the right instead. 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. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
The GCD function takes one or more arguments called number1, number2, number3, etc. All numeric values are expected to be integers. Numbers with decimal values will be truncated to integers before a result is calculated. Each argument can be a hardcoded constant, a cell reference, or a range that contains multiple values. The GCD function can accept up to 255 arguments total. Examples To return the greatest common divisor of the numbers 60 and 36: GCD returns the number 12, since 12 is the largest factor that goes into both numbers evenly....
The OR function is used to check more than one logical condition at the same time, up to 255 conditions, supplied as arguments. Each argument (logical1, logical2, etc.) must be an expression that returns TRUE or FALSE or a value that can be evaluated as TRUE or FALSE. The arguments provided to the OR function can be constants, cell references, arrays, or logical expressions. The purpose of the OR function is to evaluate more than one logical test at the same time and return TRUE if any result is TRUE....
Examples RAND takes no arguments: Automatic recalculation The RAND function will calculate a new result each time a worksheet is edited. To stop random numbers from being updated, copy the cells that contain RAND to the clipboard, then use Paste Special > Values to convert to a static result. To get a single random number that doesn’t change when the worksheet is calculated, enter =RAND() in the formulas bar and then press F9 to convert the formula into its result....
