66. Get Column Range for a Column Number
Let's suppose, you have a number in A1 and you want to get the column range for that.
Hence, if A1=1, you want "A:A"
Hence, if A1 =26, you want "Z:Z"
Hence, if A1=27, you want "AA:AA" and so on.
The formula to derive the column range would be be -
=SUBSTITUTE(ADDRESS(1,A1,4)&":"&ADDRESS(1,A1,4),1,"")
67. Find the nth Largest Number when there are duplicates
You know the LARGE function which can find the nth largest value. Hence, if you have a
series like below -
And you give =LARGE(A1:A10,3), you get the answer as 18
Now, if we have a series like below
Now, you give =LARGE(A1:A10,3) and now the result is 24. The reason is that large function
gives the nth largest value in a sorted array. Hence, LARGE function will sort the above array
as {24,24,24,22,22,18,18,9} and 3rd largest is 24.
But actually you want the unique 3rd largest which is 18 as the answer.
The formula for such case would be
