Let’s take a look. Let’s start first with the data that goes into a pivot table. This data is referred to as Source data. Source data contains rows and columns, and each column represents a Field available in the pivot table. So, for example, in this case, there are 9 columns, which translates into 9 fields that are part of the source data. Now let’s look at the pivot table in this worksheet....
Where times is the named range C6:C13. In this case, we are ranking race times. The lowest/fastest value should rank #1, so we set the order argument 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....
in a worksheet with the following named ranges: rate = F4, fixed = F5, limit = F6. The rules of this problem are as follows: If TRUE, the formula simply multiplies the amount in B5 by tax rate: If FALSE, the formula applies the tax rate to the amount over 1000, then adds the fixed amount: Dave Bruns Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa....
This order of operations can be expressed by the mnemonic phrase “Please Excuse My Dear Aunt Sally” which stands for: Parentheses Exponents Multiplication and Division Addition and Subtraction Let’s take a look. Our first example is “3 plus 4 divided by 2”. Without any parentheses, Excel will perform division first, then addition. So, 4 divided by 2 = 2, plus 3, which equals 5. If we want Excel to add 3 and 4 together first, we need to add parentheses around the 3 and 4....
Syntax TODAY() There are no arguments for this function. Example In this example we return today’s date.
where data is the named range B5:B15. Note: I learned this formula from fellow Excel MVP, Sergei Baklan . REDUCE function The REDUCE function applies a custom LAMBDA function to each element in a given array and accumulates results to a single value. The generic syntax for the REDUCE function looks like this: The calculation performed by REDUCE is determined by a custom LAMBDA function with this generic syntax: The first argument, a, is the accumulator....
In Excel, a formula is simply an expression that begins with an equal sign. A formula can be as simple as 1 + 1 or 2 + 2. However, what gives Excel so much power is that a formula can reference cells. When values in cells that have been referenced change, Excel will recalculate the formula automatically and display a new result. Let’s take a look. All formulas in Excel must begin with an equal sign....
In our last video, we used nested IF statements to calculate a commission rate based on a sales number. As a quick recap: The first formula is created with nested IF statements normally. The second example is the same formula but formatted with line breaks to make it easier to read. The third formula performs the same calculation, but it uses VLOOKUP instead of nested IF statements. If I change the sales number, all three formulas calculate the same commission rate....
Syntax YEAR(Date) Date (required) – This is the date value that you want to return the year from. Example In this example we use the function on several dates in different formats. Notice the text values we try result in a #VALUE! error while numerical values outside of the range 1 to 2,958,465 result in a #NUM! error.
This might be something you have accidentally enabled and don’t understand why your Excel is not scrolling normally or why the arrow keys aren’t moving the active cell cursor. It can be frustrating when your Excel workbook isn’t working normally as expected! This post is going to show you how to fix your scrolling problems and turn off the scroll lock feature in Excel. What is Scroll Lock? Scroll lock is a feature that allows you to scroll through your worksheet using the arrow keys without moving the active cell....
Note: a negative value for height won’t work in Google sheets. See below for more information. reference = C3 rows = COUNT(A:A) cols = 0 height = -5 width = (not provided) The starting reference is provided as C3 the cell above the actual data. Since we want OFFSET to return a range originating from the last entry in column C, we use the COUNT function to count all values in column C to get the required row offset....
Note: this formula will only handle a 2-level outline. Note the range is an expanding reference, so it will expand as it is copied down the column. The “level 2” number is generated with this code: Here, the IF function is used to check the contents of B5. If B5 is not blank, it means we have a new level 1 heading and IF returns 1. In other words, every time we have a new level 1 entry, we restart level 2 numbering at 1....
Sometimes, there is no alternative to it, however, you can still bring in some colors and zing to it by using Excel features to format the data. Formatting the data in Excel can increase its readability tremendously. Let me introduce you to some of the formatting features in Excel that can be quickly accessed through the Excel Ribbon. Suppose you have the data as shown below: Here are some ways you can format this data to make it look better:...
Where data is an Excel Table based on the data in B5:D16. The result from COUNTIFS spills into the range G5:I9. Note the result from COUNTIFS is a count of records that meet criteria. See below for a formula to sum the Qty column using the same criteria. Create the Excel Table One of the key benefits of an Excel Table is its ability to resize when rows are added or removed....
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 COMBINA function allows repetitions. To count combinations that do not allow repetitions, use the COMBIN function. To count permutations (combinations where order does matter) see the PERMUT function. The COMBINA function takes two arguments: number, and number_chosen. Number is the number of different items available to choose from. The number_chosen argument is the number of items in each combination. Both arguments are required. Example To use COMBINA, specify the total number of items and “number_chosen”, which represents the number of items in each combination....
The CONCATENATE function accepts multiple arguments called text1, text2, text3, etc. up to 30 total. Values may be supplied as cell references, and hard-coded text strings. Only the first argument is required, and values are concatenated in the order they appear. For example, to concatenate the value of A1 and B1, separated by a space, you can use CONCATENATE like this: The result of this formula is the same as using the concatenation operator (&) manually like this: The ampersand character (&) is an alternative to CONCATENATE....
The LN function takes just one argument, number, which should be a positive number. Examples The equivalent form of the natural logarithm function is given by: Graphs Below is a graph of the natural log logarithm: The natural logarithm function and exponential function are the inverse of each other, as you can see in the graph below: This inverse relationship can be represented with the formulas below, where the input to the LN function is the output of the EXP function: See wumbo....
The MOD function takes two arguments: number and divisor. Number is the number to be divided, and divisor is the number used to divide. Both arguments are required. If either argument is not numeric, the MOD function returns #VALUE!. Equation The result from the MOD function is calculated with an equation like this: where n is number, d is divisor, and INT is the INT function. This can create some unexpected results because of the way that the INT function rounds negative numbers down, way from zero: MOD with negative numbers is implemented differently in different languages....
