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.
Category: Challenges
Challenge 35 – Validate 16 Digits Credit Card Numbers through Mod 10 Algorithm
This time challenge is to come up with a formula (or a VBA function though formula is preferred) to give the result of TRUE or FALSE if a 16 digit credit card number can be validated or not using Mod 10 algorithm. The credit card numbers will be stored as Text as Excel can't contain…
Solution – Challenge 32 – Convert Matrix into Linear Column – I
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))))
Challenge 34 – Increment All Digits by 7 and Perform Numerological Sum of the Digits
I had posted a relatively simpler problem Challenge 31 – Increment All Digits by 1 which talked about adding 1 to all digits i.e. if your number is 7409, you needed to add 7409+1111 and come up with the answer of 8520. I had also posted Tips & Tricks 119 – Numerology Sum of the Digits…
Solution – Challenge 31 – Increment All Digits by 1
Below is a possible solution to the problem Challenge 31 – Increment All Digits by 1 Use below formula – =A1+REPT(1,LEN(A1))
Challenge 33 – Convert Matrix into Linear Column – II
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…
Solution – Challenge 30 – Average Last 5 Numbers in a Range
Below is a possible solution to Challenge 30 – Average Last 5 Numbers in a Range. Use below formula – =IFERROR(AVERAGE(INDIRECT("A"&LARGE(INDEX(ISNUMBER(A1:A100)* (ROW(A1:A100)),,),MIN(COUNT(A1:A100),5))&":"&"A"&LARGE( INDEX(ISNUMBER(A1:A100)*(ROW(A1:A100)),,),1))),0) The workbook containing this solution is uploaded to Solution – Challenge 30 – Average Last 5 Numbers in a Range
Challenge 32 – Convert Matrix into Linear Column – I
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…
Solution – Challenge 29 – Reverse (Flip) a Number String
Below is a possible solution to the challenge – Challenge 29 – Reverse (Flip) a Number String You may use below formula to reverse a number string – =SUMPRODUCT(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1) *10^ROW(INDIRECT("1:"&LEN(A1)))/10)
Challenge 31 – Increment All Digits by 1
This time's challenge is a short one but it may take some time to think through. Suppose, you have a number in cell A1. The challenge is to add 1 in all digits and come with a result. Hence, If A1=28, result would be 39. If A1 = 123, result would be 123+111 = 234…