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.
SUMX2PY2 takes two arguments, array_x and array_y. Array_x is the first range or array or range of numbers, and array_y is the second range or array of numbers. Both arguments can be provided as an array constant or as a range. Examples In the example shown above, the formula in E5 is: which returns 600 as a result. Equation The equation used to calculate the sum of the sum of squares is: This formula can be created manually in Excel with the exponentiation operator (^) like this: With the example as shown, the formula below will return the same result as SUMX2PY2:...
The text comes from column B, and the number of characters can be any large number that will ensure the last two words are extracted. The challenge is to determine the starting position, which is just after the second to last space. The clever work is done primarily with the SUBSTITUTE function, which has an optional argument called instance number. This feature is used to replace the second to last space in the text with the “@” character, which is then located with the FIND function....
This locates the left parentheses and adds 1 to get the position of the first character inside the parentheses. To figure out how many characters to extract, we use this expression: This locates the second parentheses in the text, and subtracts the position of the first parentheses (less one) to get the total number of characters that need to be extracted. With this information, MID extracts just the text inside the parentheses....
