Data Below is a small sample of the data used to plot this chart. This data can be downloaded from Yahoo Finance. Steps to create Create a Line Chart with Date and Close data only. Remove extra data series, leaving only Date and Close. Make sure x-axis is formatted as a Date axis. Set Major units to 5 years, minor units to 1 year. Set bounds to 1/1/1990 and 1/1/2020. Apply number format to the axis (“yyyy”) in this case....
This formula will work to calculate next upcoming birthday as well. Note: in this case, we are arbitrarily fixing the as of date as June 1, 2017 in all examples. Because we are interested in the next anniversary date, we add 1 to the DATEDIF result, then multiply by 12 to convert to years to months. Next, the month value goes into the EDATE function, with the original date from column B....
Source data The source data contains three fields: Date, Sales, and Color converted to an Excel Table. Below are the first 10 rows of data: Fields The pivot table uses just two of the three fields in the source data: Date, and Sales. Because Date is grouped by Years and Months, it appears twice in the list, once as “Date” (month grouping), once as “Years”: The Date field has been been grouped by Months and Years:...
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....
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....
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....
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....
ROUNDUP takes two arguments, number and num_digits. Number is the number to be rounded, and num_digits is the place at which number should be rounded. When num_digits is greater than zero, the ROUNDUP function rounds on the right side of the decimal point. When num_digits is less or equal to zero, the ROUNDUP function rounds on the left side of the decimal point. Use zero (0) for num_digits to round to the nearest integer....
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.
