Sat 15 Jul 2017

Challenge 64 - Sum up the Range where a particular alphabet appears

By |Saturday, July 15th, 2017|Categories: Challenges|Tags: , , , |1 Comment

Suppose, you have been given a range like this and you need to find the sum of column B where the alphabet "c" appears alone.

To check your answer, the sum will be 27 for above.

The file related to this challenge can be downloaded from Challenge 64 - Sum up the Range where a particular alphabet appears

The answer to the above solution will be presented after a month i.e. on 15-Aug-17.

Sat 10 Jun 2017

Challenge 63 - Convert to Date Format

By |Saturday, June 10th, 2017|Categories: Challenges|Tags: , , , , , , , , , , , |1 Comment

This time, you have been given a file containing Text dates into various formats. The challenge before you is to write a single formula to convert them into an appropriate date format.

The challenge file can be downloaded from Challenge 63 - Convert to Date Format

1

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

Sat 13 May 2017

Challenge 62 - Produce the Sum for Merged Cells Headers

By |Saturday, May 13th, 2017|Categories: Challenges|Tags: , , , , , |1 Comment

Download the workbook related to this challenge - Sum for Merged Cells

This time challenge is to produce the sum where header is merged cells. For the layout like below -

1

You need to write a single formula which can be dragged right and down to generate the sum for below table

1

The solution to this challenge will be published after a month i.e. on 13-Jun-17.

Sat 01 Apr 2017

Challenge 61 - Generate Multiplication Table

By |Saturday, April 01st, 2017|Categories: Challenges|Tags: , , , , , , , , , |2 Comments

This time, I want to set a challenge which is not difficult and useful for your kids.

The challenge is to write a formula which can be dragged right and down to generate a multiplication table.

1

The solution to this challenge will be published after a month i.e. on 1-May-17.

Sat 25 Feb 2017

Challenge 60 - Generate a list of 2nd and 4th Saturdays for a Year

By |Saturday, February 25th, 2017|Categories: Challenges|Tags: , , , , , , , |1 Comment

In India, 2nd and 4th Saturdays are very important days as on these days, the banks remain closed. Hence, banks in India are closed on all Sundays and 2nd and 4th Saturdays apart from their holiday calendar.

To calculate Networkdays for banking industry in India needs that the list of 2nd and 4th Saturdays needs to be generated. You know the syntax of NETWORKDAYS -

NETWORKDAYS.INTL(Start_Date, End_Date, [Weekend],[Holidays])

In weekend parameter, we can specify Sunday as weekend and in Holidays list, we can put the holidays and also the 2nd and 4th Saturdays.

Now, the challenge before you is to write a formula which takes Year as Input from A1 and which when dragged down produces the 2nd and 4th Saturdays.

1

Note - The solution to above problem will be published after a month i.e. on 25-Mar-17.

Sat 04 Feb 2017

Challenge 59 - Clean the Problem Workbook Data

By |Saturday, February 04th, 2017|Categories: Challenges|Tags: , , , , , |1 Comment

Download the the workbook from the below link. The Challenge before you is to write a formula to clean the data. If you perform any mathematical operation on the data, it will return #VALUE error. You need to clean the data through a formula.

Challenge 59 - Problem Workbook

The solution to the above problem will be posted after a month i.e. on 4-Mar-17.

 

Sat 17 Dec 2016

Challenge 58 - Make a Vedic Square

By |Saturday, December 17th, 2016|Categories: Challenges|Tags: , , , , , , |1 Comment

Vedic square is below. In this, first row and first column are numbers 0 and 9. Other numbers are populated as per logic which you will need to identify.

More about Vedic Square at https://en.wikipedia.org/wiki/Vedic_square

1

(more…)

Sat 03 Dec 2016

Challenge 57 - Another Word Challenge - Palindrome or Not

By |Saturday, December 03rd, 2016|Categories: Challenges|Tags: , , , , , , , , , , |1 Comment

We earlier had challenges related to Pangram, Isogram and Anagram. This time, it is about Palindrome. A Palindrome is a word / sentence that reads the same from backward also as it reads from forward. Some of the famous Palindromes are

(more…)

Sat 19 Nov 2016

Challenge 56 – Cryptography Challenge 5 – Fully Functional Caesar’s Shift Cipher Decrypter

By |Saturday, November 19th, 2016|Categories: Challenges, VBA|Tags: , , , , , , , , , , , , |1 Comment

We made ourselves familiarized with Caesar's Shift in Challenge 39 - Cryptography Challenge 1 - Caesar's Shift Cipher and made its decrypter in Challenge 53 - Cryptography Challenge 4 - Caesar's Shift Cipher Decrypter. We also made a fully functional encrypter in Challenge 40 – Cryptography Challenge 2 – Fully Functional Caesar’s Shift Cipher based on VBA solution as formula based solution has its own limitation.

The challenge before you is to write a VBA function to create a fully functional Caesar's Shift Cipher Decrypter. This should take cell or text as first argument and amount of shift as second argument to generate the Decrypted Text.

1

Hence, to generate answer in A3, we have called the function as =DecryptCS(A2,C2)

Note - The shift is only for the English Alphabets and case sensitive. Any other character like comma, space etc. should appear as they are.

The Excel file related to this challenge can be downloaded from Challenge 56 – Cryptography Challenge 5 – Decryption - Fully Functional Caesar’s Shift Cipher

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

Sat 05 Nov 2016

Challenge 55 - Make an Alphabetic Triangle

By |Saturday, November 05th, 2016|Categories: Challenges|Tags: , , , , |1 Comment

This time challenge before you is to write a formula which can be dragged down, left and right to make the below triangle.

(more…)