Since Excel Tips have been most sought after posts from a productivity part, I thought let me introduce an interesting tip in this blog post. Have you ever had a requirement to get the “Average of Top 5” or “Sum of Top 5” values from a list? What is the most common method that you used? I have seen most people use the sort function to order the values in descending order and just select the range of values to find SUM, Average and so on. So this post is a twist to that requirement. You can also apply the tip to other functions like Average which makes this post even more interesting.

Our Dataset

The dataset is simple. Seen below is a list of some random Sales figures and our requirement is to get the SUM of Top 5 Sales figures in D3 cell.

To start with, let us first find our top Sales figure first. This can be done using the LARGE function. The second argument “k” is to give the offset. Value of 1 means we need the top sales number. This is shown below.

The output can be seen as below:

Duplicate LARGE Function

Now that we figured out how to get the 1st, 2nd, 3rd and so on. We can just duplicate the function on the same range and make our SUM. It is as simple as it gets.

As you can see this for some reasons doesn’t look right. We seem to be duplicating and if we again look at the LARGE function. It takes just a value and doesn’t take a range.

Using SUMPRODUCT

Since SUM is taking in only values and not arrays. We need to find a way to pass some sort of array. Here we have SUMPRODCT function that takes in a set of array to work with. And to take a range of values top 5 (1+2+3+4+5), I have created a temp range with these values.

Super cool – Isn’t it?

What will happen if we do the same with SUM function?

You can see that for some strange reason the top 1 value was returned and it didn’t do a SUM for the array.

Introducing CTRL+SHIFT+ENTER

Here is the magic shortcut (CTRL+SHIFT+ENTER) – Edit our cell from above “=SUM(LARGE(B3:B10, C2:C7))” and press our magic shortcut.

Now there is something interesting that happens. Now the SUM of values is done for an array of values. The CTRL+SHIFT+Enter has many interesting use. Here I have shown one of them.

Since the array is shown using the {} – this doesn’t work if add the parenthesis manually. A small extension to this formulae is to add the array of {1,2,3,4,5} manually and then hitting our magic key CTRL+SHIFT+Enter also can do the trick.

Hope you enjoyed this tip and do let me know if you have used this in the past anytime. I will try to bring some more use of arrays in future posts.