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.

Sat 13 Aug 2016

Challenge 49 - Whether a Word is Isogram or Not

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

This time, continuing my effort to make challenge on English language, the challenge is on Isogram. An Isogram is that word in English which doesn't repeat any alphabet. For example: "Botany" is an Isogram but "Britain" is not as "i" repeats.

Hence, this time, you need to make a formula which returns "Isogram" if the word is Isogram othewise returns "Not Isogram"

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

Sat 30 Jul 2016

Challenge 48 - Whether a Sentence is Pangram or Not

By |Saturday, July 30th, 2016|Categories: Challenges|Tags: , , , , , , , |1 Comment

This time, I decided to pose a challenge on English words. A Pangram is that sentence in English which uses all 26 alphabets of English language at least once. A very common example is the very famous sentence "The quick brown fox jumps over the lazy dog" which contains all alphabets of English language at least once. Below link contains many other examples of Pangram.

http://www.rinkworks.com/words/pangrams.shtml

The challenge before you is to write a formula which gives the output "Pangram" or "Not Pangram" depending upon the fact if cell A1 has a Pangram sentence or not. Apart from English alphabets, the sentence can contain blanks, dots and commas and no other characters.

The given link contains many examples for Pangram which you can use as your test cases. Some examples from the above site -

A mad boxer shot a quick, gloved jab to the jaw of his dizzy opponent.
Big Fuji waves pitch enzymed kex liquor.
We quickly seized the black axle and just saved it from going past him.

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

Sat 16 Jul 2016

Challenge 47 - Generate Pentagonal Series

By |Saturday, July 16th, 2016|Categories: Challenges|Tags: , , , , , , , |2 Comments

Pentagonal number series is following - 1, 5, 12, 22, 35, 51, 70, 92, 117, 145, 176....

You need to write an Excel formula which can be dragged down and generates the above sequence.

The solution to this problem will be published after a month i.e. on 16-Aug-16.

Sat 02 Jul 2016

Challenge 46 - Compute Numerological Sum for a Name

By |Saturday, July 02nd, 2016|Categories: Challenges|Tags: , , , , , , , , |3 Comments

I had posted Tips & Tricks 119 – Numerology Sum of the Digits aka Sum the Digits till the result is a single digit. In this, I had explored how to add a number and arrive at a single digit. For example, if you have to add 8 + 7 the answer would be 15. You need to further add up 1 and 5 of 15 and final answer would be 6. And this is numerological sum.

In numerology, we calculate the digits corresponding to a name. All alphabets carry a number corresponding to 1 to 9. A has 1, B has 2......I has 9, J has 1...R has 9 , S is 1...Z is 8 as illustrated in the table below.

1

Hence, if my name is Vijay, then I need to add 4 + 9 + 1 + 1 + 7 = 22 = 2+2 = 4

Hence, if a person's name is Julia Richards, then following will be numerological sum = 1 + 3 + 3 + 9 + 1 (Corresponding to Julia) + 9 + 9 + 3 + 8 + 1 + 9 + 4 + 1 (Corresponding to Richards) = 61 = 6 + 1 = 7

Challenge before you is to find a formula which calculates Numerological Sum for a given name if name is given in cell A1.

The solution to this problem will be published after a month i.e. on 02-Aug-16.

Sat 18 Jun 2016

Challenge 45 - Number of Days Passed in a Quarter

By |Saturday, June 18th, 2016|Categories: Challenges|Tags: , , , , , , |1 Comment

This time the challenge is - If a date is given, what would be the formula to find the number of days passed in a quarter.

If A1 has the value 12-Mar-16, then 31 days in Jan, 29 days in Feb (2016 is a leap year) and 11 days in Mar = 31+29+11 = 71 days have passed in Q1. Hence, answer is 71.

If A1 has 15-Apr-16, then 14 days have passed in Q2. Hence, answer is 14.

If A1 has 28-Aug-16, then, 31 days in Jul and 27 days in Aug = 31+27 = 58 days have passed in Q3. Hence, answer is 58.

You need to give the formula to find the above.

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

Sat 04 Jun 2016

Challenge 44 - Hyperlink to a cell in another worksheet by specific text (not cell number)?

By |Saturday, June 04th, 2016|Categories: Challenges|Tags: , , , , , , , |1 Comment

You have a grid of English words in Sheet2. In A1 of Sheet1, you have been given a word. You need to create a Hyperlink through formula in B1 of Sheet1. The Hyperlink which says "Take to the Word - Discover" if the word was Discover in A1. "Take me the Word - Pretty" if the word was Pretty in A1.

1

1

You can download the worksheet related to this challenge from Challenge 44 - Hyperlink to a cell in another worksheet by specific text (not cell number)

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

Sat 21 May 2016

Challenge 43 - Consolidate Daily Sheets into a Weekly Sheet

By |Saturday, May 21st, 2016|Categories: Challenges|Tags: , , , , , , , , , , |0 Comments

This time, challenge is going to be tougher. An user receives a daily sheet which has got project status and you need to prepare a consolidated worksheet for the week everyday.

1. Your sheets are named Day1, Day2.....Day7.

2. One sheet can have a maximum of 20 entries.

3. The entries may not be same everyday depending upon whether a new project has started or a project has finished.

4. You need to get all the consolidation in "Consolidated Weekly Sheet".

One typical day's sheet -

1

You need to write formulas (or VBA) to populate row 3 onwards

1

 A typical answer would look like -

1

The worksheet related to this challenge can be downloaded from Challenge 43 - Consolidate Daily Sheets into a Weekly Sheet

The answer to the above challenge will be published after a month i.e. on 21-Jun-16.

Sat 07 May 2016

Challenge 42 - Split a Sentence Into Words in Different Cells

By |Saturday, May 07th, 2016|Categories: Challenges|Tags: , , , , , , , , |0 Comments

This time the challenge before you is to write a formula that if a sentence is given in A1, your formula if dragged to the right should split the sentence into words.

Remember, it has to be a single formula which should be put into B1 and dragged to the right.

See the examples below -

1

A workbook containing this challenge can be downloaded from Challenge 42 - Split a Sentence Into Words in Different Cells

The solution to above challenge will be published on 7-Jun-16.

Sat 23 Apr 2016

Challenge 41 - Sum the Maximum Number where duplicates Exist

By |Saturday, April 23rd, 2016|Categories: Challenges|Tags: , , , , , |1 Comment

Suppose, you have been given following and you will need to find duplicates in column A and sum the maximum values from column B. If duplicates don't exist, values will be taken as they are. The values which needs to be summed up are colored. The answer would be 123 in this.

1

The workbook related to this challenge can be downloaded from Challenge 41 - Sum the Maximum Number where duplicates Exist

The answer to this challenge would be published after a month i.e. on 23-May-16.