Solutions to the Past Challenges

Tue 10 Oct 2017

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

By |Tuesday, October 10th, 2017|Categories: Solutions|0 Comments

Below is a possible solution to the Challenge 65 - How Many Sundays on Last Date of Months

=SUMPRODUCT((ROW(INDIRECT(\$B\$1&":"&\$B\$2))=EOMONTH(ROW(INDIRECT(
\$B\$1&":"&\$B\$2)),0))*(TEXT(ROW(INDIRECT(\$B\$1&":"&\$B\$2)),"ddd")="Sun"))

Below is an Excel sheet containing the solution

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

Tue 15 Aug 2017

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

By |Tuesday, August 15th, 2017|Categories: Solutions|Tags: , , |0 Comments

Below is a possible solution to the Challenge 64 - Sum up the Range where a particular alphabet appears

=SUMPRODUCT(ISNUMBER(SEARCH(" "&C2&","," "&A2:A13&","))*(B2:B13))

Mon 10 Jul 2017

## Solution - Challenge 63 - Convert to Date Format

By |Monday, July 10th, 2017|Categories: Solutions|Tags: , , , , , , , , , , , |0 Comments

Below is a possible solution to the Challenge 63 - Convert to Date Format

Put following formula and drag down

=IFERROR(--SUBSTITUTE(A1,",",""),--SUBSTITUTE(SUBSTITUTE(
SUBSTITUTE(A1,",","")," ","*",2),"*",", "))

Tue 13 Jun 2017

## Solution 62 - Produce the Sum for Merged Cells Headers

By |Tuesday, June 13th, 2017|Categories: Solutions|Tags: , , , , , |0 Comments

Below is a possible solution for the challenge - Challenge 62 - Produce the Sum for Merged Cells Headers

Put following formula B14 and drag right and down

=SUM(OFFSET(\$A\$1,ROWS(\$1:1),MATCH(B\$13,\$1:\$1,0)-1,,IFERROR(MATCH(C\$13,\$1:\$1,0),COUNTA(\$2:\$2)+1)-MATCH(B\$13,\$1:\$1,0)))

Mon 01 May 2017

## Solution - Challenge 61 - Generate Multiplication Table

By |Monday, May 01st, 2017|Categories: Solutions||0 Comments

Below is a possible solution to the challenge - Challenge 61 - Generate Multiplication Table

Put following formula and drag right and down -

=ROWS(\$1:1)*COLUMNS(\$A:A)

Sat 25 Mar 2017

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

By |Saturday, March 25th, 2017|Categories: Solutions|Tags: , , , , , , , , |0 Comments

Below is a possible solution to the challenge - Challenge 60 - Generate a list of 2nd and 4th Saturdays for a Year

Drag the below formula down

=IF(ROWS(\$1:1)<25,FLOOR(DATE(\$A\$1,ROUNDUP(ROWS(\$1:1)/2,0),
2*(MOD(ROWS(\$1:1)-1,2)+1)*7),7),"")

Sat 04 Mar 2017

## Solution - Challenge 59 - Clean the Problem Workbook Data

By |Saturday, March 04th, 2017|Categories: Solutions|Tags: , , , , , |0 Comments

Below is a possible solution to the problem Challenge 59 - Clean the Problem Workbook Data

Formula to convert would be which you need to drag down would be

=--SUBSTITUTE(SUBSTITUTE(A2,UNICHAR(8237),""),UNICHAR(8236),"")

Data has UNICHAR(8237) and UNICHAR(8236) prefixed and suffixed which need to be replaced by above formula.

Tue 17 Jan 2017

## Solution - Challenge 58 - Make a Vedic Square

By |Tuesday, January 17th, 2017|Categories: Solutions||0 Comments

Below is a possible solution to Challenge 58 - Make a Vedic Square

Put the following formula in C3 and drag right and down

=MOD(C\$2*\$B3-1,9)+1

The Excel sheet having this solution can be downloaded from Solution - Challenge 58 - Make a Vedic Square

Tue 03 Jan 2017

## Solution - Challenge 57 - Another Word Challenge - Palindrome or Not

By |Tuesday, January 03rd, 2017|Categories: Solutions||0 Comments

Below is a possible solution to the Challenge 57 - Another Word Challenge - Palindrome or Not

Use the below formula -

Mon 19 Dec 2016

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

By |Monday, December 19th, 2016|Categories: Solutions, VBA||0 Comments

Below is a possible solution to Challenge 56 – Cryptography Challenge 5 – Fully Functional Caesar’s Shift Cipher Decrypter