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...
Read More
Chapter 3 – Excel Built -in Function
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...
Read More
Excel Hyperlink Function
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 ...
Read 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...
Read 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...
Read 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 ...
Read 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] )...
Read 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...
Read 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...
Read More
Excel Hyperlink Function to link to other cell, open website, open document
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://...
Read More