Sun 27 Mar 2016

Solution - Challenge 37 – Find nth Alphabet from the Bottom

By |Sunday, March 27th, 2016|Categories: Solutions|Tags: , , , , , , |0 Comments

Below is a possible solution to the challenge Challenge 37 – Find nth Alphabet from the Bottom.

Put following formula as Array formula in E1

=IF(SUM(--(ABS(77.5-IFERROR(CODE(UPPER(A1:A100)),0))<13))>=C1,
INDEX(A1:A100,LARGE((ABS(77.5-IFERROR(CODE(UPPER(A1:A100)),0))<13)
*ROW(A1:A100),C1)),"")

Note - Array Formula is not entered by pressing ENTER after entering your formula but by pressing CTRL+SHIFT+ENTER. If you are copying and pasting this formula, take F2 after pasting and CTRL+SHIFT+ENTER. This will put { } brackets around the formula which you can see in Formula Bar. If you edit again, you will have to do CTRL+SHIFT+ENTER again. Don't put { } manually.

The workbook containing the solution can be downloaded from Solution - Challenge 37 - nth Alphabet from the Bottom.

Sat 26 Mar 2016

Challenge 39 - Cryptography Challenge 1 - Caesar's Shift Cipher

By |Saturday, March 26th, 2016|Categories: Challenges|Tags: , , , , , , , , , , , |3 Comments

This is time for a cryptography challenge. The simplest cipher in cryptography is Caesar's Shift. More about this can be read here (though it will not be needed for this challenge) - https://en.wikipedia.org/wiki/Caesar_cipher

For a given alphabet, it simply substitutes by another alphabet by a fixed number of position. Hence, if shift position is 5, a will be f, A will be F, M will be R, x will be c, Z will be E.

Now challenge before you is to write a formula to achieve the same. The result should be case sensitive.

Hence, you will put the Shift in cell B3 and the answer would be expected in column H for column D.

The workbook related to this challenge can be downloaded from Challenge 39 - Caesar's Shift Cipher

1

1

1

The answer to this puzzle will be published after a month i.e. on 26-Apr-16.

Sat 19 Mar 2016

Tips & Tricks 132 - Financial Year Formula (e.g. 2015-16 or FY16)

By |Saturday, March 19th, 2016|Categories: Tips and Tricks|Tags: , , , , , , |0 Comments

A good number of countries don't follow calendar year as the financial year. For example, India's financial year start is 1-Apr and finishes on 31-Mar. Hence, currently (20-Feb-16), the financial year is 2015-16 (It is also written as FY16). On 1-Apr-16, it will become 2016-17 (It is also written as FY17).

Now if a date is given, then following formula can be used to derive 2015-16 kind of result.

=YEAR(A1)-(MONTH(A1)<=3)&"-"&YEAR(A1)+(MONTH(A1)>3)

To generate FY16 kind of result, following formula can be used

="FY"&RIGHT(YEAR(A1)+(MONTH(A1)>3),2)

Sat 19 Mar 2016

Excel Quiz 34 - Crossword VII

By |Saturday, March 19th, 2016|Categories: Quizzes|Tags: , , , |0 Comments

Time for Crossword Again. 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 7

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

(more…)

Sun 13 Mar 2016

Solution - Challenge 36 – Generate Triangular Numbers

By |Sunday, March 13th, 2016|Categories: Solutions|Tags: , , , , , , |0 Comments

Below is a possible solution to Challenge 36 – Generate Triangular Numbers.

Enter below formula anywhere and drag down -

=IF(ROWS($1:1)=1,1,INDIRECT(ADDRESS(ROW()-1,COLUMN()))+ROWS(($1:1)))

A workbook containing the above solution can be downloaded from Solution - Challenge 36 – Generate Triangular Numbers.

Edit - 23-Aug-16 - A better solution is to use below formula and drag down

=ROWS($1:1)*(ROWS($1:1)+1)/2

Sun 13 Mar 2016

Article 37 - VBA - Generating Unique (Non-repeating) Random Numbers Efficiently

By |Sunday, March 13th, 2016|Categories: Articles, VBA|Tags: , , , , , , , , |0 Comments

This article is about how to generate unique or non-repeating random numbers given two limits. Hence, if lower limit is 10 and maximum limit is 100, hence, it should generate random numbers between 10 and 100.

The algorithm to ensure uniqueness is following -

1. Given lower limit and upper limit, generate all numbers and populate an array. Now, this array contains all numbers between lower limit and upper limit sequentially. Hence, in case of 10 to 100, it will contain entries 10, 11, 12, .....99,100.
2. Next is to shuffle the array randomly using Fisher Yates algorithm so that the array contains the numbers 10, 11, 12...99,100 in a random order.
(more…)

Sat 12 Mar 2016

Challenge 38 - Formula for Top 5

By |Saturday, March 12th, 2016|Categories: Challenges|Tags: , , |1 Comment

The challenge, this time, is to write a formula to come up with names and marks for Top 5 in descending order. Below is the original table and answer expected.

The workbook related to this challenge can be downloaded from Challenge 38 - Find Top 5

1

The solution to this challenge will be published after a month i.e. on 12-Apr-16.

Sat 05 Mar 2016

Excel Quiz 33

By |Saturday, March 05th, 2016|Categories: Quizzes|Tags: , , , , |0 Comments

Excel Quiz 33

A general quiz on Excel

Sat 05 Mar 2016

Tips & Tricks 131 - Repeat a Number and Increment and Repeat....

By |Saturday, March 05th, 2016|Categories: Tips and Tricks|Tags: , , , |1 Comment

Suppose, you have been given the task of repeating a number and increment that number and repeat it. For example -

1,1,1,1,2,2,2,2,3,3,3,3.....(Here, we are repeating it 4 times and incrementing and repeating 4 times again and so on)

Then you can use following formula

=ROUNDUP(ROWS($1:1)/4,0)

Suppose, you want to start the number with 5 not 1, then you can use below formula -

=ROUNDUP(ROWS($1:1)/4,0)+4

Hence, general structure of the formula is

=ROUNDUP(ROWS($1:1)/X,0)+Y-1

X - Number of times a particular number is repeated
Y - Starting Numbers

Hence, if you want to start with number 7 and you want to repeat it 5 times, then following formula should be used

=ROUNDUP(ROWS($1:1)/5,0)+6

Tue 01 Mar 2016

Solution - Challenge 35 – Validate 16 Digits Credit Card Numbers through Mod 10 Algorithm

By |Tuesday, March 01st, 2016|Categories: Solutions|Tags: , , , , , , , , |0 Comments

Below is a possible solution to the challenge - Challenge 35 – Validate 16 Digits Credit Card Numbers through Mod 10 Algorithm.

Enter the below formula as Array formula

=MOD(SUM(IF((MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))*MOD(ROW(INDIRECT("1:"&LEN(A1))),2)
=0,0,MOD((MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))*MOD(ROW(INDIRECT("1:"&LEN(A1))),2)*2-1,9)
+1)+(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))*MOD(ROW(INDIRECT("1:"&LEN(A1)))-1,2)),10)=0

Note - Array Formula is not entered by pressing ENTER after entering your formula but by pressing CTRL+SHIFT+ENTER. If you are copying and pasting this formula, take F2 after pasting and CTRL+SHIFT+ENTER. This will put { } brackets around the formula which you can see in Formula Bar. If you edit again, you will have to do CTRL+SHIFT+ENTER again. Don't put { } manually.

The workbook containing the above solution can be downloaded from Solution - Challenge 35 – Validate 16 Digits Credit Card Numbers through Mod 10 Algorithm.