Below is a possible solution to the challenge – Challenge 48 – Whether a Sentence is Pangram or Not =IF(ISNUMBER(SUMPRODUCT(MATCH(ROW($65:$90),INDEX(CODE(MID(SUBSTITUTE(SUBSTITUTE( SUBSTITUTE(UPPER(A1)," ",""),".",""),",",""),ROW(INDIRECT("1:"&LEN(SUBSTITUTE(SUBSTITUTE( SUBSTITUTE(A1," ",""),".",""),",","")))),1)),,),0))),"Pangram","Not Pangram") Update on 10-Aug-21: The above can be done with a simpler formula =IF(ISNUMBER(MATCH(FALSE,ISNUMBER(SEARCH(CHAR(ROW(97:122)),A2)),0)), "Not Pangram","Pangram") You will have to confirm the above formula with CTRL+SHIFT+ENTER i.e. as an array formula…
Category: Challenges
Challenge 50 – Cryptography Challenge 3 – Generate Tabula Recta
Tabula Recta is supposed to be one of the foundation stones of cryptography. This was one of the earliest forms of encryption. The more about this can be read at https://en.wikipedia.org/wiki/Tabula_recta One look at below and you will understand what it is. In upcoming challenges in future, Tabula Recta will be used. The first row…
Solution – Challenge 47 – Generate Pentagonal Series
Below is a possible solution to the problem – Challenge 47 – Generate Pentagonal Series Put following formula and drag down – =ROWS($1:1)*(3*ROWS($1:1)-1)/2
Challenge 49 – Whether a Word is Isogram or Not
This time, continuing my effort to make challenge on English language, the challenge is on Isogram. An Isogram is that word in English which doesn't repeat any alphabet. For example: "Botany" is an Isogram but "Britain" is not as "i" repeats. Hence, this time, you need to make a formula which returns "Isogram" if the…
Solution – Challenge 46 – Compute Numerological Sum for a Name
Below is a possible solution to the challenge – Challenge 46 – Compute Numerological Sum for a Name The formula to calculate Numerological Sum for a Name would be – =MOD(SUMPRODUCT(MOD(CODE(MID(SUBSTITUTE(LOWER(A1)," ",""), ROW(INDIRECT("1:"&LEN(SUBSTITUTE(A1," ","")))),1))+2,9)+1)-1,9)+1
Challenge 48 – Whether a Sentence is Pangram or Not
This time, I decided to pose a challenge on English words. A Pangram is that sentence in English which uses all 26 alphabets of English language at least once. A very common example is the very famous sentence "The quick brown fox jumps over the lazy dog" which contains all alphabets of English language at…
Solution – Challenge 45 – Number of Days Passed in a Quarter
Below is a possible solution to the challenge – Challenge 45 – Number of Days Passed in a Quarter The formula to calculate number of days passed in a quarter is =A1-DATE(YEAR(A1),(ROUNDUP(MONTH(A1)/3,0)-1)*3+1,1)
Challenge 47 – Generate Pentagonal Series
Pentagonal number series is following – 1, 5, 12, 22, 35, 51, 70, 92, 117, 145, 176…. You need to write an Excel formula which can be dragged down and generates the above sequence. The solution to this problem will be published after a month i.e. on 16-Aug-16.
Solution – Challenge 44 – Hyperlink to a cell in another worksheet by specific text (not cell number)?
Below is a possible solution to the challenge – Challenge 44 – Hyperlink to a cell in another worksheet by specific text (not cell number)? Enter the below formula in B1 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,…
Challenge 46 – Compute Numerological Sum for a Name
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….