This is a real situation that many of us already experienced! We have a list of data and we need to get the top N values from that list. Of course we can sort descending the list and get the top values, but many times we can’t sort the source data (it may be used on another report, for instance). We can use the LARGE() function to get the nth largest value from a range of values. The syntax is like this:
LARGE(array, k)
array is the range of data from where we want to get our top values
k is the position on the range of data to return
k is the position on the range of data to return
So, let’s see an example on how to apply this function to get our top values. Our source data is like this:
In this example we want to get the Top 5 companies by Invoices value. We will be putting the values on cells B17:C21.
To start, let’s put the numbers 1 to 5 on cells A17:A21. Now, on cell C17 let’s put our formula, like this:
=LARGE($C$3:$C$15, A17)We will be looking on cells C3:C15, where we have our Invoices values, for the first largest value, that why we are using cell A17 that has the number 1 on it. Use the dollar signs on the array argument so that when we copy the formula to the next rows it will keep the reference to our Invoices values that are on C3:C15. Now copy the formula on cell C17 to the rest of the cells (C18:C21). This is how our sheet should look like:
So, now we have the top 5 Invoices values but wee need to fill the companies names. For that we will use the MATCH() function to get the row number within range C3:C15 that has the value that matches C17. The MATCH formula will be like this:
=MATCH(C17,$C$3:$C$15,0)
For the value in C17, this formula would return 10 because the value on this cell is coming from row 10 of our range source. This is not row 10 of our sheet but row 10 of our source! Now with this information we can get the company name that is on this row. For that we will use the INDEX() function like this:
=INDEX($B$3:$ B$15,10)
This would get the company’s name on row 10 of our source data.
Combining both formulas, it will look like this:
=INDEX($B$3:$B$15,MATCH(C17,$C$3:$C$15,0))
Put this formula on cell B17 and copy to the other cells on B18:B21.
The result will be like this, where we get on cells B17:C21 the top 5 values of Invoices from our source data on B3:C15:
Hope that this article can be useful for your work.
93 comentários:
Thanks for the help! However, it is causing the cells to be incorrectly labeled when it encounters two or more of the same value.
Please help. Thanks.
It's true! This solution is for lists that don't have duplicated values.
I agree with your solution. Here is another good explaination of the Large formula...
http://excelprofessor.blogspot.com/2011/10/dashboard-top-5-values-bottom-5-values.html
Thank you
Any ideas how to use this for whole numbers with duplicates?
Really appreciate some help if anyone knows the answer.
THanks a lot this turned out to be most helpful in solving our problem
Regards
HEMANT AND AYUSH
JIM, NOIDA
PGDM (2012-14)
SUBJECT-MIS...
TRIM-3
While this is an interesting and informative exercise this as not a good solution for the stated problem. As others have said it will give incorrect results if there are duplicate values for N.
What if you had another criteria such as Region of the country that you want to pull in the top 5 as you've done here but also want to specify a certain region which would be located within the data on each line?
Good work...
dear one
but i have one more situation where i need ur help
Company Sales
Name (in cr)
A 60
B 56
C 42
D 87
E 80
F 87
G 82
I 10
J 87
L 10
NOW I need to FIND TOP 5 COMPANIES NAME WITH SALES AMOUNT.(i.e. D,F J G and A) but it will gives result as like D D D G A
ur reply is highly expected with regards
this is good post...
i like this...
please can you visit here..
http://bantalsilikon01.blogspot.com/
http://bantalsilikon01.blogdetik.com/
http://bantalkekabumurah.blogspot.com/
http://bumbupecel1.blogspot.com/
http://bantalgood.blogspot.com/
http://bantalsilikonmalang1.blogspot.com/
http://bantalsilikonmurah.blogspot.com/
http://marinirseo.blogspot.com/
http://marinirseo.blogspot.com/
http://marinirseo.blogspot.com/
tengs very much...
What happens if the value is exactly same as another, will this not give error?
A quick workaround would be to make value unique by adding a small fraction to the value e.g. +ROW()*10^-6
Thanks for the Large formula.
easy excel rank
very helpful article
thanks
Hah, "+ROW()*10^-6" is brilliant. And then just formatting the cell as a number to only show however many decimals you want. Perfect :)
so how does the "+ROW()*10^-6" still work with the Match(index) portion of the formula? When I use that, I am no longer returning back a matching value. I have duplicates, and need to get each line to come back.
appreciate any help on this, as the first part of this was a HUGE win for me
check this out for duplicates https://www.youtube.com/watch?v=rKDI-kdBsjY
GREAT!!!! EASY TO UNDERSTAND EXPLANATION OF GETTING TOP 5!!!
THE FIRST ONE I FOUND I COULD NOT GET IT TO WORK AND THEN FOUND THIS .....IT WORKED MY FIRST TRY!!!
In the above example, why to use LARGE formula? We find Large (top) five by using Descending order and Small five by Ascending order.
Hi Thanks it works.
How will it be done for a row based ???
Wrote this formula for a row search
=INDEX($B$1:$Z$1,MATCH(D29,$B$6:$Z$6,0))
Specialized Services Area 10 is a professional company in Kuwait, based on Kuwait Satellite Technician, Renewal of satellite subscription, installation and installation,
فني ستلايت الكويت
شراء الاثاث المستعمل بالكويت - نشتري الاثاث المستعمل بالكويت
used furniture kuwait
Used Furniture Kuwait
https://themeforest.net/user/usedfurniturekw27
شراء اثاث مستعمل بالكويت
https://www.colourlovers.com/lover/usedfurniturekw27
نشتري الاثاث المستعمل بالكويت
technician healthy kuwait
شركة تسليك مجاري بالكويت
فني صحي
https://disqus.com/by/technician_healthy_kuwait/
فني صحي بالكويت
technician healthy kuwait
Technician Healthy Kuwait
تسليك مجاري بالكويت
https://profiles.wordpress.org/usedfurniturekuwait410/
خدمات الكويت
https://profile.hatena.ne.jp/kuwaitservices/
https://profiles.wordpress.org/thk279/
https://profiles.wordpress.org/ufkw27/
https://www.ifixit.com/Team/37561/kuwait+services
https://steepster.com/kwdalile3lank
https://n4g.com/user/score/kwdalile3lank
https://rhizome.org/profile/kuwait-services/
https://worldcosplay.net/member/844233
https://www.trover.com/u/3155047853
https://www.edocr.com/user/kwdalile3lank
https://www.funnyordie.com/users/kwdalile3lank
https://my.archdaily.com/us/@kuwait-services
https://myanimelist.net/profile/kwdalile3lank
http://kuwaitservices.idea.informer.com/
https://readthedocs.org/projects/kwdalile3lank-demo/
https://www.merchantcircle.com/kuwait-services-brooklyn-ny
https://www.merchantcircle.com/blogs/kuwait-services-brooklyn-ny/2019/10/Web-Services/1763322
https://www.viki.com/users/kw_dalil_e3lank_628/about
https://www.technologyreview.com/profile/web-services/
https://www.zumvu.com/kuwaitservices/
نقل عفش
فني تكييف
ونش
فني ستلايت
فني كهربائي
نشتري السيارات
نشتري الاثاث
تسليك مجاري
https://pbase.com/kwdalile3lank/profile
https://blog.dnevnik.hr/kuwait-services
فني صحي الزهراء
فني صحي القصور
فني صحي حولي
فني صحي الشهداء
فني صحي الجهراء
فني صحي مشرف
نقل عفش مبارك الكبير
نقل عفش الجهراء
نقل عفش حولي
نقل عفش السالميه
نقل عفش الاحمدي
شراء الاثاث المستعمل بالكويت
نشتري الاثاث المستعمل بالكويت
شراء عفش مستعمل بالكويت
نقل عفش الكويت
نقل عفش
نقل اثاث
فني صحي
تسليك مجاري
https://plbz.it/33V7ppT
ارقام يشترون اثاث مستعمل بالكويت
فني صحي بالكويت
شراء اثاث مستعمل الكويت
نشتري الاثاث المستعمل بالكويت
نشتري السيارات الكويت
فني كهربائي بالكويت
فني ستلايت الكويت
ونش الكويت
فني تكييف الكويت
نقل عفش الكويت
http://saudi-services-sde.mystrikingly.com/
يشتري الاثاث المستعمل بالرياض
ارقام يشترون الاثاث المستعمل بجدة
ملتي ماكا
https://yed.yworks.com/support/qa/user/kwdalile3lank
http://www.mappery.com/user.php?name=kwdalile3lank
https://fontlibrary.org/en/member/kwdalile3lank/
https://able2know.org/user/kwdalile3lank/
https://www.inprnt.com/profile/kwdalile3lank/
http://www.dermandar.com/user/kwdalile3lank/
https://kuwait-services.seesaa.net/
نقل عفش
فني صحي
شراء الاثاث
نشتري الاثاث
شراء عفش
نشتري الاثاث
https://publiclab.org/profile/kwdalile3lank
https://ecastats.uneca.org/acsweb/cr/UserProfile/tabid/866/userId/793821/language/en-US/Default.aspx
https://pro.ideafit.com/profile/kuwait-services
https://www.hipwee.com/author/kuwaitservices/
https://cycling74.com/author/5ddd7fe2c6357a7dba29c579
http://twitxr.com/kwdalile3lank/
https://www.wantedly.com/users/124167015
https://amara.org/en/profiles/profile/kuwaitservices/
https://www.longisland.com/profile/kuwaitservices
https://cycling74.com/author/5ddd841cc6357a7dba29c584
https://www.hipwee.com/author/usedfurniturekuwait/
https://publiclab.org/profile/kuwaitdaleel
https://ecastats.uneca.org/acsweb/cr/UserProfile/tabid/866/UserId/793846/language/en-US/Default.aspx
https://www.hipwee.com/author/kuwaitdaleel/
https://www.hipwee.com/author/moso3a3amalife/
http://kuwaitservices.greatwebsitebuilder.com/
https://challenges.openideo.com/profiles/kw.dalil.e3lank
https://greasyfork.org/ar/users/411144-kwdalile3lank
https://www.sandiegoreader.com/users/kwdalile3lank/
https://en.clubcooee.com/users/view/kwdalile3lank
https://thriveglobal.com/authors/kuwait-services
https://kw-services.web.wox.cc/
https://challenges.openideo.com/profiles/sallysabry85
https://www.sandiegoreader.com/users/usedfurniturekuwait/
https://www.sandiegoreader.com/users/usedfurnituregate/
https://www.sandiegoreader.com/users/usedfurniturekuwait2/
https://www.hercampus.com/author/kuwait-services
http://www.abstractfonts.com/members/872560
https://network.changemakers.com/profiles/kw.dalil.e3lank
https://network.changemakers.com/profiles/janajodyehab
http://www.abandonia.com/en/user/4068358
https://forums.prosportsdaily.com/member.php?612839-kuwaitservices
http://kuwaitservices.blog4u.pl/
http://forum.ventrilo.com/member.php?u=527390
https://community.cbr.com/member.php?112477-kwdalile3lank
https://network.changemakers.com/profiles/janajodyehab
https://fontlibrary.org/en/member/usedfurniturekuwait/
https://actionnetwork.org/users/kuwait-services/profile
https://www.chordie.com/forum/profile.php?id=948104
https://triberr.com/kwdalile3lank
http://codepad.org/users/kwdalile3lank
http://recipes.mentaframework.org/user/profile/57655.page
http://hawkee.com/profile/697839/
http://codepad.org/users/usedfurniturekuwait
http://codepad.org/users/usedfurnituregate
http://codepad.org/users/usedfurniturekuwait2
http://codepad.org/users/usedfurniturekw
http://hawkee.com/profile/697841/
http://hawkee.com/profile/697844/
http://hawkee.com/profile/697845/
http://hawkee.com/profile/697846/
https://www.misterpoll.com/users/528943
https://www.misterpoll.com/users/528944
https://www.misterpoll.com/users/528945
https://www.misterpoll.com/users/528946
https://www.misterpoll.com/users/528947
https://evermotion.org/vbulletin/member.php?955155
https://evermotion.org/vbulletin/member.php?955157
https://evermotion.org/vbulletin/member.php?955159
https://evermotion.org/vbulletin/member.php?955161
https://www.phishtank.com/user.php?username=usedfurniturekuwait
https://www.phishtank.com/user.php?username=usedfurnituregate
https://www.phishtank.com/user.php?username=usedfurniturekuwait2
https://www.myvidster.com/profile/kwdalile3lank
https://www.misterpoll.com/users/528942
https://page-79.kickoffpages.com/
https://page-80.kickoffpages.com/
https://page-81.kickoffpages.com/
https://page-82.kickoffpages.com/
https://page-83.kickoffpages.com/
https://page-84.kickoffpages.com/
https://kuwait-services.kickoffpages.com/
https://evermotion.org/vbulletin/member.php?955148
https://www.phishtank.com/user.php?username=kwdalile3lank
https://thimpress.com/forums/users/kwdalile3lank/
شراء الاثاث المستعمل
شراء اثاث
شراء اثاث مستعمل
Post a Comment