Posts

In this worksheet we have some sample order data in a table called “data”. Let’s use the FILTER function to find all “blue” orders in June. To visualize how this works I’m going to set up the logic in helper columns first. Then, I’ll move that logic into the FILTER function, to make an all-in-one formula. First, we’ll test for dates in June with the MONTH function. When I provide the full set of dates to MONTH in a range, we get back a dynamic array with a month number for each date....

where “holidays” is the named range G5:G9. In the example shown, the project end date is in column C, and days is in column D. The end date represents a due date or target date, and days represents duration in work days. In column E, the WORKDAY function calculates a date by which the project must start in order to finish by the end date. In this case, we need to count backwards from the end date, so we supply days as a negative number....

where values is the named range K4:M7 and the rule is applied to all data in B4:I15. Although this is a difficult problem for the human eye, it is exactly the kind of thing Excel does very well. The solution above shows matching data in green, and matching values in blue. The colors are applied automatically with conditional formatting and will update instantly if values change. This requires two separate conditional formatting rules, each with its own formula....

Let’s take a look. Here we have a pivot table that is summarizing sales data by region. As you can see, there are four regions total: East, Midwest, South, and West. Let’s assume that there has been a reorganization, and the East and South regions are now combined into a new region called Eastern. One option is to modify the source data to replace all instances of East and South with Eastern....

Here we data that shows answers to the survey question “How do you rate your skill with Excel?” Let’s plot this data in a bar chart. A bar chart in Excel shows horizontal bars, and it’s a good option when you want to compare data with longer labels, since there is plenty of room for text to the left of the bars. You can see we have the count for each response, with a little over 1900 responses total....

Let’s take a look. After you’ve created a chart, you normally don’t need to worry about updating the chart manually. That’s because Excel will automatically update the chart when the source data changes, as long as the calculation is set to Automatic. You can verify that Calculation is set to automatic on the Formulas toolbar. Just click Calculation Options and confirm that Automatic is checked. For this chart, the source values are in the range B7:C11....

To resolve circular references, you’ll need to find the cell(s) with incorrect cell references and adjust as needed. The article below provides more information and steps to resolve. This creates a circular reference because the formula, entered in cell F7, refers to F7. This in turn throws off other formula results in D7, C11, and D11: Circular references can cause many problems (and a lot of confusion) because they may cause other formulas to return zero, or a different incorrect result....

In this worksheet, let’s generate 20 random times between 7:00 AM and 6:00 PM. To do this, we’ll use the RANDARRAY function. Now, RANDARRAY can generate both integers and decimal values. For Times, we want decimal values, because Excel times are fractional values of one day. For example, 6:00 AM is .25, 12:00 PM is .5, 6:00 PM is .75, and so on. To make it easy to change the Start and End times, I’ve put these values in cells C4 and C5....

Let’s take a look. In column B of our table we have a set of numbers that are displayed as we like, with both leading and trailing zeros. As we’ve seen in an earlier lesson, if we try to enter these numbers in cells that are using the General format, the leading and trailing zeros will be stripped as the values are entered. One solution to this problem is to first format cells in Text format....

Where start (E5) and end (E8) are named ranges. As the formula is copied down, the formula returns “x” if the date in column B is between the start and end dates. Excel dates Excel dates are just large serial numbers and can be used in any numeric calculation or comparison. This means we can simply compare a date to another date with a logical operator like greater than or equal (>=) or less than or equal (<=)....

Excel dates are serial numbers, so you can manipulate them with simple math operations. The TODAY function always returns the current date. Inside the AND function, the first logical test checks to see if the date in B5 is greater than or equal to today’s date minus 7 days: The second logical test checks if the date is less than today: when both results are TRUE, the AND function will return TRUE....

Excel Formulas Excel Functions Pivot Tables Conditional Formatting Excel Videos Excel Shortcuts Excel Video Training Other topics…

where dates is the named range B5:B16 This formula is a basic example of using the COUNTIFS function with just one condition. The named range dates is supplied as the first argument, and the date in column E is supplied as the second argument for the condition: With the date 13-Jun-2020 in cell E5, the COUNTIFS function returns 1, so the formula then simplifies to: which returns TRUE. By checking if the result from COUNTIFS is greater than zero, we also handle cases where the count is greater than 1 (i....

Where data is an Excel Table in the range B5:D16. Note: With an Excel Table, the formula will automatically update if data is added or removed from the table. SUMIFS function The SUMIFS function can sum values in ranges based on multiple criteria. The basic function signature for SUMIFS looks like this: In this case, we need to configure SUMIFS to sum values in the Amount column based on two criteria: Conditions are supplied to SUMIFS as range/criteria pairs, so each condition will be composed of two arguments....

The result is 19, which is the sum of the quantities in the range C5:C16 when the corresponding item names in B5:B16 end with “small”. Note the SUMIFS function is not case-sensitive. Wildcards Certain Excel functions like SUMIFS and SUMIF support the wildcard characters “?” (any one character) and “*” (zero or more characters), which can be used in criteria. These wildcards allow you to create criteria such as “begins with”, “ends with”, “contains 3 characters”, etc....

As the formula is copied down, you can see that Excel evaluates text values in D9:D11 as greater than the number 100. The second formula above returns TRUE when you probably expect it to return FALSE. You can see this behavior in the worksheet shown in cells D9:D11. We are comparing each value in column B to 100, and the values in these cells return TRUE because they contain text....

This formula returns a check mark character (✓) if all dates are in chronological order and an empty string ("") if not. The logic itself is quite simple, but perhaps not intuitive. Rather than check that all dates are greater than the previous date, we check if any previous date is greater than the next date. If we find even one, the dates are not in order. If we find none (zero), they are....

This formula uses the weight to find the correct “cost per kg” then calculates the final cost. In approximate match mode, the values in the first column of VLOOKUP must be sorted. VLOOKUP will return a value at the first row that is less than or equal to the lookup value. With weight as the lookup value, VLOOKUP finds and returns the right cost per kg. This cost is then multiplied by the weight to calculate the final cost....

Here are a few examples of the results that YEARFRAC calculates: In the example shown, the formula in D6 is: Rounding results Once you have the decimal value, you can round the number if you like. For example, you could round to the nearest whole number with the ROUND function: Whole years only You might also want to keep only the integer portion of the result with no fractional value, so that you are only counting whole years....

Note: you can adjust speed and quality with the gear icon in the lower right.