Solutions to the Past Challenges

## Solution - Challenge 50 - Cryptography Challenge 3 - Generate Tabula Recta

Below is a possible solution to the **Challenge 50 - Cryptography Challenge 3 - Generate Tabula Recta**

Put following formula and drag down and right

=CHAR(MOD(ROWS($1:1)+COLUMNS($A:A)-2,26)+65)

A workbook containing the above solution can be downloaded from Solution - Challenge 50 - Cryptography Challenge 3 - Generate Tabula Recta

## Solution - Challenge 49 – Whether a Word is Isogram or Not

Below is a possible solution to the challenge - **Challenge 49 – Whether a Word is Isogram or Not**

Use following formula -

=IF(SUMPRODUCT(--(LEN(A1)-LEN(SUBSTITUTE(UPPER(A1),CHAR(ROW($65:$90)),""))>1))

=1,"Not Isogram","Isogram")

## Solution - Challenge 48 - Whether a Sentence is Pangram or Not

Below is a possible solution to the challenge - **Challenge 48 - Whether a Sentence is Pangram or Not**

=IF(ISNUMBER(SUMPRODUCT(MATCH(ROW($65:$90),INDEX(CODE(MID(SUBSTITUTE(SUBSTITUTE(

SUBSTITUTE(UPPER(A1)," ",""),".",""),",",""),ROW(INDIRECT("1:"&LEN(SUBSTITUTE(SUBSTITUTE(

SUBSTITUTE(A1," ",""),".",""),",","")))),1)),,),0))),"Pangram","Not Pangram")

## Solution - Challenge 47 - Generate Pentagonal Series

Below is a possible solution to the problem - **Challenge 47 - Generate Pentagonal Series**

Put following formula and drag down -

=ROWS($1:1)*(3*ROWS($1:1)-1)/2

## Solution - Challenge 46 – Compute Numerological Sum for a Name

Below is a possible solution to the challenge – **Challenge 46 – Compute Numerological Sum for a Name**

The formula to calculate Numerological Sum for a Name would be -

=MOD(SUMPRODUCT(MOD(CODE(MID(SUBSTITUTE(LOWER(A1)," ",""),

ROW(INDIRECT("1:"&LEN(SUBSTITUTE(A1," ","")))),1))+2,9)+1)-1,9)+1

## Solution - Challenge 45 – Number of Days Passed in a Quarter

Below is a possible solution to the challenge - Challenge 45 - Number of Days Passed in a Quarter

The formula to calculate number of days passed in a quarter is

=A1-DATE(YEAR(A1),(ROUNDUP(MONTH(A1)/3,0)-1)*3+1,1)

## Solution - Challenge 44 – Hyperlink to a cell in another worksheet by specific text (not cell number)?

Below is a possible solution to the challenge -** Challenge 44 – Hyperlink to a cell in another worksheet by specific text (not cell number)?**

Enter the below formula in B1 as Array Formula i.e. not by pressing ENTER after entering your formula but by pressing CTRL+SHIFT+ENTER. If you are copying and pasting this formula, take F2 after pasting and CTRL+SHIFT+ENTER. This will put { } brackets around the formula which you can see in Formula Bar. If you edit again, you will have to do CTRL+SHIFT+ENTER again. Don't put { } manually.

=HYPERLINK("#Sheet2!"&ADDRESS(MATCH(1,--(MMULT(--((Sheet2!A1:J20)=A1),

TRANSPOSE(COLUMN(A1:J20)^0))>0),0)+ROW(A1)-1,MATCH(A1,OFFSET(Sheet2!A1,

MATCH(1,--(MMULT(--(A1=Sheet2!A1:J20),TRANSPOSE((COLUMN(A1:J20)^0)))>0),0)

-1,0,1,COLUMNS(A1:J20)),0)+COLUMN(A1)-1),"Take me to the Word - "&A1)

The workbook illustrating the above solution can be downloaded from **Solution - Challenge 44 - Hyperlink to a cell in another worksheet by specific text (not cell number)**

## Solution - Challenge 43 - Consolidate Daily Sheets into a Weekly Sheet

Below is a possible solution to the challenge - **Challenge 43 – Consolidate Daily Sheets into a Weekly Sheet**

1. Create a Sheet named Project which can hold all the projects. In that sheet, enter following formula in A2 and copy down till row 141 in columns A, B and C (as every sheet can contain a maximum of 20 projects and there are 7 days in a week and you can have a maximum of 20*7=140 rows)

## Solution - Challenge 42 – Split a Sentence Into Words in Different Cells

Below is a possible solution to the challenge - **Challenge 42 – Split a Sentence Into Words in Different Cells**

Enter following formula in B1 and drag to the right and down.

=IFERROR(MID($A1,FIND("@",SUBSTITUTE(" "&$A1&" "," ","@",COLUMNS($A:A))),

FIND("@",SUBSTITUTE(" "&$A1&" "," ","@",COLUMNS($A:B)))-1-

FIND("@",SUBSTITUTE(" "&$A1&" "," ","@",COLUMNS($A:A)))),"")

The workbook containing this solution can be downloaded from **Solution - Challenge 42 – Split a Sentence Into Words in Different Cells**

## Solution - Challenge 41 – Sum the Maximum Number where duplicates Exist

Below is a possible solution to the challenge **Challenge 41 – Sum the Maximum Number where duplicates Exist**

Enter the below formula as Array Formula i.e. not by pressing ENTER after entering your formula but by pressing CTRL+SHIFT+ENTER. If you are copying and pasting this formula, take F2 after pasting and CTRL+SHIFT+ENTER. This will put { } brackets around the formula which you can see in Formula Bar. If you edit again, you will have to do CTRL+SHIFT+ENTER again. Don't put { } manually.

=SUM((MATCH(A2:A10,IF(COUNTIFS(A2:A10,A2:A10,B2:B10,">"&B2:B10)=0,

A2:A10),0)=ROW(A2:A10)-MIN(ROW(A2:A10))+1)*B2:B10)

The workbook illustrating the above solution can be downloaded from **Solution - Challenge 41 - Sum the Maximum Number where duplicates Exist**