where data (B5:E15) and date (C5:C15) are named ranges. The result returned by FILTER includes data in the month of July only. Here, MONTH receives the range C5:C15. Since the range contains 11 cells, MONTH returns an array with 11 results: Each result is then compared to 7, and this operation creates an array of TRUE and FALSE values, which is delivered to the FILTER function as the include argument....

Which extracts data where color is “RED”, respecting upper and lower case. The EXACT function compares two text strings in a case-sensitive manner. If the two strings are exactly the same, EXACT returns TRUE. If the two strings are not exactly the same, EXACT returns FALSE. Since we are giving EXACT a range with 11 values as the first argument, and the string “RED” as the second, EXACT returns an array with 11 results like this: Notice the position of TRUE values in this array corresponds to the rows where the color is “RED”....

This formula returns data where: account begins with “x” AND region is “east”, and month is NOT April. account begins with “x” AND region is “east”, and month is NOT April. The filtering logic of this formula (the include argument) is created by chaining together three expressions that use boolean logic on arrays in the data. The first expression uses the LEFT function to test if Account begins with “x”: The result is an array of TRUE FALSE values like this: The second expression tests if Region is “east” with the equal to (=) operator: The result is another array: The third expression uses the MONTH function with the NOT function to test if the month is not April: which yields: Note that the NOT function reverses the result from the MONTH expression....

The challenge What formula in cell I5 will correctly sum the total cups sold after 12:00 PM on Tuesday and Thursday? Relevant cells are shaded in green. For your convenience, the following named ranges are available: data = C5:G14 times = B5:B14 days = C4:G4 Download the Excel workbook, and leave your answer as a comment below. Constraints Where the expression (times>0.5) is equivalent to: This works because Excel handles times as fractional values of 1 day, where 6:00 AM is 0....

The INT function behaves a bit differently with negative values, so in this example we are using the TRUNC function. The TRUNC function simply truncates (i.e. removes) decimal values if they exist – it doesn’t do any rounding. In the example shown, cell C6 contains this formula: The TRUNC function returns the integer portion of the number which is then subtracted from the original value. The result is the decimal portion of the number....

which returns the number 3920, representing the square footage (Sq. Ft.) of the most expensive property in the list. This number is then supplied to the MATCH function as the lookup value. The lookup_array is the same range C3:C11, and the match_type is set to “exact” with 0. Based on this information, the MATCH function locates and returns the relative position of the max value in the range, which is 4 in this case....

The result is a path and filename like this: “C:\examples\workbook.xlsx”. The sheet name and the square brackets ("[ ]") that normally enclose the file name have been removed. Get workbook path The first step in this problem is to get the workbook path, which includes the workbook and worksheet name. This can be done with the CELL function like this: With the info_type argument set to “filename”, and reference set to cell A1 in the current worksheet, the result from CELL will be a full path as a text string like this: Notice the workbook name is enclosed in square brackets ("[name]")....

Let’s take a look. Let’s start off by adding Product as a Row Label. Then let’s add Total Sales as a Value. As usual, we get the sum of Total Sales. If we add Total Sales again to the Values area, we get two instances of the field, both summed. We can now change the 2nd instance to Count instead of Sum, and change the field names to something shorter and more meaningful....

Let’s take a look. This worksheet shows a class of students with five test scores in columns D through H, and an average in column I. In column J we need to add a formula that calculates a grade based on the average. This can be done with nested IF statements. When you create a nested IF, follow these steps: First, make sure the logic you need to implement is clear....

Let’s take a look. If you’re creating a pivot chart from scratch, first select a cell in the source data. Then go to the Insert tab and click the Pivot Table menu. From the menu, choose Pivot Chart. Similar to creating a pivot table, you’ll need to confirm the data source and the location. Note the title of the window - Create Pivot Table with Pivot Chart. We’ll click OK to accept the defaults....

No matter how many columns you add or delete, the number of columns in the worksheet never changes. When you insert columns, columns are pushed off the worksheet at the far right edge. When you delete columns, new columns are added to the far right edge. Let’s take a look. To insert a column in Excel, first select the column to the right of where you want the new column to be....

Dropdown lists are easy to create and use. But once you start to use dropdown menus in your spreadsheets, you’ll inevitably run into a challenge: how can you make the values in one dropdown list depend on the values in another? In other words, how can you make a dropdown list dynamic? Here are some examples: a list of cities that depends on the selected country a list of flavors that depends on type of ice cream a list of models that depends on manufacturer a list of foods that depends on category...

Let’s take a look. The easiest way to sort in Excel is to use the Sort commands on the Data tab of the ribbon, in the Sort and Filter group. Here you’ll find three basic options: two quick sort buttons—one for sorting in ascending order, and one for sorting in descending order—and a large button for custom sorting. We’ll look at custom sorting in an upcoming lesson. The ascending quick sort button sorts A-Z if the content is text, and smallest to largest for numeric content....

Here we have a simple sales summary for a team of salespeople over a period of 4 months. You can see that we have monthly totals in the bottom row and totals for each salesperson in the last column. Below the table, we have a sales target and calculations that are meant to calculate a bonus when the sales target is exceeded. As you can see, these calculations aren’t working at the moment and there are a number of NA errors in the worksheet....

Let’s take a look. In column C of our table we have a set of very large and small numbers in Number format. Let’s first copy these numbers to the rest of our table. As we can see, they don’t fit very well. Now let’s apply the Scientific format to columns D through F using the Number format menu on the ribbon. In Scientific format, all values fit easily. If we check the Format Cells dialog box, we see that “decimal places” is the only option for Scientific format....

which returns 115, the minimum of sales-cost, ignoring cases where either value is blank. Note: this is an array formula and must be entered with Control + Shift + Enter. Because each range contains 8 cells, the result of this operation is an array like this: This array acts as a filter. In cases where the value is 1, IF allows values to pass through to MIN. The actual difference values are calculated with another array operation: which generates this result: After the logical test is evaluated, the array passed into the MIN function looks like this: Notice that “difference value” for rows where either Sales or Cost are blank is now FALSE....

With a color of “red” and item of “hat” the result is $8.00 Note: This is an array formula and must be entered using Ctrl + Shift + Enter This formula uses two nested IF functions, wrapped inside MIN to return the minimum price using two criteria. Starting with 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”....

In this example, each student has five test scores in the same row, and the goal is to get the minimum score for each student. Data is supplied to MIN in a single range. The formula in I6 is: As the formula is copied down the table, MIN returns the lowest score for each student. MIN is fully automatic. If any scores are changed, MIN will automatically recalculate to show the latest....

This formula returns TRUE when either coffee or tea have an “x”. It returns FALSE if both coffee or team has an “x”, or if neither coffee or team has an “x”. With two logical criteria, XOR has a particular behavior, summarized in the table below: At each row in column E, XOR evaluates values in column C and D and returns a TRUE or FALSE result. This behavior is sometimes referred to as “exclusive OR”, meaning only one result can be true....

Fields The source data contains two fields: Date, and Sales, and both are used create the pivot table, along with Years, which appears after dates are grouped: The Date field has been grouped by Years and Quarters: After grouping, the Years field appears in the field list, and the Date field displays quarters in the form “Qtr1”, “Qtr2”, etc. Years has been added as a Column field, and Date (Quarters) has been added as a Row field....