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…
Tips & Tricks 93 – Sorting from Left to Right
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…
Challenge 15 – Floyd's Triangle
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…
Tips & Tricks 92 – Financial Function – Calculate Effective Interest
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…
Challenge 14 – Student Scoring Highest Marks in a Subject
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…
Tips & Tricks 91 – Financial Function – Calculate Compounded Interest
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
Excel Quiz 12
Article 14 – Dependent Data List 00 – Create Dependent Data List
— 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…
Challenge 13 – Generating Digit Product Sequences
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,…
Tips & Tricks 90 – Financial Function – Calculate Interest Rate
You want to take a loan. You know how much loan to take (pmt), you know how many months you want to pay up (nper) and you want to know effective rate of interest. Excel makes it easy to do. RATE function is the answer for this.