# Excel calculate covariance using COVARIANCE.S and COVARIANCE.P

This Excel tutorial explains how to calculate sample covariance using COVARIANCE.S and calculate population covariance using COVARIANCE.P Covariance Covariance is a measure of how much two random variables change together. The below formula is for calculation of Population Covariance. For Sample Covariance, divide n-1 instead of N. While σx is denoted as standard variation of x, σxy is denoted as Covariance. After we calculate the covariance, we can check the sign whether it is ne...
More

# Excel Rank Function

This tutorial explains how to use Excel Rank Function to give a number a rank in a range of number. Syntax of Rank Function RANK(number,ref,[order]) Number Required. The number whose rank you want to find. Ref Required. An array of, or a reference to, a list of numbers. Nonnumeric values in ref are ignored. Order Optional. A number specifying how to rank number. If order is 0 (zero) or omitted, Microsoft Excel ranks number as if ref were a list sorted in descending order. I...
More

More

# Excel GetPivotData Function

This Excel tutorial explains how to use Excel GetPivotData Function to get Pivot Table Data. You may also want to read: Create Pivot Table using Excel VBA Excel VBA refresh all Pivot Table or Pivot Cache Excel GetPivotData Function Excel GetPivotData Function is used to get data in Pivot Table. For example, in a Pivot Table above, if you type =G3 in a Cell, a formula will generate automatically. =GETPIVOTDATA("Salary",\$E\$1,"Department","IT","Name","John") If you drag the form...
More

# Excel Cell Function to check Cell format, address, contents

This Excel tutorial explains how to use Cell Function to check Cell format, address and contents. You may also want to read: Excel ColorIndex Property Excel verify Number format and convert Text to Number Excel Cell Function to check Cell format, address, contents Excel Cell Function is a very powerful Function that tells various information of a Cell. Cell Function has a list of arguments regarding Cell information. Syntax of Cell Function CELL(info_type, [reference]) [referen...
More

# Excel Indirect Function to convert text to Reference

This tutorial explains how to use Excel Indirect Function to convert text to Reference, and how to use in Data Validation. Excel Indirect Function Excel Indirect Function is to convert text to Reference (or cell value). For example, formula =A1 returns A1 value because A1 is considered as Reference, but ="A1" is considered as Text. Excel Indirect Function can convert the "A1" Text to become Reference. One popular use of Excel Indirect Function is to use in Data Validation where you convert ...
More

# Excel Address Function to convert column number to letter

This Excel tutorial explains how to use Excel Address Function and how to convert column number to column letter. Excel Address Function Excel Address Function is used to combine column number and row number to return Cell address in Text format. Excel Address Function is most useful is converting column number to column letter, or use with Indirect Function to convert the Text address to Range Object. Syntax of  Excel Address Function ADDRESS( row, column, [abs_num], [a1], [sheet_text] )...
More

# Excel MRound Function round to nearest multiple

This tutorial explains how to use Excel MRound Function round to nearest multiple Excel MRound Function round to nearest multiple Excel Round Function is used to round to nearest digit, while Excel MRound Function is used to round to nearest multiple. The first time I used MRound Function is to round employee bonus to nearest \$100, because we don't want the bonus amount looks better. For example, \$11040 is rounded to \$11000. If you fail to use Excel MRound Function, navigate to Developer &g...
More

# Excel Subtotal Function to calculate Auto Filter value

Excel Subtotal Function to calculate Auto Filter value Excel Subtotal Function is a collection of aggregate function (Sum, Average, Min, etc), in which you can specify in argument which aggregate function to use. There are two major differences between Subtotal Function and normal aggregate Functions 1. Subtotal Function can calculate base on filtered value in Auto Filter, while normal aggregate Function calculates all values in a Range regardless of the criteria in Filter. 2. Subtot...
More