The ISLOGICAL function takes one argument, value, which can be a cell reference, a formula, or a hardcoded value. When value is TRUE or FALSE, the ISLOGICAL function will return TRUE. If value is any other value, ISLOGICAL will return FALSE. Examples The ISLOGICAL function returns TRUE if value is TRUE or FALSE: If value is a formula, ISLOGICAL checks the result of the formula: Note that 1 and 0 (zero) are not evaluated as TRUE and FALSE....
The ISNUMBER function takes one argument, value, which can be a cell reference, a formula, or a hardcoded value. Typically, value is entered as a cell reference like A1. When value is a number, the ISNUMBER function will return TRUE. Otherwise, ISNUMBER will return FALSE. Examples The ISNUMBER function returns TRUE if value is numeric: If cell A1 contains the number 100, ISNUMBER returns TRUE: If a cell contains a formula, ISNUMBER checks the result of the formula: Note: the ampersand (&) is the concatenation operator in Excel....
The MMULT function takes two arguments, array1 and array2, both of which are required. The column count of array1 must equal the row count of array2. For example, you can multiply a 2 x 3 array by a 3 x 2 array to return a 2 x 2 array result. The MMULT function will return a #VALUE! error if array1 columns do not equal array2 rows. Note: In Excel 365, which supports dynamic arrays, MMULT spills multiple values on the worksheet....
Excel offers many types of number formatting. 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.
Note: In Mac Excel 365, the Control key can be substituted for the Command key. Before Mac Excel 2016, this shortcut was Control + I. 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.
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.
TEXTAFTER takes six arguments; only the first two are required. The first argument, text, is the text string to process. The second argument, delimiter is the substring to use as a delimiter when extracting text. Both text and delimiter are required. The third argument, instance_num, is an integer that represents the nth instance of the delimiter in text (i.e. to extract the text after the second instance, use 2 for instance_num)....
Examples The TYPE function returns a numeric code: If TYPE is given an array constant, or a range, the result is 64: TYPE returns 16 for errors: Notes You can’t use TYPE to test for a formula, because TYPE evaluates the result. Excel dates and times are numbers, and therefore return 1. 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....
The output contains only rows from the source data where all three columns have a value. To do this, we use three boolean expressions operating on arrays. The first expression tests for blank names: The not operator (<>) with an empty string ("") translates to “not empty”. For each cell in the range B5:B15, the result will be either TRUE or FALSE, where TRUE means “not empty” and FALSE means “empty”....
Note: this worksheet has two named ranges: bid (C5:C12), and company (B5:B12), used for convenience and readability. In this case, the rank simply comes from column E. Retrieve associated values To retrieve the name of the company associated with smallest bids, we use INDEX and MATCH. The formula in G7 is: Here, the value in column F is used as the lookup value inside MATCH, with the named range bid (C5:C12) for lookup_array, and match type set to zero to force exact match....
For example, if you have dates in the range B4:G11, and want to highlight cells that occur in the next 30 days, select the range and create a new CF rule that uses this formula: Note: it’s important that CF formulas be entered relative to the “active cell” in the selection, which is assumed to be B4 in this case. Once you save the rule, you’ll see the dates occurring in the next 30 days highlighted....
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.
Once you have data in a table, it’s easy to remove duplicates. Let’s go through some examples. In this first worksheet, I have a table with a list of US cities and states. Some of the entries are duplicates. You’ll find the Remove Duplicates command on the Data tab of the ribbon, in the Data Tools group. Remove Duplicates will display a list of column headers below, with buttons at the top to quickly uncheck and check all columns....
Sometimes you might be working on a formula that’s broken or unfinished and Excel won’t let you enter it as is. In this example, I’m working with an INDEX MATCH formula that has a problem. Each time I try to enter the formula, Excel complains. This isn’t such a big problem if you have plenty of time, but maybe you need to stop work and save the worksheet for another day....
When you use a formula to apply conditional formatting, it can be tricky to set the formula up correctly. The dialog used to create and edit the formula doesn’t provide all the nice features that the formula bar does. You can’t easy check references, you don’t get function screen tips, and the window is too small. An easy workaround is to test the formula on the worksheet directly. For example, if we want to highlight odd numbers in this set of random values, we can just add the formula in an empty area, and point back at the values, starting with the first value....
The status bar at the bottom of the Excel window provides real-time data about currently selected cells and can give you a lot of good information without the need to write any formulas. Here we have test score data for a group of students. Let’s use the status bar to look at the information shown in column J. First note that the status bar can be customized to display different information....
where “names” is the named range C4:E7, and “groups” is the named range B4:B7. The formula returns the group that each name belongs to. Note: this is an array formula and must be entered with control shift enter. where names is the named range C4:E7. This generates a TRUE / FALSE result for every value in the data, and the double negative coerces the TRUE and FALSE values to 1 and 0 to yield an array like this: This array is 4 rows by 3 columns, matching the structure of “names”....
How to make this chart From this point, you can set the chart title and move the legend. 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.
where “code” is the named range B5:B12. Note: this is an array formula and must be entered with control + shift + enter. Here, each value in the named range “code” is evaluated with the logical test “first letter is not N”. The result is an array or TRUE and FALSE values like this: This array is fed into the MATCH function as the lookup array. The lookup value is TRUE, and match type is set to zero to force an exact match....
With a color of “red” and item of “hat” the result is $11.00 Note: This is an array formula and must be entered using Ctrl + Shift + entered This formula uses two nested IF functions, wrapped inside MAX to return the maximum price with two criteria. Starting with a logical test of the first IF statement, color = G6, the values in the named range “color” (B6:B14) are checked against the value in cell G6, “red”....
