One of the most important skills for building formulas is creating criteria – the part of a formula that decides what to include or exclude in a calculation. However, it can be surprisingly tricky to build effective criteria because it requires a good understanding of how Excel handles data. If you’ve ever spent an afternoon troubleshooting a formula that seems like it should “just work”, you know what I mean :)...

Transcript In this video we’ll look at how to use the SUMIF function to sum cells that meet a single criteria. Let’s take a look. The SUMIF function sums cells that satisfy a single condition that you supply. It takes three arguments: range, criteria, and sum range. Note that sum range is optional. If you don’t supply a sum range, SUMIF will sum the cells in range instead. For example, if I want to sum the cells in this range that contain the number 15, I enter B7:B12 for the range, and 15 for the criteria....

Purpose Return value Syntax =IMDIV(complex_num1,complex_num2) complex_num1 - The first complex number. complex_num2 - The second complex number. Using the IMDIV function The Excel IMDIV function returns the quotient of two complex numbers. For example, given “-7+11i” and “3+5i” as input, the function returns the quotient “1+2i”, which is the result of dividing the first number by the second. =IMDIV(COMPLEX(-7,11),COMPLEX(3,5)) // returns 1+2i The quotient of two complex numbers can be visualized by uniformly stretching and rotating the coordinate system so that “1” goes to the complex number, which is the divisor....

Purpose Return value Syntax =IMLOG2(complex_num) complex_num - The complex number in the form “x+yi”. Using the IMLOG2 function The Excel IMLOG2 function returns the base-2 logarithm of a complex number. For example, given the “-4i” input, the function returns “2-2.2661800709136i” as output. =IMLOG2("0-4i") // returns 2 - 2.2661800709136i The real part of the output is equal to the logarithm (base 2) of the complex number’s magnitude. =LOG(IMABS("-4i"), 2) // returns 2 The imaginary part of the output is equal to the angle of the complex number divided by the natural logarithm of 2....

Purpose Return value Syntax =ISBLANK(value) value - The value to check. Using the ISBLANK function The ISBLANK function is a simple function to test if a cell is empty or not. If a cell is empty, ISBLANK returns TRUE. If a cell contains any value, ISBLANK returns FALSE. ISBLANK function takes one argument, value , which is a cell reference like A1. For example, if cell A1 contains nothing at all, the ISBLANK function will return TRUE:...

Explanation In the example shown, the goal is to calculate the maximum difference between the “High” values in column C and the “Low” values in column D. Because the difference between High and Low is not part of the data, the calculation must occur in the formula itself. This is a classic example of an array formula . Excel Table For convenience, all data is in an Excel Table named data in the range B5:D16....

Nominal data, also called categorical data, does not have does not have a natural sequence. Instead, the data is typically in named categories or labels without numeric significance. For example, a survey question that asks for a favorite beverage, with choices of coffee, tea, water, or milk will generate nominal data in 4 categories as in the above bar chart. When you hear “nominal”, think “named” as in “named categories”. Other examples of nominal data would be male/female, eye color, make of car, etc....

The Pie of Pie Chart is a built-in chart type in Excel. Pie charts are meant to express a “part to whole” relationship, where all pieces together represent 100%. Pie charts work best to display data with a small number of categories (2-5). The Pie of Pie Chart provides a way to add additional categories to a pie chart without generating a pie chart too complex to read. When configuring a Pie of Pie chart, Excel provides a setting that moves the smallest n slices of the pie to another smaller pie, where n can be adjusted to suit the data....

Pivot tables have a built-in feature to group dates by year, month, and quarter. In the example shown, a pivot table is used to count colors per year. This is the number of records that occur for each color in a given year. Fields The source data contains three fields: Date, Sales, and Color. Only two fields are used to create the pivot table: Date and Color. The Color field has been added as a Row field to group data by color....

Explanation In the example shown, we want to sort data in B5:D14 first by group in descending order. Here is the configuration needed: array = B5:D14 by_array1 = D5:D14 sort_order1 = 1 The formula below will sort data by group A-Z: =SORTBY(B5:D14,D5:D14,1) // sort by group only 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:...

