Assuming we have a data set and we have been asked to calculate the max and average of a particular column. Show - Dynamic array functions
- INDEX / SORT / SEQUENCE function combination (Excel 2021 & Excel 365)
- CHOOSECOLS / CHOOSEROWS / SORT / SEQUENCE function combination (Excel 365 only)
- Dynamic arrays with criteria
- Traditional functions
- Using the LARGE function
- Finding the labels for the top 10
- Finding the labels when the top 10 values are not unique
- Bottom 10 values
- Adding criteria
How do we do it? Let us see how to do it using LINQ !!! MAX Value: ## ExampleImplementation using UiPath : Let us implement a workflow which takes a sample DataTable and displays the Max and Average of a particular column of that DataTable Step 1: Drag “Build Data Table” activity into the design panel and populate it with some sample data. Step 2: Drag “Message Box” activity into the design panel to display the Max value of Sales Column and populate it with the below code. dt_Sample.AsEnumerable().Max(Function(row) cint(row(“Sales”))) Step 3: Drag “Message Box” activity into the design panel to display the Average value of Sales column and populate it with the below code. dt_Sample.AsEnumerable().Average(Function(row) cint(row(“Sales”))) Step 4: Finally, let’s run the project. Max Value: Average Value: Click here to download the Source Code… Hope it has helped you…
I am an engineer by profession and by heart :) It's been 7+ years since I have been working with software development and robotic process automation. I have implemented close to 80+ RPA processes in total by using Uipath. It is so true that someone learns more by not reading something but by practicing the skill. I have been trying to understand exactly where people who are just starting out their career struggle and understand the pain of struggling for hours trying to figure something out.I can help you with that. Having a passion to share my piece of content, I have created step-by-step easy-to-digest courses. My goal is to help you get ready for the future by learning new talents and becoming more productive by providing you with relevant and useful courses. I'm still learning and exploring my field of work :) Every feedback should be as valuable as you are to me, please feel free to reach out to me if you have any feedback, questions, or need any help.Thank You... View all posts by Sharath Raju Top 10 lists are a common form for displaying information, especially on dashboards and summarized reports. It is easy to create a top 10 in Excel when working with sorted data; just cell link to the top 10 items in the list… easy! It’s also relatively simple when using AutoFilter, Tables, and PivotTables, as it is a default filter setting within these features. However, creating a top 10 with formulas on a non-sorted dataset, things become a little bit tricky. But, formulas are the most flexible method for reporting in Excel. Therefore, in this post, I will show you exactly how to calculate a top 10 list. Through these methods, you’re not restricted to a top 10; you can create a top 5, top 8, or any number you choose. Since the introduction of dynamic array calculations in Excel, we have a new and easier method for creating the top 10 list. Therefore we will start by looking at dynamic arrays. However, don’t worry if you have Excel 2019 or prior, we will cover a solution for that too. Table of Contents Download the example file: Join the free Insiders Program and gain access to the example file used for this post. File name: 0015 Top 10 using formulas.xlsx Note: If you don’t have a Dynamic Array-enabled version of Excel, the tabs containing those examples will display errors. Watch the video: Note: The blog post has been updated to include the latest dynamic array formulas; these are not included in the video. Watch the video on YouTube ## Dynamic array functionsIf you have a dynamic array enabled version of Excel (Excel 365 and Excel 2021), it’s excellent news, as you don’t need to rely on complicated formulas. Instead, dynamic arrays make it easy. ## INDEX / SORT / SEQUENCE function combination (Excel 2021 & Excel 365)Start by looking at the Top 10 – DA tab in the example file. In our example file, there are 26 customers, with their corresponding locations and values. In cell F4 the formula is:
And that’s it! It’s a simple solution. No need for complex formulas, no need to press Ctrl+Shift+Enter, and no need to copy formulas down. Let’s dig into this a bit deeper to understand how it works. ## SORTSORT has four arguments:
- array: The range of cells, or array of values to be sorted.
- [sort_index]: The nth column or row to apply the sort to. For example, to sort by the 3rd column, the sort index would be 3.
- [sort_order]: 1 = sort in ascending order; -1 = sort in descending order (if excluded, the argument defaults to 1).
- [by_col]: TRUE = sort by columns; FALSE = sort by rows (if excluded, the argument defaults to FALSE).
In our formula, we used the following SORT.
This sorts cells A2 to C27 on the 3rd column in descending order. Find out more about the SORT function in this article: SORT Function in Excel. ## SEQUENCEThe SEQUENCE function has four arguments:
- rows: The number of rows to return
- [columns]: The number of columns to return. If excluded, it returns a single column.
- [start]: The first number in the sequence. If omitted, it starts at 1.
- [step]: The amount to increment each subsequent value. If excluded, each increment is 1.
In our example, the SEQUENCE function creates a list from 1 to 10. We only need the first argument; we can use the default options for the remaining arguments.
This formula evaluates to an array {1;2;3;4;5;6;7;8;9;10} Find out more about the SEQUENCE function in this article: SEQUENCE function in Excel. ## INDEXLet’s use both SORT and SEQUENCE into a traditional INDEX function:
The formula calculates the first 10 results from the SORT and returns columns 1 and 3. That was so easy… right. ## CHOOSECOLS / CHOOSEROWS / SORT / SEQUENCE function combination (Excel 365 only)If you have Excel 365, we have access to even more dynamic array formulas. Two of these are CHOOSECOLS and CHOOSEROWS, which we can use instead of the INDEX function. The formula in cell K4 is:
The formula may be longer than the INDEX alternative but it is more flexible. Let’s take a look at the CHOOSEROWS and CHOOSECOLS functions. ## CHOOSEROWS and CHOOSECOLSThe CHOOSEROWS and CHOOSECOLS return only the specified rows or columns from an array. They have similar syntax and only have 3 arguments:
- array: The array containing the rows or columns to be returned
- row_num1 / col_num1: The first row/column to be returned
- [row_num2] / [col_num2] : Additional row / column numbers to be returned
In our formula, we used the following:
This takes the sorted values from cells A2:C27 (sorted by column 3 in descending order) and only returns columns 1 and 3. Next, we wrap the result inside a CHOOSEROWS function.
This takes the previous result from CHOOSECOLS and returns only the first 10 rows. ## Bottom 10To get the bottom 10, we only need to change the 3rd argument of the SORT function from -1 to 1. Look at cells F18 and K18 in the example file to see this in action. ## Dynamic arrays with criteriaEven if we have selection criteria to apply, it is still straightforward with dynamic arrays. Cell J4 contains the name of the city; London or Birmingham. We want the top 10 to only include customers in that city. Look at the Top 10 – DA with criteria tab in the example file. The formula in cell F4 is:
1 The only difference to the previous example is that we are using the FILTER function to include the matching items, before it is fed into the SORT function. ## FILTERFILTER has three arguments:
2 - array: The range of cells, or array of values to filter.
- include: An array of TRUE/FALSE results, where the TRUE values are retained in the result.
- [if_empty]: The value to display if no rows are returned.
In our example, the FILTER function is:
3 It returns cells A2 to C27, but only where the corresponding values from B2 to B27 equal the selected city in cell J4. Find out more about the FILTER function in this article: FILTER function in Excel. ## Bottom 10 with CriteriaLook at the example file (cells E17:H27) to see how a bottom 10 with criteria is calculated. ## CHOOSECOLS / CHOOSEROWS / SORT / SEQUENCE combination with criteriaIn the example file, you will also find the CHOOSECOLS / CHOOSEROWS / SORT / SEQUENCE combination in cell M18.
4 This combination of functions detailed above can be used with Excel 365 only. ## Working with multiple criteriaThe key to working with multiple criteria is the FILTER function. Look at the Top 10 – DA with multi tab in the example file. An additional criterion has been added to only include items with a value equal to the city OR less than 100. The formula in cell F4 is:
5 The filter criteria are highlighted above. We can use this approach to add as many filter criteria as we like. With the FILTER function, the plus symbol ( + ) creates OR logic; to create AND logic, we use the asterisk ( * ). This is shown in the example below.
6 ## Traditional functionsOK, now let’s move on to look at traditional functions (i.e., non-dynamic array). Using these, obtaining the top 10 values is not a bit issue. But the challenge is getting the names/labels that relate to those ten values. Start by looking at the Top 10 – non-DA tab of the example file. ## Using the LARGE functionIn our example file, there are 26 customers, with their values. We use the LARGE function to create a top 10 of these customers (without sorting the list). Cell G4 contains this formula:
7 LARGE has two arguments:
8 - range: The range of data to be analyzed.
- k: The nth item to be found.
In our example, the data range to analyze is cells C2 to C27. The k value is calculated as the row number minus the row number of the header row. This always calculates the relative row position in a range of cells. So, for example, the first row of data calculates as 1, the second row calculates as 2, and so on.
9 The formula in cell G4 is copied down to display the top 10 values. ## Finding the labels for the top 10We have the values, so now we just need to calculate the customer name for that value. We can’t use VLOOKUP as the customer name is to the left of the lookup value. So instead, we will use the INDEX/MATCH formula combination. Note: Since XLOOKUP was released after dynamic arrays, I have excluded this as an option. Cell F4, in our example, contains the following formula.
0 If this formula were copied down into cells F5:F13. Our worksheet would display as follows: Can you see the problem? Our top 10 values are not unique; there are 3 values of 80 (see the screenshot above). A basic INDEX/MATCH calculation only returns the first value; therefore, it finds the name Alfa 3 times. Yet, Echo and Tango, who also have 80, do not feature on the list. This is clearly an error, so how do we get around this? ## Finding the labels when the top 10 values are not uniqueTo solve the issue of finding labels with non-unique values, we turn to an advanced array formula. The formula in cell F4 should be:
1 Woah!!! That’s a big and complicated formula! Note: This is a special type of formula known as an array formula. Don’t put the curly brackets ( { } ) at the start or the end when typing the formula into the formula bar; when you press Ctrl+Shift+Enter, Excel adds the curly brackets by itself. Pressing Ctrl+Shift+Enter lets Excel know that it is an array formula. REMEMBER! – If you go back into an array formula to edit it, you need to press Ctrl + Shift + Enter to re-enter the formula. This formula behaves like INDEX/MATCH, but returns the 1st, 2nd, 3rd, 4th… or nth value. Let’s dig a bit deeper to understand how it works. ## Section 1 – IF functionIn the middle of the formula, we find an IF function.
2 In English, this says: If C2 = G4 then return the count of rows between C2 and C1. As this is an array formula, it automatically goes onto the next row and calculates again If C3 = G4 then return the count of rows between C3 and C1. And it keeps on going. If C4 = G4 then return the count of rows between C4 and C1. This includes all the cells from C2 to C27. For the formula in F4, the IF function calculates as:
3 The 7th item in the list matches cell G4; therefore, the only value which is not FALSE is 7. ## Section 2 – SMALL and COUNTIF functionsIf we feed the result above into the SMALL function, it would be as follows:
4 SMALL finds the nth smallest value. It works in a similar way to LARGE, but for the smallest value. SMALL only has two arguments:
5 - range: The range of data to be analyzed.
- k: The nth item to be found.
In this context, COUNTIF calculates how many instances of the value have already appeared in the top 10. For the first row, there is only 1 item in the top 10 with a score of 120, so COUNTIF will calculate as 1. The SMALL will find the first smallest value, which is 7, as all the other results are FALSE. Finally, it wraps that in the INDEX function to find the 7th value in the source table.
6 This calculates to cell A8, which in our example is Golf. All the workings above were to show how the formula works. Now we can copy the complete formula down into cells F5:F13. ## Testing on duplicate valuesLet’s test out our duplicate values logic with cell F11. There are 3 values in the top 10 which are all 80; they are found in G10, G11, and G12. The label in F11 should be Echo, as it is the 2nd value of 80. The formula in F11 is:
7 The IF portion of the function calculates as:
8 Rows 1, 5, and 20 in the source table all match the value of 80. Therefore, the COUNTIF calculates to 2, which makes sense, as it is the 2nd matching value we are looking for.
9 As the second smallest number is 5, the INDEX function returns the 5th value in the source list, which is Echo. Therefore, it has calculated the correct value. ## Bottom 10 valuesIf we want the bottom 10 values, the only change is that the formula to get the values uses SMALL, rather than LARGE. Look at the example file. The formula in cell G18 is:
0 The only difference between cell G18 and cell G4 is the use of the SMALL function. The formula in cell F18 is the same as we saw above before (but pointing at different cells).
1 ## Adding criteriaNow it’s time to look at adding selection criteria. In our example file, look at the Top 10 – non-DA with criteria tab. As we saw before, cell J4 contains the name of the city; London or Birmingham. We want to return the top 10 for the selected city only. Cell G4 has the following function:
2 This is another array formula. Remember, don’t enter the curly brackets, but press Ctrl+Shift+Enter again. This uses the same logic as we’ve already seen. The IF function checks cells B2:B27; if the value matches cell J4, the value in C2:C27 is returned. When the city is London, the IF calculate as follows:
3 By using the LARGE function, only the London values are returned into the top 10. All the non-London values calculate as FALSE. We make a similar adjustment to the formula which calculates the customer name (the added section is in bold)
4 This is an array formula. Don’t enter the curly brackets. Do press Ctrl+Shift+Enter again. ## Working with multiple criteriaLook at the Top 10 – non-DA with multi tab in the example file. An additional criterion has been added to only include items with a value equal to the city OR less than 100. The key to this is the arguments contained inside the IF function. The formula in cell G4 is:
5 The formula in cell F4 is:
6 These are array formulas, so remember to press Ctrl+Shift+Enter. The filter criteria are highlighted above. We can use this approach to add as many additional filter criteria as we like. With Excel, the plus symbol ( + ) creates OR logic, and an asterisk ( * ) creates AND logic. This is shown in the formulas below:
7
8 Wow! That was getting complicated. ## ConclusionOnce you know the techniques and functions, calculating a top 10 using formulas in Excel isn’t too bad. This post demonstrates how good dynamic arrays are; we no longer need to rely on complex array formulas. |