where data is the named range B5:B14. In older versions of Excel, you can use an array formula based on the MIN, IF, and COUNTIF functions as explained below. The UNIQUE function, new in Excel 365, will return a unique list of values from a set of data. By default, this is a list of any value that occurs one or more times in the data. UNIQUE has an optional third argument called exactly_once that will limit results to values that occur once only in the source data....

which returns the nth smallest times using the value in column F for n. Note: you can also use the MIN function to get the smallest value (i.e. the 1st smallest value). 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....

Pivot Table Fields In the pivot table shown, there are three fields, Name, Date, and Sales. Name is a Row field, Sales is a Row field filtered by value, and Date is a Value field set to Max. The Date field settings are set as shown in below. Note the field has been renamed to “Latest” and set to Max: The Sales field has a Value filter applied to show the top 1 item by Latest (Date):...

In the example shown, the formula in B5 is: This formula is then copied down from B5 to B11. The result is random numbers between 1-100. Note that the RANDBETWEEN function will generate new numbers whenever a change is triggered on the worksheet. That includes any edits to the worksheet, also also simply opening the workbook. To prevent random numbers from being recalculated again, you can replace the formulas with the values last calculated:...

In the example shown, we are rounding the values in column B (which are created with the PI function) using the numbers in column B for digits. The formula in cell D6 is This tells Excel to take the value in B6 (PI) and round it to the number of digits in cell C6 (4) with a result of 3.1416 In the table, the ROUND function is used to round the same number (PI) to a decreasing number of digits, starting at 4 and moving down past zero to -1....

where “size” is the named range F4. As the formula is copied down the column, the range starting with B5 expands to include each new row, and COUNTA returns a running count of all non-blank entries in the range. The result of COUNTA is then divided by “size”, configured as a named range F4. Using a cell on the worksheet for group size allows the grouping to be changed at any time without editing the formula....

Since there are 14 columns in the chart, and the lines are somewhat hard to interpret, I though it would be interesting to plot the data in a stacked area chart. Stacked area charts aren’t so good at showing individual data points, but they work well to show overall trends. Also, unlike column charts, they can handle way many more data points without becoming cluttered. The example up top actually plots 3 more years of data, going back to 1999....

We are using the COUNTIFS function to generate a count. The first column of the summary table (F) is a date for the first of each month in 2015. To generate a total count per month, we need to supply criteria that will isolate all the issues that appear in each month. Since we have actual dates in column F, we can construct the criteria we need using the date itself, and a second date created with the EDATE function....

Note: this formula is set to “approximate match”, so row values and column values must be sorted. To get the row and column numbers, we use MATCH, configured for approximate match, by setting the 3rd argument to 1 (TRUE): In the example, MATCH will return 2 when width is 290, and 3 when height is 300. In the end, the formula reduces to: Dave Bruns Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa....

In most cases, you’ll want to take time to build clean, uncluttered charts that are easy to read. However, a quick disposable chart is a great way to check and explore data. For example, in this worksheet, I have some sample sales data from a Microsoft tutorial on pivot tables. I don’t really know much about this data, but I can learn a lot quickly with a chart. I’ll start by selecting all of the amounts, and use the keyboard shortcut Alt + F1 to build a chart....

So today I want to do some training on efficiency and entering formulas and functions in Excel, so I’m going to take you through more than 20 different tips for working faster with formulas and functions. And I’ll look at things like how to enter formulas and functions faster, how to work with the function arguments using the Function Tip Window or Screen Tip Window. We’ll look at how to use named ranges to make your formulas easier to read....

Syntax The generic syntax for AVERAGEIF looks like this: The AVERAGEIF function takes three arguments: range, criteria, and average_range. Range is the range of cells to apply a condition to. Criteria is the condition to apply, along with any logical operators that are needed. Average_range argument is optional. When average_range is not provided, AVERAGEIF will average values in the range argument. When average_range is provided, AVERAGEIF will average values in average_range....

BigNum is used in certain lookup formulas constructed in a way to find the largest value that is less than or equal to a search value. Because BigNum is an improbably large value, the lookup will find the previous numeric value. For example, you can use the following formula to find the position of the last numeric value in a column: In practice, any improbably large value will do, so you could trim the above to: The reason this works has to do with the default behavior of MATCH, which uses a binary search algorithm to find the largest value that is less than or equal to the search value in a range....

In the example shown, the formula in D5, copied down, is: Format the result as a Percentage to display with % symbol. Notes Npery should be an integer (Excel will truncate if not). The nominal_rate should be a number between 0 and 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. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts....

This formula will always add 100 to the value in A1, even when that value changes. Formulas with Functions Excel contains hundreds of pre-built formulas called functions. You can use functions on their own in formulas, or even combine multiple functions in a single formula. For example, the this formula uses only the MAX function to calculate the maximum value in the first 100 cells in column A: This formula uses the WORKDAY function to return a date 5 working days from a date in A1: In a more complex example, this formula uses two functions to count the letter “a” in A1: See this page for a full explanation of this formula....

A relative addresses will change when copied to other location in a worksheet because it describes the “offset” to another cell, rather than a fixed address. To help understand what this means, consider the phrase “the house next door to the right”. You can only understand the location of this house if you understand the starting point, because the location is described in relative terms. By default, all references in Excel formulas are relative....

Basic Example The SEARCH function is designed to look inside a text string for a specific substring. If SEARCH finds the substring, it returns a position of the substring in the text as a number. If the substring is not found, SEARCH returns a #VALUE error. For example: Note that text values entered directly into SEARCH must be enclosed in double-quotes (""). TRUE or FALSE result To force a TRUE or FALSE result, nest SEARCH inside the ISNUMBER function....

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.