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….
Excel Quiz 16
Tips & Tricks 99 – Remove Middle Name
Suppose, you have been given a name in A1 = Roberto Albert Carlos and you need to remove the middle name. Hence, you want to have an answer Roberto Carlos here. Formula Way – The formula for the above case would be =IF(COUNTIF(A1,"* * *"),LEFT(A1,FIND(" ",A1&" "))&TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))),LEN(A1))),"") OR =IF(COUNTIF(A1,"* * *"),REPLACE(A1,FIND(" ",A1)+1,FIND(" ",A1,FIND("…
Tips & Tricks 98 – Extract Middle Name
Suppose, you have been given a name in A1 = Roberto Albert Carlos and you need to extract the middle name. We have already talked about extracting first name and last name in following Tips and Tricks. We also talked about extracting initial of a middle name. Tips & Tricks 22 – Extract First Name…
Excel Quiz 15
Article 17 – 3D Formulas – Hidden Wonder of Excel
3D formulas are one of the hidden wonders of Excel and not many of us know about the secret of this. The purpose of this article is to unravel the mystery of 3D formulas in Excel. Suppose you have 4 worksheets called Quarter1, Quarter2, Quarter3, Quarter4 as below. And you want to find the total of…