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…
Category: Challenges
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.
Challenge 41 – Sum the Maximum Number where duplicates Exist
Suppose, you have been given following and you will need to find duplicates in column A and sum the maximum values from column B. If duplicates don't exist, values will be taken as they are. The values which needs to be summed up are colored. The answer would be 123 in this. The workbook related…