Solutions to the Past Challenges

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

Mon 05 Dec 2016

## Solution - Challenge 55 - Make an Alphabetic Triangle

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

Below is a possible solution to Challenge 55 - Make an Alphabetic Triangle

Put following formula AB4 somewhere and drag to the right.

Tue 22 Nov 2016

## Solution - Challenge 54 - Make a Sequence like A_B__C___D____E_____F......

By |Tuesday, November 22nd, 2016|Categories: Solutions||0 Comments

Below is a possible solution to the challenge Challenge 54 - Make a Sequence like A B C D E F......

Put below formula as array formula in a cell and drag down

=IFERROR(CHAR(MATCH(ROWS(\$1:1),(ROW(\$1:\$26)*(ROW(\$1:\$26)+1))/2,0)+64),"")

Tue 08 Nov 2016

## Solution - Challenge 53 - Cryptography Challenge 4 - Caesar's Shift Cipher Decrypter

By |Tuesday, November 08th, 2016|Categories: Solutions||0 Comments

Below is a possible solution to the Challenge 53 - Cryptography Challenge 4 - Caesar's Shift Cipher Decrypter.

Put following formula and drag down

=IFERROR(CHAR(IF(CODE(LOWER(G2))-\$B\$3<97,CODE(G2)-\$B\$3+26,CODE(G2)-\$B\$3)),"")

The Excel file containing the solution can be downloaded from Solution - Challenge 53 - Decryption - Caesar's Shift Cipher

Mon 24 Oct 2016

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

By |Monday, October 24th, 2016|Categories: Solutions|Tags: , , , , |0 Comments

Below is a possible solution to the challenge Challenge 52 - Generate the Sequence 1,2,2,3,3,3,4,4,4,4,5,5,5,5,5...

Put following formula and drag down

=FLOOR((1+((1+8*ROWS(\$1:1)-1)^0.5))/2,1)

Mon 10 Oct 2016

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

By |Monday, October 10th, 2016|Categories: Solutions||0 Comments

Below is a possible solution to the Challenge 51 - Another Word Challenge - Whether Given Words are Anagram or not

Use following formula -

=IF(AND(LEN(A1)=LEN(B1),SUMPRODUCT(--(LEN(A1)-LEN(SUBSTITUTE(UPPER(A1),
UPPER(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),""))=LEN(B1)-LEN(SUBSTITUTE(
UPPER(B1),UPPER(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),""))))=LEN(A1)),"Anagram","Not Anagram")