which returns the maximum temperature on the date in H7. Note: this is an array formula and must be entered with Control + Shift + Enter Inside the IF function, logical test is entered as B5:B9391=H7. Because we’re comparing the value in H7 against a range of cells (an array), the result will be an array of results, where each item in the array is either TRUE or FALSE. The TRUE values represent dates that match H7....
In the example shown, the formula in C6 is Next, the formula subtracts 1 day to get December 23, 2015, and the result goes into the WORKDAY function as the start date, with days = 1, and the range B9:B11 provided for holidays. WORKDAY then calculates the next business day one day in the future, taking into account holidays and weekends. If you need more flexibility with weekends, you can use WORKDAY....
To solve this formula, Excel first extracts the year, month, and day values from the date in B6, then adds 1 to the year value. Next, a new date is reassembled by the DATE function, using the same day and month, and year + 1 for year. The first formula therefore returns a new date of 1/15/2011, one year later than the starting date. Once the first formula is entered, it is copied down as far as needed....
The most basic way to extend a selection is to use the shift key + any arrow key. From a single cell, this lets you add additional cells in any direction. If you begin with larger selection, shift will let you extend the edges of that selection. To change which edges of a selection you can move, you can reposition the active to a different cell to a different corner using control + period....
Note: references to B5 and C4 are mixed references to allow the formula to be copied across and down. In this snippet, the delimiter (delim) is replaced with a number of spaces equal to the total length of the string: Then the formula uses the MID function to extract the nth substring. The starting point is calculated with the code below, where N represents “nth”: The total characters extracted is equal to the length of the full text string....
Let’s take a look. To illustrate how IF works, let’s look first at a case where we need to assign a “pass” or “fail” to a group of students. We have five test scores in columns D through H, and an average in column I. To pass, students need to achieve an average of 70 or greater. Anything lower is a fail. This is a perfect application for the IF function....
where data is the name of the Excel Table in the range B5:E14. Note: see below for an equivalent formula based on INDEX and MATCH. XLOOKUP function In the worksheet shown, the formula in cell G5 is: The lookup_value is provided as 1, for reasons that become clear below. For the lookup_array, we use an expression based on Boolean logic: In the world of Boolean algebra, AND logic corresponds to multiplication (*), and OR logic corresponds to addition (+)....
where data is the named range B5:D14. Note: this is an array formula and must be entered with control + shift + enter, except in Excel 365, where dynamic array formulas are native. To get the last row used, we use the ROW function together with the MAX function like this: Because data contains more than one row, ROW returns an array of row numbers: This array goes directly to the MAX function, which returns the largest number: To get the last column, we use the COLUMN function in the same way: Since data contains three rows, COLUMN returns an array with three column numbers: and the MAX function again returns the largest number: Both results are returned directly to the ADDRESS function, which constructs a reference to the cell at row 14, column 4: If you want a relative address instead of an absolute reference, you can supply 4 for the third argument like this:...
Which returns “B”, the correct grade for a score of 88. Note that the last argument is 1 (equivalent to TRUE), which allows MATCH to perform an approximate match on values listed in ascending order. In this configuration, MATCH returns the position of the first value that is less than or equal to the lookup value. In this case, the score is 88, row 4 is returned. Once MATCH returns 4 we have: Which causes INDEX to retrieve the value at the 4th row of the range C5:C9, which is “B”....
In this worksheet, the table in B5:C9 contains quantity-based discounts. As the quantity increases, the discount also increases. Let’s set up the XLOOKUP function to calculate the correct discount for each quantity shown in E5:E11. Now, to make entering the formula a bit easier, I’m going to start by creating two named ranges. I’ll name B5:B9 quantity and I’ll name C5:C9 discount. This is an optional step, but it will eliminate the need to use absolute references inside XLOOKUP....
Like clustered column charts, clustered bar charts become visually complex as the number of categories or data series increase. They work best in situations where data points are limited. Pros Allow direct comparison of multiple data series per category Can show change over time Cons More difficult to compare a single series across categories Become visually complex as categories or series are added Tips Limit data series and categories Avoid all 3d variants...
Note: formulas are entered relative to the upper left cell in the range, which is B5 in this example. The “stop if true” option is enabled for the first two rules to prevent further processing, since the rules are mutually exclusive. 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....
The basics Why pivot tables are so powerful, and how to set up a pivot table in less than 2 minutes. What it means to refresh a pivot table, and how to refresh a pivot table when your data changes (pivot tables work like a formula, but they don’t automatically pick up new data). The goofy names you need to know to talk about (and understand) the parts of a pivot table....
where date is the named range D5:D16. The result is the number of dates in D5:D16 that are between June 1, 2022 and June 15, 2022, inclusive. Note: Excel dates are large serial numbers so, at the core, this problem is about counting numbers that fall into a specific range. In other words, SUMIFS and SUMPRODUCT don’t care about the dates, they only care about the numbers underneath the dates....
where data is the named range B5:B15 and exclude is the named range D5:D7. But this doesn’t scale very well if you have a list of many things, because you’ll have to add an additional range/criteria pair to each thing you don’t want to count. It would be a lot easier to build a list and pass in a reference to this list as part of the criteria. That’s exactly what the formula on this page does....
The result is 7, since there are seven cells in B5:B14 that do not contain error values. COUNTIF function One way to count cells that do not contain errors is to use the COUNTIF function like this: For criteria, we use the not equal to operator (<>) with #N/A. Notice both values are enclosed in double quotes. COUNTIF returns 9 since there are nine cells in B5:B15 that do not contain the #N/A error....
where “start” is the named range K5, and contains the date September 1, 2018. Note: this example uses a formula technique that will work in older versions of Excel. For a more modern solution based on the SEQUENCE function, see this example. With the layout of grid as shown, the main problem is to calculate the date in the first cell in the calendar (B6). This is done with this formula: This formula figures out the Sunday prior to the first day of the month by using the CHOOSE function to “roll back” the right number of days to the previous Sunday....
Using the example above, you can count records where the color is “red” and price is > 10 with these formulas: Caution: if field is provided to the DCOUNT function, it is only counted when non-blank and numeric. If field contains a text value, or if the field is blank, it will not be counted, even when criteria match. Criteria options The criteria can include a variety of expressions. The table below shows some examples: The criteria range for DCOUNT can include more than one row below the headers....
The database argument is a range of cells that includes field headers, field is the name or index of the field to query, and criteria is a range of cells with headers that match those in database. Field can be specified with a name or index. Using the example above, you can get the sum of all “Blue” sales with these formulas: Criteria options The criteria can include a variety of expressions, including some wildcards....
The MINA function takes multiple arguments in the form number1, number2, number3, etc. up to 255 total. Arguments can be a hardcoded constant, a cell reference, or a range, in any combination. Examples Like the MIN function, the MINA function returns the smallest number in the supplied data: MINA can be used with constants, cell references, or ranges: MINA vs. MIN The primary difference between MIN and MINA is that MINA evaluates TRUE and FALSE values as 1 and 0, and text values as zero when these values appear in a range or in a cell reference....
