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 the example shown, the formula in C6, copied down, is: where named ranges are “cost” = G4, “salvage” = G5, and “life” = G6. Fixed-declining balance calculation To get a rate to use to calculate depreciation based on fixed-declining balance, Excel uses the following formula: To calculate depreciation in each year, Excel uses a formula like this: However, depreciation for the first and last year is calculated differently to account for the month argument....
The LOG function takes two arguments, number and base. The value provided for number should be a positive real number. The base argument represents the base of the logarithm. Base is optional and defaults to 10 if not provided. Examples The logarithm of 16 with base 2 (the power to which 2 must be raised to equal 16) is 4: The logarithm of 100 with base 10 (the power to which 10 must be raised to equal 100) is 2: Because the base argument defaults to 10, the formulas below are equivalent: The LOG10 function also returns the base 10 logarithm of a number:...
QUARTILE.INC vs QUARTILE.EXC Percentiles can be defined as “greater than or equal to” (inclusive) or “greater than” (exclusive). The QUARTILE.INC function is inclusive and has “greater than or equal to” behavior. QUARTILE.EXC is exclusive, and has “greater than” behavior. The screen below shows how QUARTILE.INC and QUARTILE.EXC return different results for the same data. Note QUARTILE.EXC cannot be used to get the minimum or maximum value like QUARTILE.INC. Note: Starting with Excel 2010, the QUARTILE....
Click any function above for details and many examples. What’s different? Although these functions are widely used, they operate differently in three key ways you may not have noticed In the article below, I explain these differences in some detail. Once you understand how these functions work, you can more easily get them to do what you want. In addition, you’ll have a better idea about when you should explore alternatives....
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.
A1 –> $A$1 –> A$1– > $A1– > A1 This is much faster and easier than typing the $ character manually. To convert an existing formula, enter cell edit mode, place the cursor in or next to the reference you’d like to convert, then use the shortcut. Note: In earlier versions of Excel on the Mac, this shortcut is exclusively Command + T. In Mac Excel 365, fn + F4 also works, inline with the Windows version of Excel....
Example In the example shown, there are 11 numeric values in two groups, A and B. The count of values in each group are the inverse of each other. There are four 1’s in group A, four 5’s in group B, etc. The formula in cell F12 returns a positive skew: The formula in J12 returns a negative skew: Excel also contains the SKEW function, which measures sample skewness. The difference in calculation is related to an adjustment (n-1) made when data represents a sample versus the entire population....
The SQRT function takes one argument, number, which must be a numeric value. If number is not numeric, SQRT returns a #VALUE! error. If number is negative, SQRT returns a #NUM! error. Examples Negative numbers The SQRT function will return a #NUM! error when number is negative: To get the square root of a negative number (as if the number was positive), wrap the number in the ABS function like this:...
This is an array formula and must be entered with Control + Shift + Enter. After you enter the formula in the first cell, drag it down and across to fill in the other cells. The gist of this formula is this: we are using the SMALL function to generate a row number corresponding to an “nth match”. Once we have the row number, we simply pass it into the INDEX function function, which returns the value at that row....
The result is the latest available close price in the current month. If there is no data yet in the current month, STOCKHISTORY will return a #VALUE! error. For a more general overview of the STOCKHISTORY function, see this page. The STOCKHISTORY function retrieves historical stock price information based on a given symbol and date range. For example, to return the closing price for Apple, Inc. on December 27, 2021, you can use: The result is an array that includes headers, date, and close price:...
The result is detailed historical pricing for Microsoft Corporation (“MSFT”) over the last 30 days. Note that STOCKHISTORY returns an array of values that spill onto the worksheet into multiple cells. The data appears in reverse chronological order because the STOCKHISTORY is nested inside the SORT function, which is configured to sort in descending order. This can be done with the STOCKHISTORY function, whose main purpose is to retrieve historical information for a financial instrument over time....
It’s important that the formula be entered relative to the “active cell” in the selection, which is assumed to be B4 in this case. Also note that because we are using less than (<) and greater than (<), the lower and upper limit will not be included. Using other cells as inputs You don’t have to hard-code the numbers into the rule and, if the numbers will change, it’s better if you don’t....
Let’s take a look. If you need to apply subscript or superscript formatting, you won’t find these options on the ribbon. Instead, they’re located on the Font tab of the Format Cells dialog box in the Effects group. To apply either subscript or superscript, you need to follow a particular process. You can’t just select a cell normally and apply one of these formats. If you do, you’ll see that all text in a cell is affected, which is almost certainly not what you want....
Let’s take a look. Let’s continue with our menu project and add some font colors. Let’s make the headings one color and the menu items another. Even though we want the headings to be a different color, it will be easiest if we apply one color to the entire worksheet, and then come back and work on the headings separately. As always, start by selecting the cells to format. The easiest way to apply font color is to use the Font color menu on the home tab of the ribbon....
Let’s take a look. Here we have the same pivot table we looked at earlier. Sales are summarized by Region, and we are using a calculated item, Eastern, to summarize the regions East and South. Before we make any changes, let’s add Product as a Row Label above the Region field. Now we have multiple instances of the calculated item Eastern. As you can see, the Name of a calculated item appears directly in the pivot table....
When you use Excel frequently, you’re likely to see a number of potential errors flagged on the worksheet as you work. Exactly which errors are flagged depends on which error checking rules are enabled. To confirm, navigate to: options > formulas > error checking rules Note that these are global settings. If you disable a rule here, it will stay disabled for all worksheets until reenabled. When Excel flags an error on a worksheet, it will appear as a small triangle in the upper left of a cell....
Let’s look at some examples. Arguments can be required or optional. Some functions take three or more arguments, and some functions don’t take any arguments at all. A good example of a function that takes no arguments is the TODAY function, which returns the current date. To use it, just enter an equal sign, TODAY, and empty parentheses. Other functions take multiple arguments; all of which are required. A good example is the DATE function, which allows you to build a valid date using the year, month, and day supplied as separate and required arguments....
Let’s take a look. There are several ways to get to slicer options. First, you can click the Option tab of the Slicer Tools ribbon. The Slicer Tools ribbon will appear whenever you select a slicer. The Slicer Tools ribbon gives you direct access to slicer settings, slicer styles, tools for arranging slicers, and tools for configuring column count, button size, and slicer dimensions. You can set the heading or caption for a slicer directly on the ribbon itself....
