Sat 25 Apr 2015

Tips & Tricks 88 – Financial Function - Calculate Principal Part of an EMI

By |Saturday, April 25th, 2015|Categories: Tips and Tricks|Tags: , , , , , |0 Comments

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. This I talked about in Tips & Tricks 87 – Financial Function - Calculate Interest Part of an EMI. To calculate the principal part of an EMI, you will need to use PPMT.

(more…)

Sat 18 Apr 2015

Challenge 12 - Student Scoring Maximum Total Score

By |Saturday, April 18th, 2015|Categories: Challenges|Tags: , , , |1 Comment

Download the file related to this problem from here Max Total Score

Given the below grid, I need a formula in N2 and N3 which gives the name of the person scoring the total highest marks adding all subjects together. This, you need to do without a helper column.

For example, in the below grid, 721 is the total highest marks and that is scored by Charlton.

1

Note - You may post your answer in the comments.

Sat 18 Apr 2015

Tips & Tricks 87 – Financial Function - Calculate Interest Part of an EMI

By |Saturday, April 18th, 2015|Categories: Tips and Tricks|Tags: , , , , , |1 Comment

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 given period for an investment based on periodic, constant payments and a constant interest rate"

(more…)

Sat 18 Apr 2015

Excel Quiz 10

By |Saturday, April 18th, 2015|Categories: Quizzes|Tags: , , , |0 Comments

The tenth 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 11 Apr 2015

Tips & Tricks 86 - Quickly Access AutoSum

By |Saturday, April 11th, 2015|Categories: Tips and Tricks|Tags: , , , , |1 Comment

11

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+

(more…)

Sat 11 Apr 2015

Excel Quiz 9

By |Saturday, April 11th, 2015|Categories: Quizzes|Tags: , , , , , , |0 Comments

The Ninth quiz in the series to test your formula knowledge mostly in SUM and COUNT areas. After you hit Finish Quiz, you can click on View Questions to compare your answers to correct answers.

Sat 11 Apr 2015

Article 13 - Generate a Unique / Distinct List out of a List when no blanks in the list / range

By |Saturday, April 11th, 2015|Categories: Articles|Tags: , , , |0 Comments

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 is for the list which has no blanks in the range select / list. The case of having blanks in data range will be investigated into another article.

1

We will revert to tackle this problem in 6 ways

A. Manual Way
B. Hybrid Way
C. Formula Way
D. MS Query Way
E. Power Pivot Way
F. VBA Way

(more…)

Sat 04 Apr 2015

Tips & Tricks 85 - Show Comments Always

By |Saturday, April 04th, 2015|Categories: Tips and Tricks|0 Comments

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 provide explanatory notes to your data.

1

This feature needs to be enabled Excel Options.

File > Options > Advanced > Under Display section, select Comments and Indicators

Now, whenever this Excel workbook is opened, the comments will be displayed always.

1

Note - After saving and closing your Excel workbook, you may like to restore this to Indicators only, and comments on hover again. Whosoever opens this workbook, the comments will always be visible to him. He need not change the setting to Comments and indicators.

Sat 04 Apr 2015

Challenge 11 - Generate a Repeating Number Sequence - II

By |Saturday, April 04th, 2015|Categories: Challenges|Tags: , , |1 Comment

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 C8, dragging down should produce the below series.1
1
1
2
2
2
3
3
3
4
4
4

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