Excel calculate covariance using COVARIANCE.S and COVARIANCE.P

Posted on April 29, 2016 by Wyman W

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...

Excel Rank Function

Posted on March 18, 2016 by Wyman W

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...

Excel Hyperlink Function

Posted on February 21, 2016February 21, 2016 by Wyman W

This Excel tutorial explains how to use Excel Hyperlink Function to add hyperlink to Cell and send email. Excel Hyperlink Function Excel Hyperlink Function is to add a hyperlink to a Cell. You can add hyperlink without using any Function. Right click on a Cell and then select Hyperlink to enter a web address or email address. With Excel Hyperlink Function, you only need to enter arguments for the Function instead of right clicking a Cell. This is useful if you are trying to create a ...

Excel GetPivotData Function

Posted on November 2, 2015November 2, 2015 by Wyman W

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...

Excel Cell Function to check Cell format, address, contents

Posted on July 12, 2015July 12, 2015 by Wyman W

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...

Excel Indirect Function to convert text to Reference

Posted on March 25, 2015March 25, 2015 by Wyman W

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 ...

Excel Address Function to convert column number to letter

Posted on March 24, 2015April 2, 2015 by Wyman W

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] )...

Excel MRound Function round to nearest multiple

Posted on March 7, 2015March 7, 2015 by Wyman W

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...

Excel Subtotal Function to calculate Auto Filter value

Posted on February 26, 2015 by Wyman W

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...

Excel Hyperlink Function to link to other cell, open website, open document

Posted on February 25, 2015February 25, 2015 by Wyman W

What does Excel Hyperlink Function do? Excel Hyperlink Function is to create a hyperlink in a Cell. The hyperlink can be linked to external website or a Cell in a worksheet. In Excel VBA, there is a similar Method called FollowHyperlink, but it actually “go to” hyperlink instead of creating a link for user to click on. Syntax of Excel Hyperlink Function HYPERLINK(link_location, [friendly_name]) link_location The path of the file you want to link to. To link website, type "http://...