Tue 27 Sep 2016

Solution - Challenge 50 - Cryptography Challenge 3 - Generate Tabula Recta

By |Tuesday, September 27th, 2016|Categories: Solutions|Tags: , , , , , , , , , , , , , , |0 Comments

Below is a possible solution to the Challenge 50 - Cryptography Challenge 3 - Generate Tabula Recta

Put following formula and drag down and right

=CHAR(MOD(ROWS($1:1)+COLUMNS($A:A)-2,26)+65)

A workbook containing the above solution can be downloaded from Solution - Challenge 50 - Cryptography Challenge 3 - Generate Tabula Recta

Sat 24 Sep 2016

Challenge 52 - Generate the Sequence 1,2,2,3,3,3,4,4,4,4,5,5,5,5,5...

By |Saturday, September 24th, 2016|Categories: Challenges|Tags: , , , , |0 Comments

This time challenge before you is to write a formula which generates a sequence where every digit appears that many times as that digit. Hence, 3 will repeat 3 times, 8 will repeat 8 times and so on..Hence, the exact sequence would be 1,2,2,3,3,3,4,4,4,4,5,5,5,5,5,6,6,6,6,6,6,7,7,7,7,7,7,7.....

The solution to above challenge will be published after a month i.e. on 24-Oct-16.

Sat 17 Sep 2016

Tips & Tricks 161 - When is Thanksgiving Day in a Year

By |Saturday, September 17th, 2016|Categories: Tips and Tricks|Tags: , , , , , , , , , |0 Comments

Last time, we discussed about finding Labor Day in a given year. This time, it is is about Thanksgiving Day in a year. Thanksgiving day is 4th Thursday in a November.

Hence, earliest possible day when 4th Thursday can happen is on 22-Nov.

(more…)

Tue 13 Sep 2016

Solution - Challenge 49 – Whether a Word is Isogram or Not

By |Tuesday, September 13th, 2016|Categories: Solutions|Tags: , , , , , , , , , |0 Comments

Below is a possible solution to the challenge - Challenge 49 – Whether a Word is Isogram or Not

Use following formula -

=IF(SUMPRODUCT(--(LEN(A1)-LEN(SUBSTITUTE(UPPER(A1),CHAR(ROW($65:$90)),""))>1))
=1,"Not Isogram","Isogram")

Sat 10 Sep 2016

Challenge 51 - Another Word Challenge - Whether Given Words are Anagram or not

By |Saturday, September 10th, 2016|Categories: Challenges|Tags: , , , , , , , , , |0 Comments

Given a word, an anagram is made by using all letters of the given word exactly once. For example, For example, my name "vijay" can be written as "jaivy", "yajvi" and so on....Some well known anagrams are

Lead - Flea
Admirer - Married
Listen - Silent
Mother in Law - Woman Hitler
Eleven Plus Two - Twelve Plus One

So, below is the challenge. Put original word in A1 and anagram in B1. You need to put a formula which gives the result "Anagram" if B1 has anagram of A1 and gives "Not Anagram" if B1 is not anagram of A1.

The solution to the above challenge will be published after a month i.e. on 10-Oct-16.

Sun 04 Sep 2016

Tips & Tricks 160 - When is Labor Day in a Given Year

By |Sunday, September 04th, 2016|Categories: Tips and Tricks|Tags: , , , , , , , , , |0 Comments

While 1st May is celebrated as Labour Day in most of the countries but in USA/Canada, it is celebrated on 1st Monday of September.

Suppose the year is given in cell A1, you can use following formula to calculate the date for Labor Day

=CEILING(DATE(A1,9,1)-2,7)+2

Note - This utilizes the knowledge gained in Article 34 - Show Date for a Coming or Previous Day (Say Coming Monday, Previous Friday)

1

Sun 04 Sep 2016

Tips & Tricks 159 - Prompted to "Save File" even if I haven't changed anything

By |Sunday, September 04th, 2016|Categories: Tips and Tricks|Tags: , , , , , , , , , |0 Comments

When you open a workbook and you change something in the workbook and you close the workbook, it will ask you to save the workbook. But sometimes, even if you have not changed anything, still the workbook asks to be saved even if you haven't changed.

Point 1 - Microsoft has provided the answer to this question at following link -

(more…)

Sat 03 Sep 2016

Excel Quiz 46

By |Saturday, September 03rd, 2016|Categories: Quizzes|Tags: , , , , , , , |0 Comments

Excel Quiz 46

A general quiz on Excel.

Tue 30 Aug 2016

Solution - Challenge 48 - Whether a Sentence is Pangram or Not

By |Tuesday, August 30th, 2016|Categories: Solutions|Tags: , , , , , , , |0 Comments

Below is a possible solution to the challenge - Challenge 48 - Whether a Sentence is Pangram or Not

=IF(ISNUMBER(SUMPRODUCT(MATCH(ROW($65:$90),INDEX(CODE(MID(SUBSTITUTE(SUBSTITUTE(
SUBSTITUTE(UPPER(A1)," ",""),".",""),",",""),ROW(INDIRECT("1:"&LEN(SUBSTITUTE(SUBSTITUTE(
SUBSTITUTE(A1," ",""),".",""),",","")))),1)),,),0))),"Pangram","Not Pangram")

 

Sat 27 Aug 2016

Challenge 50 - Cryptography Challenge 3 - Generate Tabula Recta

By |Saturday, August 27th, 2016|Categories: Challenges|Tags: , , , , , , , , , , , , , , |1 Comment

Tabula Recta is supposed to be one of the foundation stones of cryptography. This was one of the earliest forms of encryption. The more about this can be read at https://en.wikipedia.org/wiki/Tabula_recta

One look at below and you will understand what it is. In upcoming challenges in future, Tabula Recta will be used. The first row is from A to Z and first column is from A to Z (Yellow row and column). You need to populate all other values by a single formula. (Yellow row and column is something which you can put as constant. But white area is something which you need to populate)

1

The solution to the above challenge will be published after a month i.e. on 27-Sep-16.