Below is a possible solution to the challenge Challenge 38 – Formula for Top 5. Enter following formula in F2 and drag down – =IF(COUNTIF($B$2:$B$100,">="&LARGE(IF(FREQUENCY($B$2:$B$100,$B$2:$B$100)<>0, $B$2:$B$100),5))>=ROWS($1:1),INDEX($A$2:$A$100,MATCH(1,INDEX(($B$2:$B$100= LARGE($B$2:$B$100,ROWS(F$1:F1)))*(COUNTIF(F$1:F1,$A$2:$A$100)=0),),0)),"") Enter following formula in G2 and drag down – =IF(COUNTIF($B$2:$B$100,">="&LARGE(IF(FREQUENCY($B$2:$B$100,$B$2:$B$100)<>0, $B$2:$B$100),5))>=ROWS($1:1),INDEX($B$2:$B$100,MATCH(1,INDEX(($B$2:$B$100= LARGE($B$2:$B$100,ROWS(F$1:F1)))*(COUNTIF(F$1:F1,$A$2:$A$100)=0),),0)),"") The workbook containing the solution can be downloaded from Solution – Challenge 38 – Find Top…
Category: Challenges
Challenge 40 – Cryptography Challenge 2 – Fully Functional Caesar’s Shift Cipher
We made ourselves familiarized with Caesar's Shift in last challenge – Challenge 39 – Cryptography Challenge 1 – Caesar's Shift Cipher That challenge was for writing a formula to perform Caesar's Shift on a single alphabet. But now, we need to have a fully functional Caesar's Shift Cipher. The challenge before you is to write…
Solution – Challenge 37 – Find nth Alphabet from the Bottom
Below is a possible solution to the challenge Challenge 37 – Find nth Alphabet from the Bottom. Put following formula as Array formula in E1 =IF(SUM(–(ABS(77.5-IFERROR(CODE(UPPER(A1:A100)),0))<13))>=C1, INDEX(A1:A100,LARGE((ABS(77.5-IFERROR(CODE(UPPER(A1:A100)),0))<13) *ROW(A1:A100),C1)),"") Note – Array Formula is not entered by pressing ENTER after entering your formula but by pressing CTRL+SHIFT+ENTER. If you are copying and pasting this formula, take…
Challenge 39 – Cryptography Challenge 1 – Caesar's Shift Cipher
This is time for a cryptography challenge. The simplest cipher in cryptography is Caesar's Shift. More about this can be read here (though it will not be needed for this challenge) – https://en.wikipedia.org/wiki/Caesar_cipher For a given alphabet, it simply substitutes by another alphabet by a fixed number of position. Hence, if shift position is 5,…
Solution – Challenge 36 – Generate Triangular Numbers
Below is a possible solution to Challenge 36 – Generate Triangular Numbers. Enter below formula anywhere and drag down – =IF(ROWS($1:1)=1,1,INDIRECT(ADDRESS(ROW()-1,COLUMN()))+ROWS(($1:1))) A workbook containing the above solution can be downloaded from Solution – Challenge 36 – Generate Triangular Numbers. Edit – 23-Aug-16 – A better solution is to use below formula and drag down =ROWS($1:1)*(ROWS($1:1)+1)/2
Challenge 38 – Formula for Top 5
The challenge, this time, is to write a formula to come up with names and marks for Top 5 in descending order. Below is the original table and answer expected. The workbook related to this challenge can be downloaded from Challenge 38 – Find Top 5 The solution to this challenge will be published after…
Solution – Challenge 35 – Validate 16 Digits Credit Card Numbers through Mod 10 Algorithm
Below is a possible solution to the challenge – Challenge 35 – Validate 16 Digits Credit Card Numbers through Mod 10 Algorithm. Enter the below formula as Array formula =MOD(SUM(IF((MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))*MOD(ROW(INDIRECT("1:"&LEN(A1))),2) =0,0,MOD((MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))*MOD(ROW(INDIRECT("1:"&LEN(A1))),2)*2-1,9) +1)+(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))*MOD(ROW(INDIRECT("1:"&LEN(A1)))-1,2)),10)=0 Note – Array Formula is not entered by pressing ENTER after entering your formula but by pressing CTRL+SHIFT+ENTER. If you are copying and…
Challenge 37 – Find nth Alphabet from the Bottom
Suppose, you have been given a layout as below. The challenge before you is to write a formula to find nth alphabet from bottom. If no criterion is met, blanks should be returned. C1 hold the value of that nth. For C1 = 5, answer would be k. for C1 = 10, answer would be…
Solution – Challenge 34 – Increment All Digits by 7 and Perform Numerological Sum of the Digits
Below is a possible solution to the challenge – Challenge 34 – Increment All Digits by 7 and Perform Numerological Sum of the Digits. =SUMPRODUCT((MOD((MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)+ 7*ROW(INDIRECT("1:"&LEN(A1)))^0)-1,9)+1)*10^(LEN(A1) -ROW(INDIRECT("1:"&LEN(A1))))) The workbook containing the above solution can be downloaded from Solution – Challenge 34 – Increment All Digits by 7 and Perform Numerological Sum of the Digits.
Challenge 36 – Generate Triangular Numbers
If interested in details about Triangular Numbers, you can refer to following link (though it is not needed and one look at the sequence, you will understand what it is) https://en.wikipedia.org/wiki/Triangular_number It is, basically, the following sequence – 1, 3, 6, 10, 15, 21, 28, 36, 45, 55, 66, 78, 91, 105, 120, 136, 153,…