Download the practice worksheet here: If this OR that Practice.xlsx This is part of a 3-part series on Excel formulas. Video 1: 5 ways to use VLOOKUP Video 2: 23 tips to work faster with formulas today Video 3: How to learn Excel formulas and functions Sample video lessons: How to use IF with AND and OR <- this video How to create 3d references
Explanation The WEEKDAY function returns a number, 1-7, that corresponds to particular days of the week. By default, WEEKDAY assumes a Sunday-based week, and assigns 1 to Sunday, 2 to Monday, and so on, with 7 assigned to Saturday. In this case, we only want to take action if the date in question is Monday. To test, we use this expression inside the IF function: WEEKDAY(B5)=2 If the logical expression returns TRUE, we know the date is a Monday, so we subtract 3 to “roll back” to Friday....
Explanation Consider a simple dynamic reference to Sheet2 using the INDIRECT in a formula like this: =INDIRECT($B$5&"!"&"A1") If we change the sheet name in B5 to another (valid) name, INDIRECT will return a reference to A1 in the new sheet. However, if we copy this formula down the column, the reference to A1 won’t change, because “A1” is hardcoded as text. To solve this problem, we use the CELL function to generate a text reference from a regular cell reference:...
Purpose Return value Syntax =ISREF(value) value - The value to check. Using the ISREF function The ISREF function returns TRUE to test for a reference in a formula. The ISREF function takes one argument , value , to test. If value is a valid cell reference, range , or named range , ISREF returns TRUE. If value is not a reference, ISREF returns FALSE. ISREF does not evaluate the contents of a reference, just the reference itself....
Explanation The LAMBDA function can be used to create reusable, custom functions in Excel without VBA or macros. The first step in creating a LAMBDA function is to verify the formula logic needed in a standard Excel formula. In this example, the base formula is: =LEN(TRIM(B5))-LEN(SUBSTITUTE(B5," ",""))+(LEN(TRIM(B5))>0) This formula uses three built-in functions: SUBSTITUTE , TRIM , and LEN . Here is the formula in action below. You can read a detailed explanation here ....
Explanation This formula uses the LOOKUP function to find and retrieve the last matching file name. The lookup value is 2, and the lookup_vector is created with this: 1/(ISNUMBER(FIND(G6,files))) Inside this snippet, the FIND function looks for the value in G6 inside the named range “files” (B5:B11). The result is an array like this: {1;#VALUE!;1;1;#VALUE!;#VALUE!;1} Here, the number 1 represents a match, and the #VALUE error represents a non-matching file name....
Explanation In this example, the goal is to look up a number with a certain amount of allowed tolerance, defined as n . In other words, with a given lookup number we are trying to find a number in a set of data that is ± n . In the worksheet shown, the number to find is in cell G4 and the number used for n is in G5. All data is in an Excel Table in the range B5:D15 named “data”....
Explanation This formula uses the EXACT formula to compare a range of cells to a single value: =EXACT(B5:F5,B5) Because we give EXACT a range of values in the first argument, we get back an array result containing TRUE FALSE values: {TRUE,FALSE,TRUE,TRUE,TRUE} This array goes into the AND function, which returns TRUE only if all values in the array are TRUE. Ignore empty cells To ignore empty cells, but still treat non-empty cells in a case-sensitive manner, you can use a version of the formula based on SUMPRODUCT:...
A named range is one or more cells that have been given a name. Using named ranges can make formulas easier to read and understand. They also provide simple navigation via the Name Box . In the example, the formula in F6 is: =MAX(sales) where “sales” is the named range C4:C10. Other examples of formulas that use this same named range are: =MIN(sales) =AVERAGE(sales) =SUM(sales) Note: named ranges are absolute references by default....
Purpose Return value Syntax =NOW() Using the NOW function NOW takes no parameters but requires empty parentheses. The value returned by NOW will continually update each time the worksheet is updated, for example, each time a value is entered or changed. Use F9 to force the worksheet to recalculate and update the value. The value returned by the NOW function is a standard Excel date , including a fractional value for time ....
Explanation The CHOOSE function does most of the work in this formula. Choose takes a single numeric value as its first argument (index_number), and uses this number to select and return one of the values provides as subsequent arguments, based on their numeric index. In this case, we are providing four numbers as options: 25,50,75,100, so we need to give CHOOSE a number between 1 and 4. To generate this number, we use RANDBETWEEN, a function that returns a random integer based on a lower and upper bound....
Explanation At the core, this formula uses the MID function to extract each character of a text string in reverse order. The starting character is given as a list of numbers in descending order hard-coded as array constant: MID(B5,{10,9,8,7,6,5,4,3,2,1},1) The text argument comes B5, and 1 is specified for the number of characters to extract. With the string “ABCD” in B5, the output from MID is an array that looks like this:...
Explanation The ROUNDUP function rounds a number up to a given number of places. The number of places is controlled by the number of digits provided in the second argument ( num_digits ). For example, these formulas round the number 5.13 up to 1 and zero places: =ROUNDUP(5.13,1) // returns 5.2 =ROUNDUP(5.13,0) // returns 6 In the example, the formula in cell D7 is =ROUNDUP(B7,C7) This tells Excel to take the value in B7 (PI) and round it to the number of digits in cell C7 (3) with a result of 3....
Transcript In this video, we’ll look at shortcuts for navigating dialog boxes on a Mac. Excel has a large number dialog boxes that you will use frequently. Some examples include Format Cells, find and replace, spelling, paste special, go to special, and many more. On a Mac, you have a more limited ability to select controls in dialog boxes, but it is possible to drive these dialogs entirely from the keyboard....
Explanation If you are new to the SUMIFS function, you can find a basic overview with many examples here . The SUMIFS function is designed to sum numeric values based on one or more criteria. In specific cases however, you may be able to use SUMIFS to “look up” a numeric value that meets required criteria. The main reasons to do this are simplicity and speed. In the example shown, we have quarterly sales data for four regions....
Explanation In this example, the goal is to calculate durations in “days” starting from the start date and time in cell G5 and ending at the dates and times shown in column B. The twist is that we want to classify the durations using the custom labels shown in column E, starting with “Day 0” for the first 24 hours and ending at “Day 3+” for durations greater than 72 hours....
About This Shortcut This shortcut enables and disables “End mode”. In End mode, arrow keys move you farther across the worksheet. In End mode, pressing an arrow key will take the cursor to the first nonblank or blank cell in the same row or column. About This Shortcut To navigate back to a hyperlink, after clicking the link, you can use a two step-process: (1) use the shortcut control + G to bring up the Go To dialog box, (2) press Enter to go back to the hyperlink....
Explanation In this example, the goal is to configure VLOOKUP to perform a lookup in a table where the first column contains numbers entered as text, and the lookup value is a true number. This mismatch between numbers and text will cause VLOOKUP to return an #N/A error. Typically, the lookup column in the table contains values that look like numbers , but are in fact numbers entered as text ....
Explanation In this example, the goal create a formula that will return TRUE when a volunteer has successfully logged the required number of hours in each of the three categories. Two requirements must be satisfied: A volunteer should have at least 5 hours in each of the three categories. A volunteer needs to have at least 15 hours in total. Both requirements are evaluated inside a single AND function in a formula like this:...
A wildcard is a special character that lets you perform “fuzzy” matching on text in your Excel formulas. For example, this formula: =COUNTIF(B5:B11,"*combo") counts all cells in the range B5:B11 that end with the text “combo”. And this formula: =COUNTIF(A1:A100,"???") Counts all cells in A1:A100 that contain exactly 3 characters. Available wildcards Excel has 3 wildcards you can use in your formulas: Asterisk (*) - zero or more characters Question mark (?...
