Errors 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.
Using the example above, you can get the minimum value from the field “Price” for records where the color is “Red” and Quantity is > 2 with these formulas: Criteria options The criteria can include a variety of expressions, including some wildcards. The table below shows some examples: Note: it appears support for wildcards is not as extensive as with other functions like COUNTIFS, SUMIFS, MATCH, etc. For example, the pattern ?...
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. Using the example above, you can get the standard deviation of heights for the group “Fox” with either of these formulas: The standard deviation for all heights in C8:C13 is calculated in F5 with the STDEV....
All three formulas return FALSE if the value in A1 is not greater than 65. The FALSE function is classified as a “compatibility function”, needed only for compatibility with other spreadsheet applications. There is no need to use the FALSE function if you are creating a spreadsheet in Excel. Note that logical expressions will automatically generate TRUE and FALSE results. For example, the formula below will automatically return TRUE if the value in A1 is greater than 90 and FALSE if not: To test a condition and return different results based on whether the results are TRUE or FALSE, see the examples on this page....
Most Excel functions require specific inputs, called function arguments. For example, the DATE function creates a date value based on three arguments: year, month, and day: Some functions don’t require any arguments. For example, the TODAY function returns the current date without any input: Formulas with multiple functions Formulas can include multiple functions and/or constants. For example, the following formula returns a date 30 days from today: The formula below uses three different functions to calculate current age based on a birthdate in A1: See this page for a full explanation of this formula....
For example, given two numbers, 4 and 9, the long-hand calculation for the geometric mean is 6: The GEOMEAN function returns the same result: By contrast, the arithmetic mean is 6.5: The GEOMEAN 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. Often, a single range or array is used instead of multiple arguments, as seen in the example worksheet....
Here, the ROW function returns an array with 10 items: and each item in this array is displayed in a different cell. Steps to enter a multi-cell array formula To enter a multi-cell array formula, follow these steps: Multi-cell array formulas have unique characteristics: To select all the cells in a multi-cell array formula, you can use the keyboard shortcut control + / Author Dave Bruns Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa....
The starting point (the reference argument) can be one cell or a range of cells. The rows and cols arguments are the number of cells to “offset” from the starting point. The height and width arguments are optional and determine the size of the range that is created. When height and width are omitted, they default to the height and width of reference. For example, to reference C5 starting at A1, reference is A1, rows is 4 and cols is 2: To reference C1:C5 from A1, reference is A1, rows is 0, cols is 2, height is 5, and width is 1: Note: width could be omitted, since it will default to 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.
On a Mac, check options at System Preferences > Keyboard > Shortcuts > Keyboard to confirm this shortcut is enabled. 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.
Note: STDEV.S replaces the older STDEV function, but has identical behavior. Although STDEV still exists for backwards compatibility, Microsoft recommends that people use the newer STDEV.S function instead. Standard Deviation functions in Excel The table below summarizes the standard deviation functions provided by Excel. Notes: STDEV.S calculates standard deviation using the “n-1” method. STDEV.S assumes data is a sample only. When data represents an entire population, use the STDEV.P function....
counts all cells in the range B5:B11 that end with the text “combo”. And this formula: Counts all cells in A1:A100 that contain exactly 3 characters. Available wildcards Excel has 3 wildcards you can use in your formulas: Asterisk () - zero or more characters Question mark (?) - any one character Tilde (~) - escape for literal character (~) a literal question mark (~?), or a literal tilde (~~)....
In this case, we want days ignoring years so we supply “yd” for unit. (For the full list of options, see the DATEDIF page). Once configured, the function is fully automatic and returns a result in the unit requested. 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....
Where named ranges are amts (D4:D11), id (I3), and ids (C4:C11). This is an array formula and must be entered with Control + Shift + Enter. The IF function does the work of figuring out which rows contain a match, and the SMALL function returns the nth value from that list. Inside of IF, the logical test is: which yields this array: Note the customer id matches at the 1st and 4th positions, which appear as TRUE....
Here we have a sample set of data that shows t-shirt sales. You can see we have columns for date, item, color, and amount. So let’s break this data down by color. Now, before we start, I want to mention that Pivot Tables would be an excellent way to summarize this data, but you can certainly use formulas for basic summaries, and that’s what I’m going to demonstrate here. First, I’m going to name the Color and Amount columns in the data....
Let’s take a look. You can find the list of recently opened workbooks on the File tab, under Recent. Here you’ll see a list of workbooks, with the most recently opened workbooks at the top. If there are files you use frequently, or if you’re working on a project and want to make sure you have quick access to specific files during the project, you can “pin” any file that appears in the list....
Let’s take a look. One way to hide a column is to choose Hide Columns from the Format menu on the home ribbon. Notice that when a column is hidden, its heading is also hidden, so that there is a gap in the column letters that run across the top of the worksheet. To unhide a column, you need to select columns on both sides of the hidden column. Then, choose Unhide Columns from the Format menu....
If so, you can save yourself time and trouble by setting a default template for Excel to use each time you create a new workbook. As long as you name the template correctly, and put it in the correct location, Excel will use your custom template to create all new workbooks. Note: biggest challenge with this tip is figuring out the right location for the template file. This can be maddeningly complex, depending on which platform and version of Excel you use....
Each time you click the Evaluate button, Excel will solve the underlined part of the formula and show you the result. Here’s the same worksheet we looked at in a previous video when we talked about debugging formulas using the F9 key. The Age column contains a formula that calculates age from Birthdate, and the Status column uses the IF function to label each person in the list as either an Adult or Minor....
Here’s the commission example we looked at earlier which uses VLOOKUP to find approximate matches in a named range called “commission table.” Let’s take a look at how VLOOKUP handles approximate matches in more detail. First, when you’re using VLOOKUP for approximate matches, it’s likely that the lookup value won’t be in the table. That’s why you’re using the approximate match setting to start with. In that case, Excel moves through the lookup values in the table until it reaches a value that’s greater than the lookup value....
