Sun 16 Dec 2018

## Challenge 68 - Need a Data Validation Formula

By |Sunday, December 16th, 2018|Categories: Challenges|Tags: |1 Comment

Need a data validation formula for following assuming you are putting data validation in cell A2.

The user should be able to enter a date in following format

1. MM/YYYY for the year 2018
2. QQ/YYYY for the years 2019 to 2021

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

Sat 20 Jan 2018

## Challenge 69 - Need Help in Not Showing Comments

By |Saturday, January 20th, 2018|Categories: Challenges|Tags: , |4 Comments

Download the workbook. Whenever I select a cell, the yellow box pops up. I need your help to provide me instructions to remove these.

The solution to above challenge will be published after a month i.e. on 20-Feb-2018.

Sat 16 Dec 2017

## Challenge 68 - Generate a Particular Sequence

By |Saturday, December 16th, 2017|Categories: Challenges||1 Comment

Study the sequence below and you need to write a formula which when dragged down should generate the below sequence.

For visual representation purpose, the sequence is in 5 columns but you need to generate the sequence in one column only.

You need to generate upto 100 entries.

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

Thu 09 Nov 2017

## Challenge 67 - Find Duplicates and Show the Count

By |Thursday, November 09th, 2017|Categories: Challenges|Tags: , , |2 Comments

You have been given following data and you need to show corresponding count. You need to write a formula which can generate this count. If needed, you can use a maximum of helper column also.

The solution to this problem will be published after a month i.e. on 9-Dec-17.

Sat 14 Oct 2017

## Challenge 66 - Find the Position of Word

By |Saturday, October 14th, 2017|Categories: Challenges|Tags: , |3 Comments

Suppose you have been given a word say "and" and you need to find which word position is this

1. In Mr. and Mrs. Smith - Position of "and" is 2 as this is the 2nd word, the position is not 5.
2. In Samarand Smith and Kittie Smith - the position is 3 not 2 as "and" is appearing in the first word also but not as a single word.

You need to write a formula which finds the word position for the word "and"

Below is the test data.

Mr. and Mrs John Doe
The Reverend John Doe and Mrs. Jane Doe
and
Myranda and Lynda
Rama and
and rama

You can also download an Excel to work on this problem - Challenge 66 - Find the Position of Word

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

Sun 10 Sep 2017

## Challenge 65 - How Many Sundays on Last Date of Months

By |Sunday, September 10th, 2017|Categories: Challenges||2 Comments

This time you need to work out the number of Sundays on last date of the months between two given dates. For example, between two dates of 1-Jan-2017 to 31-Dec-2020, total number of Sundays on last date of the months is 6 as highlighted below.

The answer to the solution will be published after a month i.e. on 10-Oct-17.

Sat 15 Jul 2017

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

By |Saturday, July 15th, 2017|Categories: Challenges||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.

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

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 -

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

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||4 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.

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