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…
Tips & Tricks 97 – Force Text to Columns Wizard to Appear for CSV Files
You have a csv file and 1. You double click it, the file will be opened directly in Excel. 2. You opened the file from File > Open but still the file opens directly in Excel. The solution is to change the extension of file from csv to txt. Hence, if you are having a…