Solutions to the Past Challenges

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

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),"")

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

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.

## Solution - Challenge 58 - Make a Vedic Square

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**

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

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

Use the below formula -

## Solution - Challenge 55 - Make an Alphabetic Triangle

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

Put following formula AB4 somewhere and drag to the right.

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

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),"")

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

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**

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

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)

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

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")