This Excel tutorial explains how to find the percentile of a number from an array using PERCENTRANK.EXC function.
Excel Percentile
Percentile is a statistics term used to describe the Nth observation in a data set after sorting in ascending order. For example, 40th percentile (P40) refers to the approximately 40th data of the 100 data. If the data set is not exactly 100, say 200, we find the location of observation at 40% of total data.
Percentile is commonly used in salary survey to find o...
Read More
Chapter 3 – Excel Built -in Function
Excel Exact Function – compare case sensitive text / case sensitive lookup
This Excel tutorial explains how to compare case sensitive text using Excel Exact Function and how to perform case sensitive lookup / vlookup.
Excel Exact Function to Compare case sensitive text / case sensitive lookup (vlookup)
In Microsoft Excel, you cannot perform case sensitive comparison directly using equal sign. For example, "Peter" is considered same as "peter" in Excel.
Vlookup also shows matching result regardless of case sensitivity.
In order to compare case sensitive text, we...
Read More
Excel MOD Function to get remainder
This Excel tutorial explains how to use Excel MOD Function to get remainder in division.
Excel MOD Function to get remainder
In Excel worksheet, you can calculate quotient by a division using operator / . However chances are you need to calculate the quotient as well as reminder. Excel MOD Function calculate the remainder.
Syntax of Excel MOD Function
MOD(number, divisor)
Number
The number for which you want to find the remainder
Divisor
The number by which you want to divide ...
Read More
Access Excel INT Function to get the integer of number
This Access / Excel tutorial explains how to use INT Function to get the integer part of a number.
Access Excel INT Function to get the integer of number
When you have a number with decimal places, you can separate the number into integer part using INT Function, and then work around to get the decimal part. INT Function can be used in Access and Excel, and also VBA.
Syntax of INT Function
INT(number)
INT Function only contains one argument, which is the number from which you want to get th...
Read More
Excel Days360 Function to calculate day difference
This Excel tutorial explains how to use Excel Days360 function to calculate day difference between two dates.
Excel Days360 Function
You can easily calculate the number of days between two dates by a simple deduction using Excel formula, however when you try to calculate the number of years between two dates, most people simply divide the day difference by 365. However, because every month has different days, and due to leap year, dividing the difference by 365 is totally incorrect.
Excel D...
Read More
Excel get formula of a cell using FormulaText Function and VBA
This Excel tutorial explains how to show / get formula of a cell using FormulaText Function and VBA.
Search formula of a Cell
Assume that you have the below spreadsheet and you don't know whether any cells contain a formula.
Press CTRL + F to open Find and Replace dialog
In the Find what text box, enter equal sign = , then press Find Next button
Note that if a cell contains equal sign in Cell Text, it will also be searched.
Show formula of a Cell
Navigate to Formul...
Read More
Excel T.TEST Function to perform Student’s T Test
This Excel tutorial explains how to use T.TEST Function to perform Independent Sample T Test and Paired-samples T Test.
What is Independent T Test
In statistical inference, we are interested to know whether a small sample comes from a population. To inference using sample mean, when the population standard deviation and population mean are known, we can use Z test to interference the population mean from sample mean.
In reality, we do not have data of the whole population. Without the popul...
Read More
Excel IRR Function to calculate Internal Rate of Return
This Excel tutorial explains how to use Excel IRR Function to calculate Internal Rate of Return.
You may also want to read
Excel NPV Function to calculate Net Present Value
Internal Rate of Return
Internal Rate of Return (IRR) is the discount rate of cash flow at which an investment breaks even. Similar to Net Present Value (NPV), which discounts a series of cash flow back to present value.
For example, an initial investment is $10000, at the end of 1st year, the cash flow received is...
Read More
Excel NPV Function to calculate Net Present Value
This Excel tutorial explains how to calculate net present value using Excel NPV Function.
You may also want to read:
Excel VBA NPV Function
Net Present Value
Net Present Value is to calculate the present value of future cash flow. For example, your client will give you $10000 when the project completes after 1 year, assume there will be inflation, the future $10000 will be less than the present $10000. Assume the inflation is 4%, the present value of $10000 is 10000/(1+4%) = 9615.384615 ...
Read More
Excel CORREL Function to calculate coefficient of correlation
This Excel tutorial explains how to use Excel CORREL Function to calculate coefficient of correlation.
Coefficient of Correlation
Covariance is a measure of how much two random variables change together. After calculating covariance, we can check the sign whether it is negative or positive. Positive covariance means positive relationship (y increases as x increases), negative covariance means a negative relationship (y decreases as x increases).
In order to measure the strength of relation...
Read More