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

Sat 13 May 2017

Challenge 62 - Produce the Sum for Merged Cells Headers

By |Saturday, May 13th, 2017|Categories: Challenges|Tags: , , , , , |1 Comment

Download the workbook related to this challenge - Sum for Merged Cells

This time challenge is to produce the sum where header is merged cells. For the layout like below -

1

You need to write a single formula which can be dragged right and down to generate the sum for below table

1

The solution to this challenge will be published after a month i.e. on 13-Jun-17.

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)

Sat 04 Jun 2016

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

By |Saturday, June 04th, 2016|Categories: Challenges|Tags: , , , , , , , |1 Comment

You have a grid of English words in Sheet2. In A1 of Sheet1, you have been given a word. You need to create a Hyperlink through formula in B1 of Sheet1. The Hyperlink which says "Take to the Word - Discover" if the word was Discover in A1. "Take me the Word - Pretty" if the word was Pretty in A1.

1

1

You can download the worksheet related to this challenge from Challenge 44 - Hyperlink to a cell in another worksheet by specific text (not cell number)

The solution to above challenge will be published after a month i.e. on 04-Jul-16.

Tue 02 Feb 2016

Solution - Challenge 33 – Convert Matrix into Linear Column – II

By |Tuesday, February 02nd, 2016|Categories: Solutions|Tags: , , , , , |0 Comments

Below is a possible solution to the challenge - Challenge 33 – Convert Matrix into Linear Column – II.

Enter below formula and drag down -

=IF(OFFSET($A$1,MOD(ROWS($1:1)-1,COUNTA($A:$A)),ROUNDUP(ROWS($1:1)
/COUNTA($A:$A),0)-1)=0,"",OFFSET($A$1,MOD(ROWS($1:1)-1,COUNTA($A:$A)),
ROUNDUP(ROWS($1:1)/COUNTA($A:$A),0)-1))

The workbook containing the above solution can be downloaded from Solution - Challenge 33 – Convert Matrix into Linear Column – II.

Mon 18 Jan 2016

Solution - Challenge 32 – Convert Matrix into Linear Column – I

By |Monday, January 18th, 2016|Categories: Solutions|Tags: , , , , , |3 Comments

Below is a possible solution to Challenge 32 – Convert Matrix into Linear Column – I

Enter following formula and drag down -

=IF(OFFSET($A$1,ROUNDUP(ROWS($1:1)/COLUMNS($A$1:$D$100),0)-1,
MOD(ROWS($1:1)-1,COLUMNS($A$1:$D$100)))=0,"",OFFSET($A$1,ROUNDUP(ROWS($1:1)
/COLUMNS($A$1:$D$100),0)-1,MOD(ROWS($1:1)-1,COLUMNS($A$1:$D$100))))

Sun 03 Jan 2016

Challenge 33 – Convert Matrix into Linear Column – II

By |Sunday, January 03rd, 2016|Categories: Challenges|Tags: , , , , , |1 Comment

Last challenge, we converted a matrix into Linear Column. Last time, we picked up values in a row and populated the column and then moved to next row. This time, we have to do exchange row with column. Hence, you need to pick up values from one column and move to the next column and so on.

Suppose you have been given a grid like below. The challenge before you is to write a formula to create a linear column with no blanks in between.

The column ranges would be A to E whereas row ranges would be from 1 to 100. Hence, your formula has to be flexible enough to consider A1:E100 range. (Note - There would be no blanks in between the values in the Grid)

Download the workbook related to this from Challenge 33 - Convert Matrix into Linear Column - II

1

The solution to this challenge will be published after a month i.e. on 2-Feb-16.

Sat 19 Dec 2015

Challenge 32 - Convert Matrix into Linear Column - I

By |Saturday, December 19th, 2015|Categories: Challenges|Tags: , , , , , |2 Comments

Suppose you have been given a grid like below. The challenge before you is to write a formula to create a linear column with no blanks in between.

The column ranges would be A to D whereas row ranges would be from 1 to 100. Hence, your formula has to be flexible enough to consider A1:D100 range. (Note - There would be no blanks in between the values in the Grid)

Download the workbook related to this from Challenge 32 - Convert Matrix into Linear Column

1

The solution to this problem will be published after a month i.e. on 18-Jan-16.

Mon 13 Jul 2015

Solution - Challenge 14 – Student Scoring Highest Marks in a Subject

By |Monday, July 13th, 2015|Categories: Solutions|Tags: , , , , |0 Comments

A proposed solution is listed below for the challenge - Challenge 14 – Student Scoring Highest Marks in a Subject

Put following Array formula in N2

=INDEX(A2:A20,MATCH(1,--(MMULT(--(MAX(B2:K20)=B2:K20),TRANSPOSE((COLUMN(B2:K20)^0)))>0),0))

Put following Array formula in N3

=INDEX(B1:K1,MATCH(MAX(B2:K10),OFFSET(A1,MATCH(1,--(MMULT(--(MAX(B2:K20)=B2:K20),
TRANSPOSE((COLUMN(B2:K20)^0)))>0),0),1,1,COLUMNS(B2:K10)),0))

Put following (non Array) formula in N4

=MAX(B2:K20)

The solution workbook can be downloaded from Solution - Challenge 14 – Student Scoring Highest Marks in a Subject

Mon 13 Jul 2015

Solution - Challenge 12 – Student Scoring Maximum Total Score

By |Monday, July 13th, 2015|Categories: Solutions|Tags: , , , , , , , |0 Comments

Below is a proposed solution for the challenge Challenge 12 – Student Scoring Maximum Total Score

Put following Array formula in N2

=INDEX(A2:A20,MATCH(MAX(MMULT(B2:K20,TRANSPOSE(COLUMN(B2:K20)^0))),
MMULT(B2:K20,TRANSPOSE(COLUMN(B2:K20)^0)),0))

Put following Array formula in N3

=MAX(MMULT(B2:K20,TRANSPOSE(COLUMN(B2:K20)^0)))

The solution sheet for this can be downloaded from Solution - Challenge 12 – Student Scoring Maximum Total Score