Solutions to the Past Challenges

Tue 27 Sep 2016

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

By |Tuesday, September 27th, 2016|Categories: Solutions|Tags: , , , , , , , , , , , , , , |0 Comments

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

Tue 13 Sep 2016

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

By |Tuesday, September 13th, 2016|Categories: Solutions|Tags: , , , , , , , , , |0 Comments

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

Tue 30 Aug 2016

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

By |Tuesday, August 30th, 2016|Categories: Solutions|Tags: , , , , , , , |0 Comments

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

 

Tue 16 Aug 2016

Solution - Challenge 47 - Generate Pentagonal Series

By |Tuesday, August 16th, 2016|Categories: Solutions|Tags: , , , , , , , |0 Comments

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

Tue 02 Aug 2016

Solution - Challenge 46 – Compute Numerological Sum for a Name

By |Tuesday, August 02nd, 2016|Categories: Solutions|Tags: , , , , , , , , |3 Comments

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

Mon 18 Jul 2016

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

By |Monday, July 18th, 2016|Categories: Solutions|Tags: , , , , , , |0 Comments

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)

Mon 04 Jul 2016

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

By |Monday, July 04th, 2016|Categories: Solutions|Tags: , , , , , , , |0 Comments

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)

Tue 21 Jun 2016

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

By |Tuesday, June 21st, 2016|Categories: Solutions|Tags: , , , , , , , |0 Comments

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)

(more…)

Tue 07 Jun 2016

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

By |Tuesday, June 07th, 2016|Categories: Solutions|Tags: , , , , , , , , |0 Comments

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

Mon 23 May 2016

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

By |Monday, May 23rd, 2016|Categories: Solutions|Tags: , , , , , , |0 Comments

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