Sat 30 May 2015

Excel Quiz 13 - LOOKUPs Quiz - Part I

By |Saturday, May 30th, 2015|Categories: Quizzes|Tags: , , , , , , , |3 Comments

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

Sat 30 May 2015

Challenge 16 – Team Pairing

By |Saturday, May 30th, 2015|Categories: Challenges|Tags: , , , , , , |1 Comment

Given the team list below, the challenge before you is to align teams RANDOMLY under Group A and Group B where one team faces another and there is no repetitions of the team. The formula should be flexible, in case, team list increases / decreases. The team list will always be even as if number of teams is odd, then one team will be left out.

The related Excel file can be downloaded from here Team Pairing

11

Note - You may choose to post your response in comments.

Sat 30 May 2015

Tips & Tricks 93 - Sorting from Left to Right

By |Saturday, May 30th, 2015|Categories: Tips and Tricks|Tags: , , |0 Comments

Generally, we do sorting in a column from top to bottom. But, what happens if the data is in rows. Then you will have to sort from left to right. This is possible in Excel.

1. Select your data in the row.
2. Data tab> Sort
OR
Home tab > Sort & Filter
OR
ALT+DS

11

3. You will get following screen. Here, you may choose appropriate action.

11

4. Pressing OK brings us to Sort screen. There, you may choose Options to bring up Sort Options. Here, you can choose Sort left to right to affect sort.

11

Sat 23 May 2015

Challenge 15 - Floyd's Triangle

By |Saturday, May 23rd, 2015|Categories: Challenges|Tags: , , , , |1 Comment

Wikipedia article related to Floyd's Triangle will give you details about this. But, one look at below and you will understand what is this.

1
2 3
4 5 6
7 8 9 10
11 12 13 14 15
16 17 18 19 20 21
22 23 24 25 26 27 18

Now, the challenge before you is to give me a formula which can be put anywhere in the Excel and can be dragged down and right to generate the above triangle.

Hence, if I put the formula in C3 and copy the formula in the range C3:H8, the below Grid should be generated which is Floyd's Triangle.

1

Note - You may choose to post your response in comments.

Sat 23 May 2015

Tips & Tricks 92 – Financial Function – Calculate Effective Interest

By |Saturday, May 23rd, 2015|Categories: Tips and Tricks|Tags: , , , , |0 Comments

You are applying for a loan and an interest rate has been quoted. The interest rate which is quoted is called "Nominal Interest Rate". They will quote Nominal Interest Rate in yearly terms. Hence, if they quote 12% interest for a loan, this is yearly figure. Now, you generally pay EMIs every month. They simply say that you need to pay 1% monthly interest which has been derived by annual interest rate / 12 which 12%/12=1% in this case.

(more…)

Sat 16 May 2015

Challenge 14 - Student Scoring Highest Marks in a Subject

By |Saturday, May 16th, 2015|Categories: Challenges|Tags: , , , |1 Comment

The Excel file pertaining to this problem can be downloaded from here Max Scoring Subject

Challenge 12 was a related challenge. Now, this time, you will need to find the maximum number which has been scored and by which student in which subject. This, you need to do without using a helper column.

In case of ties, choose the first one as the answer.

In below example, 99 marks have been scored in Subject 7 by Leon.

1

Note - You may choose to post your answer in comment section.

Sat 16 May 2015

Tips & Tricks 91 – Financial Function – Calculate Compounded Interest

By |Saturday, May 16th, 2015|Categories: Tips and Tricks|Tags: , , |0 Comments

As part of our Mathematics courses in our childhood, we had learned about Compounded Interest. The famous formula which we remember is

Compounded Balance = Principal x (1+rate)^N

(more…)

Sat 16 May 2015

Excel Quiz 12

By |Saturday, May 16th, 2015|Categories: Quizzes|Tags: , , , |0 Comments

The 12th 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 09 May 2015

Article 14 - Dependent Data List 00 - Create Dependent Data List

By |Saturday, May 09th, 2015|Categories: Articles|Tags: , , , , , , |0 Comments

--- The Excel files related to this article can be downloaded from Dependent Drop Down List_Single Words & Dependent Drop Down List_Multiple Words  ---

Creation of dependent data list is a topic which I wanted to write and I have got some time to talk about this. In this article, I will be covering the dependency for one level but same logic can be extended to more levels.

