Don\u2019t worry, we will cover all of these (they are also all included in the example file). Hopefully, along the way, you\u2019ll learn a little more about how Excel works.<\/p>\n\n\n\n
Dynamic arrays<\/h4>\n\n\n\n
Users with an Office 365 subscription now have access to a group of functions, which make use of the new dynamic array calculation engine. These give us a nice simple way to calculate a top 10. So, if you have a dynamic array enabled version of Excel, then be sure to check out that section.<\/p>\n\n\n\n
Download the example file<\/strong><\/p>\n\n\n\n I recommend you download the example file for this post. Then you\u2019ll be able to work along with examples and see the solution in action, plus the file will be useful for future reference.<\/p>\n\n\n\n <\/a> Note: If you don\u2019t have a Dynamic Array enabled version of Excel, the tabs containing those examples will display errors. <\/strong><\/p>\n\n\n\n Watch the video:<\/strong><\/p>\n\n\n\n Watch the video on YouTube<\/a><\/p>\n\n\n\n Contents<\/p>\n\n\n\n We are going to start with traditional functions (i.e., non-dynamic array). Using these, it is easy to obtain the top 10 values. But getting the names\/labels which relate to those ten values is the challenge.<\/p>\n\n\n\n Start by looking at the Top 10 \u2013 non-DA<\/strong> tab of the example file.<\/p>\n\n\n\n In our example file, there are 26 customers, with their values.<\/p>\n\n\n\n If we wanted to create a top 10 of these customers (without sorting the list), we could use the LARGE function. Cell G4 contains this formula:<\/p>\n\n\n\n LARGE only has two arguments:<\/p>\n\n\n\n In our example, the data range to analyze is cells C2 to C27.<\/p>\n\n\n\n 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. The first row of data calculates as 1, the second row, calculates as 2, and so on.<\/p>\n\n\n\n The formula in cell G4 has been copied down to display the top 10 values.<\/p>\n\n\n\n We have the values, so now we can calculate the customer name for that value.<\/p>\n\n\n\n We can\u2019t use VLOOKUP as the customer name is to the left of the lookup value. Instead, we will use the INDEX\/MATCH formula combination. Cell F4, in our example, would contain the following formula.<\/p>\n\n\n\n If this formula were copied down into cells F5 \u2013 F13. Our worksheet would display as follows:<\/p>\n\n\n\n There is one huge problem \u2013 our top 10 values are not unique; there are 3 values of 80 (see the screenshot above). A basic INDEX\/MATCH only returns the first value, so it finds the name Alfa<\/strong> 3 times. Yet, Echo<\/strong> and Tango<\/strong>, who also have 80, do not feature on the list. This is clearly an error, so how do we get around this?<\/p>\n\n\n\n To solve the issue of finding labels with non-unique values, we will turn to an advanced array formula. The formula in cell F4 should be:<\/p>\n\n\n\n Woah!!! That\u2019s big and complicated!.<\/p>\n\n\n\n This is a special type of formula, known as an array formula. Don\u2019t 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 will add these itself. Pressing Ctrl+Shift+Enter lets Excel know that it is an array formula.<\/strong>REMEMBER! \u2013 If you go back into an array formula to edit it, you need to press Ctrl + Shift + Enter again to re-enter the formula.<\/strong><\/p>\n\n\n\n This formula is like INDEX\/MATCH, but returns the 1st, 2nd, 3rd, 4th\u2026 or nth value. Let\u2019s dig a bit deeper to understand how it works.<\/p>\n\n\n\n In the middle of the formula, we find an IF function.<\/p>\n\n\n\n In English, this says:<\/p>\n\n\n\n If C2 = G4 then return the count of rows between C2 and C1.<\/em><\/p>\n\n\n\n As this is an array formula, it automatically goes onto the next row and calculates again<\/p>\n\n\n\n If C3 = G4 then return the count of rows between C3 and C1.<\/em><\/p>\n\n\n\n And it keeps on going<\/p>\n\n\n\n If C4 = G4 then return the count of rows between C4 and C1.<\/em><\/p>\n\n\n\n This will go all the way from C2 to C27.<\/p>\n\n\n\n For the formula in F4, the IF function calculates as:<\/p>\n\n\n\n The 7th item in the list matches cell G4; therefore, the only value which is not FALSE is 7.<\/p>\n\n\n\n If we feed this into the SMALL function, it would be as follows:<\/p>\n\n\n\n SMALL finds the nth <\/sup>smallest value. It works in a similar way to LARGE, but for the smallest value.<\/p>\n\n\n\n SMALL only has two arguments:<\/p>\n\n\n\n In this context, COUNTIF calculates how many instances of the value have appeared in the top 10 already.<\/p>\n\n\n\n For the first row, there is only 1 item in the top 10 with a score of 120, so COUNTIF will calculate as 1.<\/p>\n\n\n\n The SMALL will find the first smallest value, which is 7<\/strong>, as all the other results are FALSE.<\/p>\n\n\n\n Finally, let\u2019s wrap that in the INDEX function to find the 7th value in the source table.<\/p>\n\n\n\n This calculates to cell A8, which in our example is Golf<\/strong>.<\/p>\n\n\n\n All the workings above were to show how the formula works. Now we can copy the complete formula down into cells F5 \u2013 F13.<\/p>\n\n\n\n The first row does not have a duplicate value. So, let\u2019s test it out 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<\/strong>, as it is the 2nd value of 80.<\/p>\n\n\n\n The formula in F11 is:<\/p>\n\n\n\n The IF portion of the function calculates as:<\/p>\n\n\n\n Rows 1, 5, and 20 in the source table all match the value of 80.<\/p>\n\n\n\n The COUNTIF calculates to 2, which makes sense, as it is the 2nd matching value we are looking for.<\/p>\n\n\n\n As the second smallest number is 5, the INDEX function returns the 5th value in the source list, which is Echo.<\/strong><\/p>\n\n\n\n Fantastic stuff, right!!!<\/p>\n\n\n\n If you want the bottom 10 values, the only change is that the formula to get the values uses SMALL, rather than LARGE.<\/p>\n\n\n\n Look at the example file. The formula in cell G18 is:<\/p>\n\n\n\n The only difference between cell G18 and cell G4 is the use of the SMALL function.<\/p>\n\n\n\n The formula in cell F18 is the same as we saw above before (but pointing at different cells).<\/p>\n\n\n\n Sometimes we want to calculate the top 10 results where a specific condition is met. In our example file, look at the Top 10 \u2013 non-DA with criteria<\/strong> tab.<\/p>\n\n\n\n Cell J4 contains the name of the city; London or Birmingham. The top 10 will only be returned for customers in that city.<\/p>\n\n\n\n Cell G4 has the following function:<\/p>\n\n\n\n This is another array formula. Remember, don\u2019t enter the curly brackets, but do press Ctrl+Shift+Enter again.<\/strong><\/p>\n\n\n\n This uses the same logic as we\u2019ve already seen. The IF function checks cells B2 to B27; if the value matches cell J4, the value in C2 to C27 is returned.<\/p>\n\n\n\n When the city is London, the IF calculate as follows:<\/p>\n\n\n\n By using the LARGE function, only the London values will be returned into the top 10. All the non-London values calculate as FALSE.<\/p>\n\n\n\n We make a similar adjustment to the formula which calculates the customer name (the added section is in bold)<\/p>\n\n\n\n This is an array formula. Don\u2019t enter the curly brackets. Do press Ctrl+Shift+Enter again.<\/strong><\/p>\n\n\n\n This uses similar TRUE\/FALSE logic to only calculate the values which match the city selected.<\/p>\n\n\n\n Look at the example file (Cells E17 \u2013 H27) if you want to see how a bottom 10 with criteria is calculated.<\/p>\n\n\n\n Generate accurate VBA code in seconds with AutoMacro<\/strong><\/p>\n\n\n\n Everything above seems quite complicated. Wouldn\u2019t it be better if we could use some more straightforward formulas? If you have a dynamic array enabled version of Excel (only available to Office 365 subscribers at the time of writing), then you\u2019re in luck.<\/p>\n\n\n\n Look at the Top 10 DA<\/strong> tab of the example file.<\/p>\n\n\n\n In cell F4 the formula is:<\/p>\n\n\n\n And that\u2019s it! No more array formulas required for the top 10, no need to press Ctrl+Shirt+Enter, no need to copy down.<\/p>\n\n\n\n Let\u2019s dig into this a bit deeper.<\/p>\n\n\n\n SORT has four arguments:<\/p>\n\n\n\n In our formula, we used the following SORT.<\/p>\n\n\n\n This sorts the cells A2 to C27 on the 3rd column in descending order.<\/p>\n\n\n\n The SEQUENCE function has four arguments:<\/p>\n\n\n\n In our example, the following creates a list from 1 to 10. We only need the first argument, as we can use the default options for the optional arguments.<\/p>\n\n\n\n Let\u2019s put both SORT and SEQUENCE into a traditional INDEX function:<\/p>\n\n\n\n The formula returns the first 10 results from the SORT and returns columns 1 and 3.<\/p>\n\n\n\n That was so easy\u2026 right.<\/p>\n\n\n\n To get the bottom 10, we only need to change the 3rd argument of the SORT function from -1 to 1. Look at cell F18 in the example file to see this in action.<\/p>\n\n\n\n Even if we have criteria to apply, it is still straight forward with dynamic arrays.<\/p>\n\n\n\n Now, look at the Top 10 \u2013 DA with criteria<\/strong> tab in the example file.<\/p>\n\n\n\n The formula in Cell F4 is:<\/p>\n\n\n\n The only difference to the previous example is that we are using the FILTER function to only include the matching items, before it is fed into the SORT function.<\/p>\n\n\n\n FILTER has three arguments:<\/p>\n\n\n\n In our example, the FILTER function is:<\/p>\n\n\n\n It is returning cells A2 to C27, but only where the values from B2 to B27 equals the selected city in cell J4.<\/p>\n\n\n\n <\/p>\n\n\n\n Once you know the techniques and the formulas, calculating a top 10 using formulas in Excel isn\u2019t too bad.<\/p>\n\n\n\n This post demonstrates how good dynamic arrays are; we don\u2019t need to rely on complex array formulas any longer.<\/p>\n\n\n\n Don\u2019t forget:<\/strong><\/p>\n\n\n\n If you\u2019ve found this post useful, or if you have a better approach, then please leave a comment below.<\/p>\n\n\n\n Ref: Top 10 with formulas in Excel – Excel Off The Grid<\/a><\/p>\n","protected":false},"excerpt":{"rendered":" A top 10 list is a common form for displaying information, especially on dashboards and summarized reports. It is easy to create the list when working with sorted data, just cell link to the top 10 items in the list\u2026 easy! It\u2019s also relatively simple when using Auto Filter, Tables and Pivot Tables, as it Read More<\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_bbp_topic_count":0,"_bbp_reply_count":0,"_bbp_total_topic_count":0,"_bbp_total_reply_count":0,"_bbp_voice_count":0,"_bbp_anonymous_reply_count":0,"_bbp_topic_count_hidden":0,"_bbp_reply_count_hidden":0,"_bbp_forum_subforum_count":0,"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[10,18],"tags":[660],"class_list":["post-3178","post","type-post","status-publish","format-standard","hentry","category-microsoft","category-microsoft-office","tag-top-10-with-formulas-in-excel"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/posts\/3178","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=3178"}],"version-history":[{"count":2,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/posts\/3178\/revisions"}],"predecessor-version":[{"id":3184,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/posts\/3178\/revisions\/3184"}],"wp:attachment":[{"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=3178"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=3178"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=3178"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
Download the file: 0015 Top 10 using formulas.zip<\/a><\/p>\n\n\n\nTraditional functions<\/h3>\n\n\n\n
Using the LARGE function<\/h3>\n\n\n\n
![\"Top](\"https:\/\/exceloffthegrid.com\/wp-content\/uploads\/2020\/02\/Top-10-source-scenario.png?ezimgfmt=rs:657x661\/rscb2\/ng:webp\/ngcb2\")
<\/figure>\n\n\n\n=LARGE($C$2:$C$27,ROW(F4)-ROW(F$3))<\/pre>\n\n\n\n
=LARGE(Range, k)<\/pre>\n\n\n\n
ROW(F4)-ROW(F$3)<\/pre>\n\n\n\n
![\"\"](\"https:\/\/exceloffthegrid.com\/wp-content\/uploads\/2020\/02\/Top-10-copied-down-LARGE-function.png?ezimgfmt=rs:607x379\/rscb2\/ng:webp\/ngcb2\")
<\/figure>\n\n\n\nFinding the labels for the top 10<\/h3>\n\n\n\n
=INDEX($A$2:$A$27,MATCH(G4,$C$2:$C$27,0))<\/pre>\n\n\n\n
![\"Top](\"https:\/\/exceloffthegrid.com\/wp-content\/uploads\/2020\/02\/Top-10-Duplicate-Values.png?ezimgfmt=rs:607x358\/rscb2\/ng:webp\/ngcb2\")
<\/figure>\n\n\n\nFinding the labels when the top 10 values are not unique<\/h3>\n\n\n\n
{=INDEX($A$2:$A$27,SMALL(IF($C$2:$C$27=G4,ROW($C$2:$C$27)-ROW($C$1)),\nCOUNTIF($G$4:G4,G4)))}<\/pre>\n\n\n\nSection 1 \u2013 IF function<\/h4>\n\n\n\n
IF($C$2:$C$27=G4,ROW($C$2:$C$27)-ROW($C$1))<\/pre>\n\n\n\n
{FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;7<\/strong>;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;\nFALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}<\/pre>\n\n\n\nSection 2 \u2013 SMALL and COUNTIF functions<\/h4>\n\n\n\n
SMALL(<\/strong>{FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;7;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;\nFALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE},\nCOUNTIF($G$4:G4,G4))<\/strong><\/pre>\n\n\n\n=SMALL(Range,k)<\/pre>\n\n\n\n
=INDEX($A$2:$A$27,<\/strong>7)<\/strong><\/pre>\n\n\n\n
Testing on duplicate values<\/h4>\n\n\n\n
{=INDEX($A$2:$A$27,SMALL(IF($C$2:$C$27=G11,ROW($C$2:$C$27)-ROW($C$1)),\nCOUNTIF($G$4:G11,G11)))}<\/pre>\n\n\n\n{1<\/strong>;FALSE;FALSE;FALSE;5<\/strong>;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;\nFALSE;FALSE;FALSE;FALSE;FALSE;FALSE;20<\/strong>;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}<\/pre>\n\n\n\nSMALL(<\/strong>{1;FALSE;FALSE;FALSE;5;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;\nFALSE;FALSE;FALSE;FALSE;FALSE;FALSE;20;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE},2)<\/strong><\/pre>\n\n\n\nBottom 10 values<\/h3>\n\n\n\n
=SMALL($C$2:$C$27,ROW(F18)-ROW($F$17))<\/pre>\n\n\n\n
=INDEX($A$2:$A$27,SMALL(IF($C$2:$C$27=G18,ROW($C$2:$C$27)-ROW($C$1)),\nCOUNTIF($G$18:G18,G18)))<\/pre>\n\n\n\n
Adding criteria<\/h3>\n\n\n\n
![\"Top](\"https:\/\/exceloffthegrid.com\/wp-content\/uploads\/2020\/02\/Top-10-with-criteria.png?ezimgfmt=rs:800x667\/rscb2\/ng:webp\/ngcb2\")
<\/figure>\n\n\n\n{=LARGE(IF($B$2:$B$27=$J$4,$C$2:$C$27)<\/strong>,ROW(F4)-ROW(F$3))}<\/pre>\n\n\n\n{80<\/strong>;FALSE;FALSE;FALSE;80<\/strong>;100<\/strong>;120<\/strong>;FALSE;95<\/strong>;FALSE;FALSE;55<\/strong>;FALSE;FALSE;\n-35<\/strong>;30<\/strong>;FALSE;FALSE;FALSE;80<\/strong>;FALSE;-20<\/strong>;75<\/strong>;-15<\/strong>;FALSE;FALSE}<\/pre>\n\n\n\n={INDEX($A$2:$A$27,SMALL(\nIF(($C$2:$C$27=G4)*($B$2:$B$27=$J$4)<\/strong>,ROW($C$2:$C$27)-ROW($C$1)),\nCOUNTIF(G4:$G$4,G4)))}<\/pre>\n\n\n\n![\"AutoMacroExample\"](\"https:\/\/exceloffthegrid.com\/wp-content\/uploads\/2019\/11\/AutoMacroExample.gif\")
<\/a><\/figure>\n\n\n\nDynamic array functions<\/h3>\n\n\n\n
INDEX \/ SORT \/ SEQUENCE function combination<\/h3>\n\n\n\n
![\"Dynamic](\"https:\/\/exceloffthegrid.com\/wp-content\/uploads\/2020\/02\/Dynamic-Arrays-using-SORT.png?ezimgfmt=rs:650x407\/rscb2\/ng:webp\/ngcb2\")
<\/figure>\n\n\n\n=INDEX(SORT(A2:C27,3,-1),SEQUENCE(10),{1,3})<\/pre>\n\n\n\nSORT<\/h4>\n\n\n\n
=SORT(array, [sort_index], [sort_order], [by_col])<\/pre>\n\n\n\n
SORT(A2:C27,3,-1)<\/pre>\n\n\n\n
SEQUENCE<\/h4>\n\n\n\n
=SEQUENCE(Rows, [Columns], [Start], [Step])<\/pre>\n\n\n\n
SEQUENCE(10)<\/pre>\n\n\n\n
INDEX<\/h4>\n\n\n\n
=INDEX(SORT(A2:C27,3,-1),SEQUENCE(10),{1,3})<\/pre>\n\n\n\nBottom 10<\/h3>\n\n\n\n
<\/a>report this adDynamic arrays with criteria<\/h3>\n\n\n\n
![\"Dynamic](\"https:\/\/exceloffthegrid.com\/wp-content\/uploads\/2020\/02\/Dynamic-arrays-with-criteria.png?ezimgfmt=rs:800x389\/rscb2\/ng:webp\/ngcb2\")
<\/figure>\n\n\n\n=INDEX(SORT(FILTER(A2:C27,B2:B27=J4)<\/strong>,3,-1),SEQUENCE(10),{1,3})<\/pre>\n\n\n\nFILTER<\/h4>\n\n\n\n
=FILTER(array, include, [if_empty])<\/pre>\n\n\n\n
FILTER(A2:C27,B2:B27=J4)<\/pre>\n\n\n\n
Conclusion<\/h3>\n\n\n\n