Sat 28 Mar 2015

Challenge 10 - Generate a Repeating Number Sequence - I

By |Saturday, March 28th, 2015|Categories: Challenges|Tags: , , |1 Comment

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
2
3
4
5
1
2
3
4
5
1
2
3
4
5

Note - If you wish, you may like to post your answer in the comments.

Sat 28 Mar 2015

Tips & Tricks 84 – Financial Function - Calculate EMI

By |Saturday, March 28th, 2015|Categories: Tips and Tricks|Tags: , , , , , , |1 Comment

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])

(more…)

Sat 21 Mar 2015

Tips & Tricks 83 – Calculate Geometric Mean by Ignoring 0 and Negative Values

By |Saturday, March 21st, 2015|Categories: Tips and Tricks|Tags: , , |0 Comments

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 Geometric Mean is used to calculate not Arithmetic Mean.

Generally, Geometric Mean is calculated by the formula =GEOMEAN(A1:A10)

It would give error if the range contains <=0 values. There are various ways to deal with it and most commonly used way is to ignore <=0 values while calculating Geometric Mean.

To ignore <=0 values, you must use an Array formula i.e. which must be entered by pressing CTRL+SHIFT+ENTER.

=GEOMEAN(IF(A1:A10>0,A1:A10))

The above formula takes into account only those values which are positive.

Bonus Tip - When %age growth are involved, you will need to use following ARRAY formula to calculate Geometric Mean -

=GEOMEAN(IF(A1:A10>0,(1+A1:A10)))-1

Don't forget to format your result as %age.

Sat 21 Mar 2015

Excel Quiz 8

By |Saturday, March 21st, 2015|Categories: Quizzes|Tags: , , , |0 Comments

The eighth quiz in the series to test your general knowledge in Excel. After you hit Finish Quiz, you can click on View Questions to compare your answers to correct answers.

Sat 14 Mar 2015

Article 12 - Overcoming Wildcard Limitation in Sumproduct

By |Saturday, March 14th, 2015|Categories: Articles|Tags: , , , |1 Comment

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 the most widely used Excel Functions.

But SUMPRODUCT has a limitation. The limitation is that you can't use SUMPRODUCT with wildcards. SUMPRODUCT doesn't like wildcards whereas SUMIF(S) and COUNTIF(S) support wildcards.

(more…)

Sat 14 Mar 2015

Tips & Tricks 82 – Date for Nth Day of the Year

By |Saturday, March 14th, 2015|Categories: Tips and Tricks|Tags: , , , |0 Comments

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

Sat 07 Mar 2015

Tips & Tricks 81 – First Working Day of the Month if a Date is Given

By |Saturday, March 07th, 2015|Categories: Tips and Tricks|Tags: , , , , |1 Comment

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 weekend and 0 is a working day. First digit is Monday and last digit is Sunday. The above example is for Gulf countries where Friday and Saturday are weekends.

You also have option to give a range which has holidays. In that case, your formula would become

=WORKDAY(EOMONTH(A1,-1),1,D1:D10)

=WORKDAY.INTL(EOMONTH(A1,-1),1,"0000110",D1:D10)

Where range D1:D10 contains the list of holidays.

Tue 03 Mar 2015

Excel Quiz 7 - Crossword - II

By |Tuesday, March 03rd, 2015|Categories: Quizzes|Tags: , |0 Comments

This time, I decided to put another Crossword on Excel Functions. This crossword is interactive and you can use browser to play with this. Click in the Grid to start.

Click on Check Puzzle just above Questions to check your score.

If you want to print the Crossword on a paper to play with, download from here Excel Crossword 2

The answer key can be downloaded from here Excel Crossword 2 Answers

If the below Crossword is not properly visible because of WordPress plugin limitation, you can play it properly here  Excel_CW_2_V2

(more…)