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,…
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…