This Tips and Tricks is in continuation of Tips & Tricks 84 – Financial Function – Calculate EMI. Now the EMI for a month = Interest for that month and Principal for that month. IPMT is used to calculate the interest portion of your EMI. Excel defines IPMT as "Returns the interest payment for a…
Excel Quiz 10
Tips & Tricks 86 – Quickly Access AutoSum
Autosum is a much widely used feature in Excel. It is available in two places in Excel 2013. One in Home tab and one in Formulas tab. You can quickly access the AutoSum by the shortcut key ALT+
Excel Quiz 9
Article 13 – Generate a Unique / Distinct List out of a List when no blanks in the list / range
Suppose you have a list as given below and now task before you is to generate a list of unique / distinct list from column A. We will also be looking into generating Unique values from Columns A, B and C together which can be generalized to any number of columns. Note – This article…
Tips & Tricks 85 – Show Comments Always
You put a comment in Excel and it puts a red triangle in the top right corner to indicate that there is a comment. But sometimes, it is necessary that comment should be visible when Excel is opened. This becomes very desirable feature when dealing with top executives. This is also a good feature to…
Challenge 11 – Generate a Repeating Number Sequence – II
Last time, I posed a challenge to generate a repeating number sequence. This time, there is small twist. The sequence which you need to generate is 3 times each number i.e. 111222333444555……….. The formula should be flexible enough to be put in any cell and be dragged down. Hence, if I put the formula in…
Challenge 10 – Generate a Repeating Number Sequence – I
This time challenge is to write a formula which can be dragged down to generate number sequence 1,2,3,4,5 and repeats this sequence again. The formula should be flexible enough to be put in any cell and be dragged down. Hence, if I put the formula in D4, dragging down should produce the below series. 1…
Tips & Tricks 84 – Financial Function – Calculate EMI
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])
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…