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…
Excel Quiz 8
Article 12 – Overcoming Wildcard Limitation in Sumproduct
In my view, SUMPRODUCT is the king of all Excel Functions and till Excel 2003, it was rightly so. But starting Excel 2007, SUMIF(S) and COUNTIF(S) conspired to dethrone the king named SUMPRODUCT. But once a king, always a king. There are many scenarios where SUMPRODUCT still comes handy and this is still one of…
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…
Excel Quiz 7
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…