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…
Author: Vijay A. Verma
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,…
Tips & Tricks 132 – Financial Year Formula (e.g. 2015-16 or FY16)
A good number of countries don't follow calendar year as the financial year. For example, India's financial year start is 1-Apr and finishes on 31-Mar. Hence, currently (20-Feb-16), the financial year is 2015-16 (It is also written as FY16). On 1-Apr-16, it will become 2016-17 (It is also written as FY17). Now if a date…
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
Article 37 – VBA – Generating Unique (Non-repeating) Random Numbers Efficiently
This article is about how to generate unique or non-repeating random numbers given two limits. Hence, if lower limit is 10 and maximum limit is 100, hence, it should generate random numbers between 10 and 100. The algorithm to ensure uniqueness is following – 1. Given lower limit and upper limit, generate all numbers and…
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…
Excel Quiz 31
Tips & Tricks 131 – Repeat a Number and Increment and Repeat….
Suppose, you have been given the task of repeating a number and increment that number and repeat it. For example – 1,1,1,1,2,2,2,2,3,3,3,3…..(Here, we are repeating it 4 times and incrementing and repeating 4 times again and so on) Then you can use following formula =ROUNDUP(ROWS($1:1)/4,0) Suppose, you want to start the number with 5 not…