Explanation In this example the goal is to sort the comma separated values in column B in alphabetical order. In the latest version of Excel, you can solve this problem with a formula based on TEXTSPLIT, SORT, and TEXTJOIN. In earlier versions of Excel the problem is more complicated. See below for a couple of alternatives. Current Excel In the latest version of Excel, you can use a formula based on TEXTSPLIT, SORT, TEXTJOIN and (optionally) TRIM....

A stacked area chart is a primary Excel chart type that shows data series plotted with filled areas stacked, one on top of the other. A stacked area chart can show how part to whole relationships change over time. They offer a simple presentation that is easy to interpret at a glance. Pros Simple presentation can be red at a glance Can show part to whole changes over time Cons Generally harder to add data labels since there is less white space available Area charts can imply more data than actually available A 100% Stacked Area Chart is a built-in Excel chart type, with data plotted as areas and stacked so that the cumulative area always represents 100%....

Explanation Note the purpose of this example is to how one way to “normalize” data when the order of values is random. There are many ways to approach this problem. The formula in G6 relies on the COUNTIF function to count the presence of a given class (i.e. “math”, art", etc.) in a columns C through F: =IF(COUNTIF($C6:$F6,G$5),"x","") Class names are pulled from row 5, and references are mixed to allow the formula to be copied across and down the table....

Explanation In this example, the goal is to return the sum for an entire row in an Excel worksheet. One way to do this is to use a full row reference. Full row references Excel supports full row references like this: =SUM(1:1) // sum all of row 1 =SUM(3:3) // sum all of row 2 =SUM(4:5) // sum all of rows 4 and 5 You can see how this works yourself by typing 1:1 or 3:3 into the name box (left of the formula bar ) and hitting return....

Explanation At the core, the OFFSET function delivers a range of 3 cells to SUM, which returns a summed result. The arguments for OFFSET are provided as follows: For reference we use the first cell in the data range, B5, entered as a mixed reference (column locked, row relative). For rows , we use 0, since we don’t need to change rows. For cols , we use the expression: (COLUMN()-COLUMN($O$5))*3 This part of the formula figures out how many columns from the starting reference to offset....

Explanation The heart of this formula is a basic INDEX and MATCH formula, used to translate text values into numbers as defined in a lookup table. For example, to translate “EX” to the corresponding number, we would use: =INDEX(value,MATCH("EX",code,0)) which would return 4. The twist in this problem however is that we want to translate and sum a range of text values in columns C through G to numbers. This means we need to provide more than one lookup value, and we need INDEX to return more than one result....

Explanation In this example, the goal is to sum the value of orders that have a status of “Complete” or “Pending”. This is a slightly tricky problem in Excel because the SUMIFS function is designed for conditional sums based on multiple criteria, but all criteria must be TRUE in order for a value to be included in the sum. In other words, the criteria used in the SUMIFS function are joined with AND logic, not OR logic....

Explanation In this example, the goal is to calculate the correct grade for each name in column B using the score in column C and the table in F5:G9 as a “key” to assign grades. For convenience only, the range F5:G9 has been named key . This is a classic “approximate-match” lookup problem because it is not likely that a given score will be found in the lookup table. The desired behavior is to match the largest value in the table that is less than or equal to the score ....

Explanation Working from the inside out, the IF function in this formula, which is entered as the “table_array” argument in VLOOKUP, runs a logical test on the value in column C “Years”, which represents the number of years a salesperson has been with a company. If C5 is less than 2, then table1 is returned as the value if true. If C4 is greater than 2, table2 is returned as the value if false....

Transcript In this video we’ll look at how to use the XLOOKUP function with Boolean logic. Boolean logic is an elegant way to apply multiple criteria. In this worksheet we have sample order data in a table called “data”. Let’s use the XLOOKUP function to find the first order in March where the color is red. To make things clear, I’m going to work out the logic in helper columns first....