Excel find the percentile of a number using PERCENTRANK.EXC function

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

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