You want to take a loan and you want to calculate EMI OR you want to build an EMI calculator in Excel. It is a fairly easy job to do – You will need to use PMT function for this. It has following structure – PMT(rate, nper, pv, [fv], [type])
Category: Tips and Tricks
Tips & Tricks 83 – Calculate Geometric Mean by Ignoring 0 and Negative Values
Geometric Mean is a useful mean and is applied only for +ve values. Hence, you will need to ignore <=0 values while calculating Geometric Mean. It is generally used where %ages are involved. For example, population growth for first year is 30%, for second year is 25% and for third year, it is 15%. Then…
Tips & Tricks 82 – Date for Nth Day of the Year
Suppose A1 contains the Year and you are asked to find 69th day of the year which is contained in A2. Then formula for finding Nth day of the year would be =DATE(A1,1,1)+A2-1
Tips & Tricks 81 – First Working Day of the Month if a Date is Given
If A1 contains a date, then formula for First Working Day of the month would be =WORKDAY(EOMONTH(A1,-1),1) The above formula assumes that your weekends are Saturday and Sunday. But, if your weekends are different (e.g. in gulf countries), you can use following formula – =WORKDAY.INTL(EOMONTH(A1,-1),1,"0000110") Where 0000110 is a 7 character string, 1 represents a…
Tips & Tricks 80 – Last Working Day of the Month If a Date is Given
If A1 holds a date, the formula for calculating last Working Day of the month would be =WORKDAY(EOMONTH(A1,0)+1,-1) The above formula assumes that your weekends are Saturday and Sunday. But, if your weekends are different (e.g. in gulf countries), you can use following formula – =WORKDAY.INTL(EOMONTH(A1,0)+1,-1,"0000110") Where 0000110 is a 7 character string, 1 represents…
Tips & Tricks 79 – How to Know if a Year is a Leap Year
Let's say that A1 contains the year. To know whether it is a Leap Year or not, use following formula – =MONTH(DATE(A1,2,29))=2 TRUE means that it is Leap Year and FALSE means that this is not a Leap Year.
Tips & Tricks 78 – Number of Days in a Month
Suppose, you have been given a date say 15-Nov-14 and you have to determine how many days this particular month contains. The formula which you need to use in the above case would be =DAY(EOMONTH(A1,0)) Explanation – EOMONTH(A1,0) gives the last date of the month and DAY function extract that particular Day from the last…
Tips & Tricks 77 – Inserting picture in the comments box
The below discussion is for Excel 2013. If a cell is selected and it has comments, the comments will be displayed. Sometimes, there may be a need to display picture when you select a cell. I answered this question on Microsoft Community Forum and I have decided to reproduce it here. 1. Right click the…
Tips & Tricks 76 – Whenever I Refresh a Pivot, its Column Width Changes
You can stop Pivot from doing this behaviour. Take following steps – 1. Right Click on Pivot Table > PivotTable Options 2. Uncheck Autofit column widths on update.
Tips & Tricks 75 – What is DATEDIF and it is not available in my Excel
DATEDIF calculates the difference between two dates in terms of COMPLETED Days, Months and Years. Note the emphasis on COMPLETED. It means that if elapsed years or months or days is 1.78, the answer would be 1 only. Hence, it gives only integer and doesn't round them. A good documentation exists on following link http://www.cpearson.com/excel/datedif.aspx…