A proposed solution is listed below for the challenge – Challenge 13 – Generating Digit Product Sequences Put a starting seed in A1. Put following formula in A2 and drag down – =A1+PRODUCT(INDEX(–MID(SUBSTITUTE(A1,0,1),ROW(INDIRECT("1:"&LEN(A1))),1),,)) For a seed value of 1 in A1, it will generate following sequence – 1, 2, 4, 8, 16, 22, 26, 38,…
Solution – Challenge 12 – Student Scoring Maximum Total Score
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
Solution – Challenge 11 – Generate a Repeating Number Sequence – II
Below is a proposed solution for challenge – Challenge 11 – Generate a Repeating Number Sequence – II Put following formula and drag down – =ROUNDUP(ROWS($1:1)/3,0) This will repeat the sequence 111222333444555………..
Solution – Challenge 10 – Generate a Repeating Number Sequence – I
Below is the proposed solution for challenge – Challenge 10 – Generate a Repeating Number Sequence – I Put following in a cell and drag down – =MOD(ROWS($1:1)-1,5)+1 This will generate a sequence 1, 2, 3, 4, 5 and will repeat this.
Solution – Challenge 9 – Get the Customer Spending Maximum Amount for a given Month and Starting Alphabet
Below is a possible solution for the challenge – Challenge 9 – Get the Customer Spending Maximum Amount for a given Month and Starting Alphabet Put following Array formula in H4 =INDEX(D2:D20,MATCH(1,–(MAX(IF(C2:C20=TEXT(H2&"/1","mmm"),IF(LEFT(D2:D20,1)=H3,E2:E20))) =(IF(C2:C20=TEXT(H2&"/1","mmm"),IF(LEFT(D2:D20,1)=H3,E2:E20)))),0)) Put following Array formula in H5 =INDEX(E2:E20,MATCH(1,–(MAX(IF(C2:C20=TEXT(H2&"/1","mmm"),IF(LEFT(D2:D20,1)=H3,E2:E20))) =(IF(C2:C20=TEXT(H2&"/1","mmm"),IF(LEFT(D2:D20,1)=H3,E2:E20)))),0)) Note – Array Formula is not entered by pressing ENTER after entering your formula…
Solution – Challenge 8 – Counting Number of single 1s in Rows in a Grid
Below is a proposed solution for the challenge – Challenge 8 – Counting Number of single 1s in Rows in a Grid Enter below Array formula – =SUM(–(MMULT(–(A1:E10=1),TRANSPOSE(COLUMN(A1:E10)^0))=1)) Note – Array Formula is not entered by pressing ENTER after entering your formula but by pressing CTRL+SHIFT+ENTER. If you are copying and pasting this formula, take…
Solution – Challenge 7 – Formula for Sum of Square of the Digits in an Alphanumeric String
Below is a possible solution to the challenge – Challenge 7 – Formula for Sum of Square of the Digits in an Alphanumeric String 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…
Solution – Challenge 6 – Non-array Formula for Sum of Square of the Digits in a Numeric String
Below is a possible solution for the challenge – Challenge 6 – Non-array Formula for Sum of Square of the Digits in a Numeric String =IFERROR(SUMPRODUCT(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)^2),"")
Solution – Challenge 5 – Prepare a Graphical Chart
Below is a possible solution to the challenge – Challenge 5 – Prepare a Graphical Chart 1. Download the Challenge file – Tiger Count 2. Right click on Tiger's picture > Cut > Open Microsoft Paint or any other picture editor> Paste > Save the picture 3. Select A1:B7 in Tiger Count file. 4. Create a…
Solution – Challenge 4 – SUM of Multiplication of Preceding Digits by Succeeding Digits
Below is a possible solution to the challenge –Challenge 4 – SUM of Multiplication of Preceding Digits by Succeeding Digits Given your numerical string is in A1, use following formula – =IFERROR(SUMPRODUCT((MID(A1,ROW(INDIRECT("1:"&LEN(A1)-1)),1)*MID(A1,ROW(INDIRECT("2:"&LEN(A1))),1))),0) The answer would be as follows