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
Solution – Challenge 3 – Generate a Chessboard in Excel
Below is a possible solution to problem posed in Challenge 3 – Generate a Chessboard in Excel 1. Select B2 to I9 starting with B2. 2. Home tab > Conditional Formatting > New Rule > Use a formula to determine which cells to format and put following formula and choose Black Fill Colour =MOD(ROW()+COLUMN()+1,2)
Solution – Challenge 2 – Sum of Numbers in the Diagonal of a Square
Below is a proposed solution for the challenge – Challenge 2 – Sum of Numbers in the Diagonal of a Square Put following formula for the answer – =IF(L2="B",SUMPRODUCT((OFFSET(H8,0,0,-MIN(8,L1),-MIN(8,L1)))*(ROW(OFFSET(H8,0,0,-MIN(8,L1),-MIN(8,L1)))=COLUMN(OFFSET(H8,0,0,-MIN(8,L1),-MIN(8,L1))))),SUMPRODUCT((OFFSET(A1,0,0,MIN(8,L1),MIN(8,L1)))*(ROW(OFFSET(A1,0,0,MIN(8,L1),MIN(8,L1)))=COLUMN(OFFSET(A1,0,0,MIN(8,L1),MIN(8,L1)))))) The worksheet for the solution can be downloaded from Solution – Challenge 2 – Sum of Numbers in the Diagonal of a Square
Solution – Challenge 1 – Single Formula for Fibonacci Numbers
This is a possible solution to Challenge 1 – Single Formula for Fibonacci Numbers There can be many solutions to a given problem in Excel. Below are solutions proposed by me – A1 and drag down – =IF(ROW()<3,ROW()-1,SUM(OFFSET($A$1,ROW()-3,0,2))) =IF(ROW()<3,ROW()-1,SUM(INDIRECT("A"&ROW()-2&":A"&ROW()-1))) If you belong to that class where you worry about volatality of OFFSET and INDIRECT, use…
Challenge 20 – Find Number of Friday the 13th between Two Given Dates
I had read the example of this problem in Mike Girvin's book on Array Formulas. So, I am posing this challenge here for my readers. Suppose, you have two given dates – A1: 1/1/2013 A2: 12/31/2015 The challenge before you is to write a formula to find the the days which were Friday the 13th….