Excel Exact Function – compare case sensitive text / case sensitive lookup

Posted on September 17, 2017 by Wyman W

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

Excel MOD Function to get remainder

Posted on October 11, 2016 by Wyman W

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

Access Excel INT Function to get the integer of number

Posted on October 4, 2016 by Wyman W

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

Excel Days360 Function to calculate day difference

Posted on October 3, 2016October 4, 2016 by Wyman W

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

Excel get formula of a cell using FormulaText Function and VBA

Posted on May 19, 2016 by Wyman W

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

Excel T.TEST Function to perform Student’s T Test

Posted on May 19, 2016 by Wyman W

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

Excel IRR Function to calculate Internal Rate of Return

Posted on May 10, 2016 by Wyman W

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

Excel NPV Function to calculate Net Present Value

Posted on May 6, 2016May 7, 2016 by Wyman W

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

Excel CORREL Function to calculate coefficient of correlation

Posted on April 30, 2016 by Wyman W

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

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