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…
Downloads 02 – Template 02 – A Highly Customizable Perpetual Yearly Calendar Template in Excel
The second template in the series is a calendar. Just download it once and use every year. Following are the features – 1. The calendar is perpetual in nature. It supports all the years from 1900 to 9999. You just need to change the year every year or you can leave the year field blank….
Tips & Tricks 134 – Last Working Day of the Year
If a year is given in A1 say 2016, below formula can be used to know the last working day of the year (format the result as date) =WORKDAY("1JAN"&A1+1,-1) The above formula assumes that your weekends are Saturday and Sunday. But, if your weekends are different (e.g. in gulf countries), you can use following formula…
Article 38 – 10 Features I would like to see in Excel
Like all of you, I am dependent upon day to day work in Excel. Excel is a software which is like ocean, unlimited in bounds. It has almost all features but it doesn't have few which everybody cherishes. All of us want something or the other. Below is a list of features which I would…
Solution – Challenge 38 – Formula for Top 5
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…
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…
Tips & Tricks 133 – First Working Day of the Year
If a year is given in A1 say 2016, below formula can be used to know the first working day of the year (format the result as date) =WORKDAY(EOMONTH("1JAN"&A1,-1),1) The above formula assumes that your weekends are Saturday and Sunday. But, if your weekends are different (e.g. in gulf countries), you can use following formula…
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,…