Purpose Return value Syntax =NORM.DIST(x,mean,standard_dev,cumulative) x - The input value x. mean - The center of the distribution. standard_dev - The standard deviation of the distribution. cumulative - A boolean value that determines whether the probability density function or the cumulative distribution function is used. Using the NORM.DIST function The NORM.DIST function returns values for the normal probability density function (PDF) and the normal cumulative distribution function (CDF). For example, NORM....

Explanation By definition, the nth root of a number can be calculated by raising that number to the power of 1/n. Exponents are entered using the exponentiation operator (^), with a number on the left and power on the right. So, in this example we get the numbers from column B and powers from column C: =B5^(1/C5) With the POWER function The POWER function is another way to perform exponentiation in Excel....

Purpose Return value Syntax =OFFSET(reference,rows,cols,[height],[width]) reference - The starting point, supplied as a cell reference or range. rows - The number of rows to offset below the starting reference. cols - The number of columns to offset to the right of the starting reference. height - [optional] The height in rows of the returned reference. width - [optional] The width in columns of the returned reference. Using the OFFSET function The Excel OFFSET function returns a dynamic range constructed with five inputs: (1) a starting point, (2) a row offset, (3) a column offset, (4) a height in rows, (5) a width in columns....

Purpose Return value Syntax =PV(rate,nper,pmt,[fv],[type]) rate - The interest rate per period. nper - The number of payment periods. pmt - The payment made each period. fv - [optional] Future value. If omitted, defaults to zero. type - [optional] Payment type, 0 = end of period, 1 = beginning of period. Default is 0. Using the PV function The PV function returns the value in today’s dollars of a series of future payments, assuming periodic, constant payments and a constant interest rate....

Explanation Ordinal numbers represent position or rank in a sequential order. They are normally written using a number + letter suffix: 1st, 2nd, 3rd, etc. To get an ordinal suffix for a small set of numbers, you can use the CHOOSE function like this: =CHOOSE(B5,"st","nd","rd","th","th","th","th","th","th","th") Here CHOOSE simply picks up a number from column B and uses that number as an index to retrieve the right suffix. A universal formula With a larger range of numbers it’s not practical to keep adding values to CHOOSE....

Purpose Return value Syntax =RATE(nper,pmt,pv,[fv],[type],[guess]) nper - The total number of payment periods. pmt - The payment made each period. pv - The present value, or total value of all loan payments now. fv - [optional] The future value, or desired cash balance after last payment. Default is 0. type - [optional] When payments are due. 0 = end of period. 1 = beginning of period. Default is 0. guess - [optional] Your guess on the rate....

Purpose Return value Syntax =RIGHT(text,[num_chars]) text - The text from which to extract characters on the right. num_chars - [optional] The number of characters to extract, starting on the right. Default = 1. Using the RIGHT function The RIGHT function extracts a given number of characters from the right side of a supplied text string. The first argument, text , is the text string to extract from. This is typically a reference to a cell that contains text....

Explanation In this example, the context is that a certain number of items are needed and the items are only available in bundles of a set size. The goal is to calculate the number of bundles needed based on the items in each bundle, and the number of items needed. For example: If you need 6 items, and the bundle size is 2, you’ll need 3 bundles and have zero extra items....

About This Shortcut Inside a cell, this shortcut will extend the selection by one word to the left each time the left arrow key is pressed. About This Shortcut Inside a cell, this shortcut will select from the cursor to the beginning of the text.

Purpose Return value Syntax =SQRTPI(number) number - A positive number to multiply by π before taking the square root. Using the SQRTPI function The SQRTPI function calculates the square root of (number × π). This is equivalent to SQRT(number*PI()) but provides a more direct way to perform this calculation. The function returns a numeric value accurate to 15 digits. =SQRTPI(1) // returns 1.77245385090552 (square root of π) If the number argument is negative, SQRTPI returns a #NUM!...

Explanation In this example, the goal is to sum the values in columns C, E, G, and I conditionally using the text values in columns B, D, F, and H for criteria. This problem can be solved with the SUMPRODUCT function , which is designed to multiply ranges or arrays together and return the sum of products. The formula in K5 is: =SUMPRODUCT(--($B5:$H5=K$4),$C5:$I5) Working from the inside out, SUMPRODUCT is configured with two arguments, array1 and array2 ....

Explanation In this example, the goal is to sum amounts C5:C16 when the date in B5:B16 is greater than the date provided in cell E5. A good way to solve this problem is with the SUMIFS function . Note: for SUMIFS to work correctly, the worksheet must use valid Excel dates . All dates in Excel have a numeric value underneath, and this is what allows SUMIFS to apply the logical criteria described below....

Transcript In this video, we’ll look at some advanced options for the TEXTBEFORE and TEXTAFTER functions. In this first example, let’s look at how to configure TEXTBEFORE and TEXTAFTER to handle situations when a delimiter is not found. In column B, we have a list of projects with status. The delimiter between the project and status is a hyphen surrounded by space. The goal is to extract the project and status into separate columns....

Explanation This example shows a workbook designed to apply discounts based on seven pricing tiers. The total quantity of items is entered as a variable in cell C4. The discount is applied via the unit costs in E7:E13, which decrease as the quantity increases. The first 200 items have an undiscounted price of $1.00. The next 300 items have a discounted unit price of $0.90. The next 250 items have a unit price of $0....

Purpose Return value Syntax =VAR.S(number1,[number2],...) number1 - First number or reference. number2 - [optional] Second number or reference. Using the VAR.S function The VAR.S function calculates the variance of a sample of data. Variance provides a general idea of the spread of data. The VAR.S function can accept up to 254 arguments. Note while VAR.S ignores text and logicals passed into as cell references, it will evaluate logical values, and text representations of numbers hardcoded directly as arguments....

Explanation The ISREF function returns TRUE for a valid worksheet reference and FALSE is not. In this case, we want to find out of a particular sheet exists in a workbook, so we construct a full reference by concatenating the sheet names in column B with an exclamation mark and “A1”: B5&"!A1" This returns the text: "Sheet1!A1" which goes into the INDIRECT function. INDIRECT then tries to evaluate the text as a reference....

Transcript When you open a workbook in Excel, you’ll be working in a surprisingly dense interface, with a lot of different parts. In this lesson, we’ll take a high level run through the Excel interface to get you up to speed with the most important parts. Let’s take a look. First and foremost is the worksheet. Each Excel workbook can have an unlimited number of worksheets. Worksheets appear as tabs at the bottom of an Excel workbook window....

About This Shortcut This shortcut will activate the selected command or control in the Ribbon. For example, it will check and uncheck a checkbox. About This Shortcut This shortcut confirms any change make to a ribbon or dialog window box. For example, enter a value in an input like width or height, and press enter. You can also use the tab key to confirm a change and move to the next control....

Transcript In this video, we’ll review the key terminology associated with Excel Tables. All Excel tables are composed of rows and columns, and share a number of common elements. First, there’s the table itself, which is a rectangular range of cells with a unique name. You’ll see the table name on the design tab, and in the name box menu. The data range in a table is dynamic. As rows are added or removed, Excel will keep track of these changes....

Purpose Return value Syntax =BYCOL(array,lambda) array - The array or array to process. lambda - The lambda function to apply to each column. Using the BYCOL function The Excel BYCOL function applies a function to each column in array and returns one result per column in a single array . The purpose of BYCOL is to process data in an array or range in a “by column” fashion. For example, if BYCOL is given an array with 10 columns, BYCOL will return an array with 10 results....