1. Create the right layout for your dependent data list

Suppose, you have a list of 6 continents and you want to display the countries on the basis of continents selected. The step 1 is to layout your data properly.

Below, is the layout which you need to use. First row will contain the name of continents (i.e. first level drop down values) and below them will be the list of countries (i.e. second level of drop down values)

No blanks are allowed in between the list entries. Blanks can be only at the end.

Dependent Drop Down Layout_00

(Please note that Row1 has single words like North America is written as NorthAmerica i.e. no blanks. Also, it doesn't have any special character like @, % etc. The reason is that Naming Range doesn't support blanks / special characters in between the words. For matters of simplicity, let's keep it as one word without any special characters. We will remove this limitation later on)

2. Create the first drop down

Let's assume that cell A2 needs to contain the first drop down.

Select A2 > Data tab > Data Validation > Data Validation > Choose List in Allow: and in source put $F$1:$K$1

First Dropdown

3. Prepare for second drop down - Create Name Ranges

We can see column F has values from F1 till F7.

3.1 Select the range F1:F7
3.2 Formulas tab > Create from Selection > The Create from Selection box will have Top Row automatically selected and all others deselected > Click OK
3.3 Now, you have created a named range Africa.
3.4 Do, it for all other ranges also i.e. select G1:G7 for named range Asia and repeat steps 3.1 to 3.3. For Australia H1:H5 and repeat steps 3.1 to 3.3. Do it for all the continents.

Dependent Drop Down Layout 00

4. Create your second drop down

Select B2 > Data tab > Data Validation > Data Validation > Choose List in Allow: and in source put

=INDIRECT($A$2)

Dependent Drop Down  00

Now, you have the drop down created.

Dependent Drop Down Layout 00_1

Removing Limitation of Spaces (blanks) in First Row (First Dropdown)

We talked about the limitation that First Row (First Dropdown) can not contains spaces between the words as Name Manager doesn't support space in the Name Box. Let's now remove this.

Let's assume you have a list like below where first row has blanks - Notice Australia and NZ, North America & South America.

Dependent Drop Down Layout 00_2

Your remember in Step 3, you created Named Ranges. Excel automatically replaces blanks with underscore and creates named ranges. Hence, you will have Australia_and_NZ, North_America & South_America if there are blanks.

The only change you need to make is for step 4. The formula in this case would be

=INDIRECT(SUBSTITUTE($A$2," ","_"))

Now, you have the drop down created even with blanks in first row.

Dependent Drop Down  00 with blanks

Removing Limitation of Illegal Characters in First Row (First Dropdown)

Many characters are not allowed in the name and when you follow step 1, Excel automatically replaces them. Hence, if you put Australia & NZ, the name range created will be Australia___NZ (2 underscores for spaces and 1 for &). Excel treats & as illegal character as & is a concatenate operator.

Similarily, if I put C1, Excel will treat it as illegal character while creating name as C1 is also a cell address. Hence, Excel creates a named range like C1_  .

For this case, don't use "Create from Selection" method to create Named Ranges

Create the named ranges by selecting the range and choosing Define Name rather from Create From Selection. As you will have to use an INDIRECT formula as given in Removing Limitation of Spaces (blanks) in First Row (First Dropdown). You can use appropriate SUBSTITUTE, REPLACE or LOOKUPS to workout replacing the characters.

Sat 09 May 2015

Challenge 13 - Generating Digit Product Sequences

By |Saturday, May 09th, 2015|Categories: Challenges|Tags: |0 Comments

Digit Product Sequences can be read here https://facstaff.bloomu.edu/ploomis/sequences.html

In Summary, given a starting positive integer number, every succeeding number is previous number + product of non zeros digits of previous number.

Hence, if starting number is 1, then Digit Product Sequences will be -

1, 2, 4, 8, 16, 22, 26, 38, 62, 74, 102, 104, 108, 116, 122, 126, 138, 162, 174, 202, 206, 218, 234, 258, 338, 410, 414, 430, 442, 474, 586, 826, 922, 958, 1318, 1342, 1366, ...

Challenge before you is following -

A1 should have a starting number
Write a formula in A2 which can be dragged down to generate above number sequence.

Note - You may wish to post the answer to this in comments.