Transcript In this video we’ll look at how to calculate an average value. Let’s take a look. In this worksheet we have a list of 16 properties, each with a price and other information. Let’s calculate an average price. First, I’ll create a named range for the prices. This makes the formulas easier to read and copy. Excel has a function called AVERAGE , but let’s do the calculation manually to start off with....

Transcript After you’ve built a pivot table , and spent some time looking at the data from various angles, you will likely discover interesting observations, connections, and insights that weren’t visible in the raw data itself. In these cases, you might want to see the detail behind the data that appears in the pivot table. Pivot tables have an interesting feature that allows you to do just that. Let’s take a look....

Transcript In this video, we’ll look at how to work with the chart area and plot area in an Excel chart. All charts in Excel have a chart area, which encloses all elements of the chart. The easiest way to select the chart area is to click just below the top edge of the chart. The Chart Elements menu on the Format tab of the ribbon, and the Format Task pane title will confirm the chart area is selected....

Transcript In this video we’ll look at how to calculate the “nth” smallest or largest values in a range using the SMALL or LARGE function s. This would be, for example, the 1st, 2nd, and 3rd smallest or largest values. In this first sheet, we have a list of students with five test scores. I’ll use the LARGE function to extract the three best scores for each student. To get top values, use the LARGE function ....

Transcript In this lesson, we’ll take a quick look at how to use addition in Excel. To add things in Excel, use the “+” sign. Let’s take a look. Here we have a simple worksheet that highlights several cell references. Following the instructions in the table, let’s build simple formulas that use addition. The first two examples don’t require any cell references and can be input directly. Always use an equal sign “=” to start off the formula:...

Transcript In this lesson, we’ll look at the Number format called “Number.” The Number format provides three options: the ability to set a fixed number of decimal places, an option to use commas to separate thousandths, and several ways to display negative numbers. Let’s take a look. To start off, let’s copy the numbers in column B to the rest of our table. The format of all cells in the table is General, so let’s apply the Number format to columns C through H....

Implicit Intersection describes a formula behavior in Excel where many values are reduced to a single value. Typically, it happens when a range or array is passed into a formula meant to display a single result in a single cell. In this situation, Excel will resolve the formula to a single value following the steps below: If the result is already single value, return the value. If the result is an array , try to return a value from the same row or column as the formula....

Explanation In this example, the goal is to demonstrate how an INDEX and (X)MATCH formula can be set up so that the columns returned are variable. This approach illustrates one benefit of the 2-step process used by INDEX and MATCH: Because INDEX expects a numeric index for row and column numbers, it is easy to manipulate these values before they are returned to INDEX. If you are new to INDEX and MATCH, see the overview here How to use INDEX and MATCH ....

Explanation At the core, this is a normal INDEX and MATCH function : =INDEX(array,MATCH(value,range,0)) Where the MATCH function is used to find the correct row to return from array, and the INDEX function returns the value at that array. However, in this case we want to make the array variable, so that the range given to INDEX can be changed on the fly. We do this with the CHOOSE function:...

Explanation In this example, the goal is to join a text string to a formatted date. The challenge is that numbers lose their formatting in Excel when they are concatenated in a formula. For example, if we have the date 31-Dec-1999 in cell B5, and we join B5 to a text string and don’t control the date format with a formula like this: ="The date is "&B5 The date will revert to its raw serial number format and the result will show the date as a serial number like this:...

Explanation This is a standard “exact match” VLOOKUP formula with one exception: the column index is calculated using the COLUMN function. When the COLUMN function is used without any arguments, it returns a number that corresponds to the current column. In this case, the first instance of the formula in column E returns 5, since column E is the 5th column in the worksheet. We don’t actually want to retrieve data from the 5th column of the customer table (there are only 3 columns total) so we need to subtract 3 from 5 to get the number 2, which is used to retrieve Name from customer data:...

Explanation In this example, the goal is to return the most frequently occurring text based on one or more supplied criteria. Working from the inside out, we use the MATCH function to match the text range against itself, by giving MATCH the same range for lookup value and lookup array, with zero for match type: MATCH(supplier,supplier,0) Since the lookup value is an array with 10 values, MATCH returns an array of 10 results:...

Explanation In a nutshell, this formula uses the LARGE function to find the nth largest value in a set of data. Once we have that value, we plug it into a standard INDEX and MATCH formula to retrieve the associated name. In other words, we use the nth largest value like a “key” to retrieve associated information. The LARGE function is a straightforward way to get the nth largest value in a range....

Purpose Return value Syntax =NORM.S.DIST(z,cumulative) z - Numeric z-score value. cumulative - Logical value that determines the form of the function. Using the NORM.S.DIST function The NORM.S.DIST function returns values for the standard normal cumulative distribution function (CDF) and the standard normal probability density function (PDF). For example, NORM.S.DIST(1,TRUE) returns the value 0.8413 and NORM.S.DIST(1,FALSE) returns the value 0.2420. The parameter, z, represents the output we are interested in and cumulative flag indicates whether the CDF or PDF function is used....

Purpose Return value Syntax =PERCENTRANK(array,x,[significance]) array - Array of data values. x - Value to rank. significance - [optional] Number of significant digits in result. Defaults to 3. Using the PERCENTRANK function The PERCENTRANK function returns the relative standing of a value within a data set as a percentage. For example, a test score greater than 80% of all test scores is said to be at the 80th percentile. In this case, PERCENTRANK will assign a rank of ....

Standard Pivot Tables have a simple feature for creating calculated fields. You can think of a calculated field as a virtual column in the source data. A calculated field will appear in the field list window, but will not take up space in the source data. In the example shown, a calculated field called “Unit Price” has been created with a formula that divides Sales by Quantity. The pivot table displays the calculated unit price for each product in the source data....

To group a pivot table by day of week (e.g. Mon, Tue, Wed, etc.) you can add a helper column to the source data with a formula to extract the weekday name, then use the helper to group data in the pivot table. In the example shown, the pivot table is configured to display sales by weekday. Note that Excel automatically sorts standard weekday names in a natural order, instead of alphabetically....

Purpose Return value Syntax =SLN(cost,salvage,life) cost - Initial cost of asset. salvage - Asset value at the end of the depreciation. life - Periods over which asset is depreciated. Using the SLN function The Excel SLN function returns the depreciation of an asset for one period, calculated with a straight-line method. The calculated depreciation is based on initial asset cost, salvage value, and the number of periods over which the asset is depreciated....

Transcript In this video, we’ll look at how to sort with SORTBY function using a custom list. One challenge that comes up frequently when sorting is a need to sort in a custom order. For example, in this worksheet, we have a list of opportunities, and we want to sort the list in the order that stages appear in column H. How can we do this with a formula? Well, to start off, if we try to sort the data with the SORTBY function , and provide the list of stages for the sort_by array, I’ll get an error....

Purpose Return value Syntax =STDEV(number1,[number2],...) number1 - First number or reference in the sample. number2 - [optional] Second number or reference. Using the STDEV function The STDEV function calculates the standard deviation for a sample set of data. Standard deviation measures how much variance there is in a set of numbers compared to the average (mean) of the numbers. The STDEV function is meant to estimate standard deviation in a sample....