This will be a short lesson on three great and sadly unknown functions and how to use them as an array function.
One of my favorite questions to propose to listeners is “Give me the eighth largest value from an array”. This is one of the simplest ways to put an otherwise very “Excel knowledgeable” person in a pickle. If you say the largest value, everyone will spit out Max, but if you say second largest, suddenly they are calling Houston 🙂
Well this problem is easily solvable if you know one single function…
LARGE is a very straightforward and easy to use function. All it’s supposed to do is to return the nth largest number from an Array.
=LARGE(Array,n)
Excel function LARGE also has an evil twin sister called SMALL and you will never guess what it does :). Ok, maybe you could guess, but I’ll just tell you. It returns the nth smallest value from an Array.
=SMALL(Array,n)
ROW is a very simple function if you use it on a cell. ROW returns the number of the ROW that that cell occupies. So =ROW(A28) would return 28.
One great trick and also one of the greatest uses is to use it blank, so without any arguments. You simply put =ROW() into a cell and it returns the number of a row that that cell occupies.
But you can also use it as an Array formula if you write something like {=ROW(1:3)}. You will get a vector of numbers from 1 to 3, that is {1,2,3}. Don’t be fooled by the {} curly brackets in a {=ROW(1:3)} formula, it’s not something you write with the formula, it’s something Excel Adds to the formula when you press Ctrl+Shift+Enter to enter the formula into the cell. Ctrl+Shift+Enter is the way you enter an Array formula into a Cell.
Well now we have all we need…
To achieve this, we will use the following formula
=SUM(LARGE(Array,ROW(1:10)))
You must(!!!) enter this formula with a Ctrl+Shift+Enter combination (because it’s an Array formula) and excel will change it into
{=SUM(LARGE(Array,ROW(1:10)))}
The calculation goes as follows.
First the ROW(1:10) is resolved into
=SUM(LARGE(Array,{1,2,3,4,5,6,7,8,9,10}))
Then this 1 to 10 vector is placed in LARGE and you get a vector with the first largest, second largest,…
=SUM({1000,999,998,997,996,995,994,993,992,991})
Now all that remains is for the SUM function to sum up the numbers.
With the use of functions described above, you can easily extend this to the sum of bottom thirty values or an average of seven largest and so one…
This brings us one step closer to eternal happiness once more!
The title of this post is actually a question I got on one of the ...
As promised in the Pragmatism vs. Purity in Excel article from ...
A task that comes about every so often in Excel and can be quite ...