I had posted Tips & Tricks 119 – Numerology Sum of the Digits aka Sum the Digits till the result is a single digit. In this, I had explored how to add a number and arrive at a single digit. For example, if you have to add 8 + 7 the answer would be 15….
Category: Challenges
Solution – Challenge 43 – Consolidate Daily Sheets into a Weekly Sheet
Below is a possible solution to the challenge – Challenge 43 – Consolidate Daily Sheets into a Weekly Sheet 1. Create a Sheet named Project which can hold all the projects. In that sheet, enter following formula in A2 and copy down till row 141 in columns A, B and C (as every sheet can…
Challenge 45 – Number of Days Passed in a Quarter
This time the challenge is – If a date is given, what would be the formula to find the number of days passed in a quarter. If A1 has the value 12-Mar-16, then 31 days in Jan, 29 days in Feb (2016 is a leap year) and 11 days in Mar = 31+29+11 = 71…
Solution – Challenge 42 – Split a Sentence Into Words in Different Cells
Below is a possible solution to the challenge – Challenge 42 – Split a Sentence Into Words in Different Cells Enter following formula in B1 and drag to the right and down. =IFERROR(MID($A1,FIND("@",SUBSTITUTE(" "&$A1&" "," ","@",COLUMNS($A:A))), FIND("@",SUBSTITUTE(" "&$A1&" "," ","@",COLUMNS($A:B)))-1- FIND("@",SUBSTITUTE(" "&$A1&" "," ","@",COLUMNS($A:A)))),"") The workbook containing this solution can be downloaded from Solution –…
Challenge 44 – Hyperlink to a cell in another worksheet by specific text (not cell number)?
You have a grid of English words in Sheet2. In A1 of Sheet1, you have been given a word. You need to create a Hyperlink through formula in B1 of Sheet1. The Hyperlink which says "Take to the Word – Discover" if the word was Discover in A1. "Take me the Word – Pretty" if…
Solution – Challenge 41 – Sum the Maximum Number where duplicates Exist
Below is a possible solution to the challenge Challenge 41 – Sum the Maximum Number where duplicates Exist 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 pasting and CTRL+SHIFT+ENTER. This will put…
Challenge 43 – Consolidate Daily Sheets into a Weekly Sheet
This time, challenge is going to be tougher. An user receives a daily sheet which has got project status and you need to prepare a consolidated worksheet for the week everyday. 1. Your sheets are named Day1, Day2…..Day7. 2. One sheet can have a maximum of 20 entries. 3. The entries may not be same…
Solution – Challenge 40 – Cryptography Challenge 2 – Fully Functional Caesar’s Shift Cipher
Below is a possible solution to the challenge – Challenge 40 – Cryptography Challenge 2 – Fully Functional Caesar’s Shift Cipher 1. Make a backup of your workbook 2. Open your workbook and ALT+F11 3. Locate your Workbook name in Project Explorer Window 4. Right click on your workbook name > Insert > Module 5….
Challenge 42 – Split a Sentence Into Words in Different Cells
This time the challenge before you is to write a formula that if a sentence is given in A1, your formula if dragged to the right should split the sentence into words. Remember, it has to be a single formula which should be put into B1 and dragged to the right. See the examples below…
Solution – Challenge 39 – Cryptography Challenge 1 – Caesar’s Shift Cipher
Below is a possible solution to the challenge Challenge 39 – Cryptography Challenge 1 – Caesar’s Shift Cipher. Put below formula in G2 and drag down – =IFERROR(CHAR(IF(CODE(LOWER(D2))+$B$3>122,CODE(D2)+$B$3-26, CODE(D2)+$B$3)),"") The workbook containing the solution can be downloaded from Solution – Challenge 39 – Caesar's Shift Cipher.