Dynamically rank the top 10 values including handling tied values - ua-cam.com/video/sk3z8e2mvng/v-deo.html This answers a common question in the comments thread for this video.
This is really helpful ! Thanks a lot :) But I am facing an issue that I have extract the same value for all top 10 somehow. I do have duplicated values in the "List" column, but do you think that is the case?
This is great, how could i do this across multiple sheets? Say I had similar data as you, but had 40 sheets each named after a year. How would I pull the top 10 from all sheets combined?
This is really helpful, so thanks. The only issue I've identified is where the value of 2 items in the sales column are equal, when this occurs the CompanyName index returns the first company name it finds twice. Any ideas of how to fix this?
This is a good video as far as it goes. My problem is I have duplicates within the top 10 and when I use the INDEX & MATCH function to show the names it will only show the first name of the duplicate sales. How do I get this formula to show the second name.
Thank you Don. Check out this video on returning the top ten and whatever details you want - name and other columns if you have them and want - ua-cam.com/video/u0kg0xIbJuw/v-deo.html A more complete solution than the formula because of the issue that you state.
Great video! I had a question - I have duplicate values in the "total" range. When I pull the names over, it duplicates a single name rather than filling in all of the names that match that total. (IE: I have 3 $100's, rather than seeing three names, I see one name, three times.) Is there a way to fix this?
I would use a formula in a new column next to Total column that uniquely ranks each value. I show this technique in this video - ua-cam.com/video/QFdsGGlSmrU/v-deo.html Then with this, we can then use a VLOOKUP to pull each name over using a technique like shown in this video - ua-cam.com/video/QAZ3L6xbNJc/v-deo.html That should complete what you ask for.
I tried following this myself. And when I get to the Vlookup part. How do I get it to automatically apply in my top 10 list since you only showed how to find the last instance. I still need to show each instance just not pulling the first.
I never get bored watching valuable videos and learning from you Alan. I am addicted to Excel and to such excellent videos. A good addiction, I think. Thank you. 👍
This video is awesome and incredibly helpful. I'd just add that if you're referencing a column by the range (e.g., A1:A50) to make sure you're using absolute referencing (e.g., $A$1:$A$50) etc. when creating the "name" column. I was using a lot more data but ran into the problem of relative referencing and, had I not caught it, would have given me the completely wrong "names." Thanks again!
@@Computergaga The only challenge with this approach is .. If 2 companies have same corresponding values then index match function will fail as this will show same company name twice in the list...
Great video. May I also check what if the TOP 3 in the E column are same sales figure. How am I going to match the different sales person name in the “D” column in your example. Please advice.
very helpfull.i only had a Problem with my table. If i have companies with the same Total, the index will refer to the first value correctly but repeat it in the nex same value. if my top 3 have the same total value, it will Show only the Name of the 1st one. Any idea on how to get around it? thanks a lot
You're welcome. In this example, it just includes the top 10 and does not account for duplicates. So if 9th is duplicated, then those results occupy 9th and 10th place, which is often ok and accurate. I do however, have another video showing ranking that handle tied values - ua-cam.com/video/sk3z8e2mvng/v-deo.html
I did this formula with 'small' for bottom 5 of a list of values, but it didn't take into account negative values. It only returned the 5 smallest positive amounts. Can anyone advise here please? TIA
The problem with the second part where it is indexed is if you have the same values in column 2, will it give 2 different lookup values in indexing column 1. It would be better if it step 1 could take multiple cells as one line.
Thank you Sean. Not sure about 'often'. Depends on what you're ranking. If it is sales then it is rare to tie values especially when in the high hundreds like the video example. But yes it happens. And then are various approaches. This video shows one where the tied values share the same rank ua-cam.com/video/sk3z8e2mvng/v-deo.html I have other videos, where the tied values are given distinct ranks, or other criteria is used to differentiate them.
Why do I get a pop up message saying, we dound a problem with this formula, try clicking insert function on the formula tab to fix... I pressed =LARGE(L8:L57,{1;2;3}) What could be the problem, I want the top 3
I want to do the same but instead of returning the list in column E I want to mark all rows falling within the top range values (i.e. in your example, appearing in the list) in column C (say, with an 'X'). What is the function I need to put in column C? Thanks in advance!
what if I have a third column with "orange" "apple" "banana" and i want to list the top sales only about "orange"? how it could be managed in your opinion?
There are a few approaches. One is to use COUNTIFS to create a unique ranking, video here ua-cam.com/video/g-L0uvVcEoM/v-deo.html, and then use a lookup to return the items for each rank. Another option is to use FILTER to filter for that fruit and then SORT to order them.
It does not give me the correct list if 2 values are the same. The first name is then repeated. (Eg. If 2 sales values are 984, then both will return the first name rather than both the names in subsequent cells)... So what modification needs to be done in the formula to get the total list, even if the values are the same.
This works if you don't have multiple "LARGE" things that match (for me, at least). I freely admit, I'm not the best with Excel formulas, but this doesn't seem to work for me if I have an array where the numbers might repeat (like 50, 49, 49, 47, 30, 27, 27, 25). When I do this, it only returns the first Index/Match for the 49 and 27, wherein in reality those might be two different people. How would you resolve this with a formula? Example Table: Name Value Peter 50 Mikey 30 Jonni 49 Mary 47 Paul 30 Elvis 27 Harry 49 Priscilla 25 I am getting (for example, top 5 values) Peter 50 Jonni 49 Jonni 49 Mary 47 Mikey 30 When (for it to function like the Conditional formatting "Top 10") I should be getting: Peter 50 Jonni 49 Harry 49 Mary 47 Mikey 30 Paul 30 Notice how there are multiple values for the top 5 values? It's like golf, you can have more than one person in 2nd, 3rd, etc. place. You can end up with 10 people as "the top 5 finishers". I'm probably really messing up the explanation. But would you know the equations to match the Conditional Formatting's "Top 10"?
When you have duplicate values it chooses the first name it comes to and list the name twice. How do I get the formula to give me both names that are tied to the duplicate values.
Hi Becky, INDEX and MATCH would just return the first value it finds without some advanced adaptations. I have this video on using COUNTIF to uniquely rank values to help create tables such as top 10 even when duplicates exist - ua-cam.com/video/QFdsGGlSmrU/v-deo.html
I would like to to use a couple of conditional statements to control the data selection where a top 10 list is drawn from, ideally 2 drop-down boxes, so i can see top 10 by either volume (kg) or turnover (kg), also top 10 by either customer or product. My column headers will be Customer Name, Product, total volume bought (kg) and total turnover (£). Can I put conditional statements in with a Large function to do this and control it with 2 drop-downs to choose the type of data you see? It would enable 1 list to show 4 different sets of data - top 10 volume by product, top ten value by product, top ten volume by customer, top ten value by customer. Does that make sense? Can it be done?
It can absolutely be done Al. Bit much to explain here though. You could use the LARGE function to create top 10 lists for all 4 situations. This could be on a hidden calculations tab. Then on the report sheet use either lookups or IF functions to show the right value dependent upon drop down list selection.
What if the top ten includes 3 different values of $950? The company name e.g. "Giles Co." would be repeated 3 times, instead of showing the three company with that value... Any advice?
I'm using this, but getting a strange result when values are tied (two+ names with the same value). Only one name shows in all three positions - instead of showing all three different names. Am I doing something wrong, or need to use a different formula?
@@Computergaga And I thank you for that. I am trying to rank about 500 players and some of them are appearing two or more times in the first 10 places list. I am not sure why. Is there a way to tie break the match function?
The COUNTIF in the video is a way I have done it in the past. I have created many ranking tables where I rank based on different criteria. But if the criteria all the same, than I just use the COUNTIF to make the rankings unique.
Sales is a named range. You can create these by highlighting the range and typing the name you want to use "sales" in the Name Box (left of Formula Bar).You can then enter this into formulas, chart ranges etc.
I adressed this by appending a random number to the source data for decimal places 4 onwards to limit the chances of getting a match for two values which "look" to be the same. Use the formula =YOURVALUE+RAND()/1000
sir i have a problem in this formula i put large formula because i want top4 values in a range but in next stepp slaes not put in this formula plz solve my problem i have a number range what i do sir
great video! Look, i have a similar column containing the %of the total sales for different products. i would like to create a rank where 5 would be if the percentage is on the top 20 values, 4 the 20-40, 3 the 50-60 and so on. any idea on how to do it? thanks a lot
Once you work out if the values is in the top 20 values or not using a formula similar to the video. You can then use that result either in a lookup or a logical function like IF to provide the correct ranking.
@@Computergaga I just found out the formula Percentile.inc.with this one i can calculate if my percentage of net sales is in the upper 20% or 60%..etc..and then im using an if to give the ranking...a bit weird but it seems to work somehow..
I would use a helper column to uniquely rank them first as shown in this video - ua-cam.com/video/QFdsGGlSmrU/v-deo.html Then use a lookup formula to return them in order for a top 10. This is a technique I use in my sports league table course.
You can uniquely rank them with the COUNTIF function - ua-cam.com/video/QFdsGGlSmrU/v-deo.html I'm planning on producing another video with other techniques on this soon.
what if the we have to find top 10 of row my list contains an item the below it name of suppliers that provide the item now how can i find the top 10 suppliers for particular item
Sounds like the setup of the spreadsheet may complicate matters if the items are always on a row above the suppliers. Typically your best bet would be a PivotTable for this task. Item in the Filters area, Supplier in Rows and sorted largest to smallest in the values area.
There a various approaches to this depending on what behaviour you want. This video shows a formula approach that merges the tied values to the same ranking ua-cam.com/video/sk3z8e2mvng/v-deo.html
You could also handle it by counting how many of any index are duplicated with COUNTIFS and adding that number. I have a video here using COUNTIFS - ua-cam.com/video/g-L0uvVcEoM/v-deo.html
We would ideally uniquely rank the values so that if there is a duplicate, both names appear. I have a video on unique ranking here - ua-cam.com/video/QFdsGGlSmrU/v-deo.html
SIR I NEED THE FOLLOWING RANK IN EXCEL HOW IT WILL BE POSSIBLE (AUTO AS RANK NORMAL FORMULA DO IT) ST MARKS RANK WE 50 1 ER 49 2 YT 48 3 PR 48 3 PP 47 4 LL 42 5 KK 40 6 MM 40 6 NB 39 7 THANKS IN ADVANCE.... WAIT FOR RESPONSE
How can write figure in Indian rupees like- 1,00,000/- one lakh, 10,00,000/- ten lakhs, 1,000/- thousands, 1,00,00,000/- one crore Sir how can I do this ...?
Because we are looking at automating the process. If you had a report that people in the company view 24/7 even when you are asleep, or out the office. A formula would also show the top ten value without the need for someone to go and sort values.
Thank you so much this video really helps me. But I have a problem when the value is the same few figure, they retrieve only one name. For example, if 3 people have the same marks, the data will capture the same name for all three. Please advise.
I have a video on using COUNTIF to uniquely rank items with the same value - ua-cam.com/video/QFdsGGlSmrU/v-deo.html I also have this newer video on creating a top 10 rank and it also handles the problem you state - ua-cam.com/video/sk3z8e2mvng/v-deo.html
That really depends on how you want to rank it. I have various videos at this channel on creating top 10 lists and how to handle duplicate values. This is one ua-cam.com/video/sk3z8e2mvng/v-deo.html But I also show in other videos how to use other criteria to distinguish which one ranks higher. I use this in my sports league rankings. Also, this is the new fastest way to create a top 10 list ua-cam.com/users/shortscfgx62N-dQo
Dynamically rank the top 10 values including handling tied values - ua-cam.com/video/sk3z8e2mvng/v-deo.html
This answers a common question in the comments thread for this video.
This is really helpful ! Thanks a lot :) But I am facing an issue that I have extract the same value for all top 10 somehow. I do have duplicated values in the "List" column, but do you think that is the case?
It's so satisfying finding an Excel tutorial that lets me do exactly what I wanted to do. Thank you!
It is very satisfying to read comments like this. Thank you for watching.
Of all the resources available on this subject I found yours to be most clear and helpful. God Bless!
That is great to hear, thank you.
Thank you So Much, this is very helpful for me
Fantastic! Glad it helped.
always coming back for this tutorial. thanks!
No problem, Terence 😊
YOU ARE SO AWESOME!!! THANKYOU THIS WAS SUPER USEFUL. IM TYPING IN ALL CAPS BECAUSE IM SUPER EXCITED THAT I WAS ABLE TO DO THIS
Glad it helped! You're very welcome.
This is great, how could i do this across multiple sheets? Say I had similar data as you, but had 40 sheets each named after a year. How would I pull the top 10 from all sheets combined?
Question: how would the formula change, if searching for the top 10 values in multiple columns, instead of one column?
i need answer to this too lol
same question here...
This is really helpful, so thanks. The only issue I've identified is where the value of 2 items in the sales column are equal, when this occurs the CompanyName index returns the first company name it finds twice. Any ideas of how to fix this?
same question, is anyone find the answer?
have you found the answer to this?
This is a good video as far as it goes. My problem is I have duplicates within the top 10 and when I use the INDEX & MATCH function to show the names it will only show the first name of the duplicate sales. How do I get this formula to show the second name.
Thank you Don. Check out this video on returning the top ten and whatever details you want - name and other columns if you have them and want - ua-cam.com/video/u0kg0xIbJuw/v-deo.html
A more complete solution than the formula because of the issue that you state.
Great video! I had a question - I have duplicate values in the "total" range. When I pull the names over, it duplicates a single name rather than filling in all of the names that match that total. (IE: I have 3 $100's, rather than seeing three names, I see one name, three times.)
Is there a way to fix this?
I would use a formula in a new column next to Total column that uniquely ranks each value. I show this technique in this video - ua-cam.com/video/QFdsGGlSmrU/v-deo.html
Then with this, we can then use a VLOOKUP to pull each name over using a technique like shown in this video - ua-cam.com/video/QAZ3L6xbNJc/v-deo.html
That should complete what you ask for.
I tried following this myself. And when I get to the Vlookup part. How do I get it to automatically apply in my top 10 list since you only showed how to find the last instance. I still need to show each instance just not pulling the first.
ua-cam.com/video/eilE4qFATrk/v-deo.html
I never get bored watching valuable videos and learning from you Alan. I am addicted to Excel and to such excellent videos. A good addiction, I think. Thank you. 👍
You're welcome Salim. Thank you.
This video is awesome and incredibly helpful. I'd just add that if you're referencing a column by the range (e.g., A1:A50) to make sure you're using absolute referencing (e.g., $A$1:$A$50) etc. when creating the "name" column. I was using a lot more data but ran into the problem of relative referencing and, had I not caught it, would have given me the completely wrong "names." Thanks again!
Thank you Jem. Much appreciated.
@@Computergaga The only challenge with this approach is .. If 2 companies have same corresponding values then index match function will fail as this will show same company name twice in the list...
@@dineshltjd i also suffered same problem..
@@dineshltjd do you have solutions
Great video. Thank you!
You're welcome Griffin.
Thanks. All the best and keep up the good work.
Thank you Akash.
Hi Alan.. excellent.. another great tip. Thumbs up!
You're welcome Wayne.
Great video. May I also check what if the TOP 3 in the E column are same sales figure. How am I going to match the different sales person name in the “D” column in your example. Please advice.
I have a new video which uses Power Query for the top 10/top 3 - ua-cam.com/video/u0kg0xIbJuw/v-deo.html
Good knowledge sir thank u so much for this formula
My pleasure, Sachin.
This is totaly Supper, But let me know whats the sales you include. is it total.
Thank you. Yes, the Total column.
very helpfull.i only had a Problem with my table. If i have companies with the same Total, the index will refer to the first value correctly but repeat it in the nex same value. if my top 3 have the same total value, it will Show only the Name of the 1st one. Any idea on how to get around it? thanks a lot
I have a video here on how to uniquely identify records with the same value - ua-cam.com/video/QFdsGGlSmrU/v-deo.html
I really enjoyed the video
Great! 😊
Thank you for this tech.
what happens if the data are duplicate? which one will be included in the list. let say the 9th record is duplicate
You're welcome. In this example, it just includes the top 10 and does not account for duplicates. So if 9th is duplicated, then those results occupy 9th and 10th place, which is often ok and accurate. I do however, have another video showing ranking that handle tied values - ua-cam.com/video/sk3z8e2mvng/v-deo.html
Thanks for this wonderful video
Glad you like it Syed.
This is really useful! What would be the formula for bottom 10 values?
Thank you. Same but with SMALL instead of LARGE.
I did this formula with 'small' for bottom 5 of a list of values, but it didn't take into account negative values. It only returned the 5 smallest positive amounts. Can anyone advise here please? TIA
Suppose more than one same values occured means. How can we find
Hi sir, if same amont in three raws, then how it works???
The problem with the second part where it is indexed is if you have the same values in column 2, will it give 2 different lookup values in indexing column 1. It would be better if it step 1 could take multiple cells as one line.
Please see this video Aaron - ua-cam.com/video/sk3z8e2mvng/v-deo.html
Nice job... thank you
You're welcome. Thank you.
thank you sir. what if the data has same value? it kept refer to the first name with the same value on the table.
I face the same problem. Do you find the solution?
How would I find values that are in the top ten in two columns, e.g. items that are in the top ten for both 3 months and 1-month lists?
Thanks. It helped me
You're welcome! Thank you.
Very Useful..
Thank you.
Brilliant Really helpful.
You're very welcome, Pramod.
EVERY HELPFUL.
Thanks 🙂
Cheers, that is so useful.
You're welcome. Thank you, Paul-Maurice.
Hi Sir, I'm still confused how you entered sales in the formula =Large(sales. Where does this sales came from. Can u just help me out?
@Computergaga please how did you the sales name for col B?
Short sited video. What if there are multiple matching total values (which is often the case). Index match will return the same company name for each.
Thank you Sean. Not sure about 'often'. Depends on what you're ranking. If it is sales then it is rare to tie values especially when in the high hundreds like the video example. But yes it happens. And then are various approaches. This video shows one where the tied values share the same rank ua-cam.com/video/sk3z8e2mvng/v-deo.html
I have other videos, where the tied values are given distinct ranks, or other criteria is used to differentiate them.
Hi one more thing, I have a question, if they ask you: If you need the top 3, but it must appear as a pop up that said top 3, in the selected cell.
Why do I get a pop up message saying, we dound a problem with this formula, try clicking insert function on the formula tab to fix...
I pressed =LARGE(L8:L57,{1;2;3})
What could be the problem, I want the top 3
Very useful, thank you
You're very welcome.
What if two company names have the same value, how are you able to list them both ?
Did you got thid bcz same question arrise when I went to an operation.
Very nice video Sir
Thank you Sada.
What if you have 2 items with the same numerical value?
hey bro if sometimes the value is same of two names then it shows only the one name as same in second.... then what to do next in mobile
I want to do the same but instead of returning the list in column E I want to mark all rows falling within the top range values (i.e. in your example, appearing in the list) in column C (say, with an 'X'). What is the function I need to put in column C? Thanks in advance!
Something like this works, Nándor - =IF(B2>=LARGE($B$2:$B$13,10),"X","")
@@Computergaga thank you very much
what if I have a third column with "orange" "apple" "banana" and i want to list the top sales only about "orange"? how it could be managed in your opinion?
There are a few approaches. One is to use COUNTIFS to create a unique ranking, video here ua-cam.com/video/g-L0uvVcEoM/v-deo.html, and then use a lookup to return the items for each rank.
Another option is to use FILTER to filter for that fruit and then SORT to order them.
thank you so much for helping.
You're welcome. Thank you Indrajit.
It does not give me the correct list if 2 values are the same. The first name is then repeated. (Eg. If 2 sales values are 984, then both will return the first name rather than both the names in subsequent cells)... So what modification needs to be done in the formula to get the total list, even if the values are the same.
This works, but what if you are trying to pull a top 50 or top 100?
This works, but there are other options also such as PivotTables and Power Query.
This works if you don't have multiple "LARGE" things that match (for me, at least).
I freely admit, I'm not the best with Excel formulas, but this doesn't seem to work for me if I have an array where the numbers might repeat (like 50, 49, 49, 47, 30, 27, 27, 25). When I do this, it only returns the first Index/Match for the 49 and 27, wherein in reality those might be two different people. How would you resolve this with a formula?
Example Table:
Name Value
Peter 50
Mikey 30
Jonni 49
Mary 47
Paul 30
Elvis 27
Harry 49
Priscilla 25
I am getting (for example, top 5 values)
Peter 50
Jonni 49
Jonni 49
Mary 47
Mikey 30
When (for it to function like the Conditional formatting "Top 10") I should be getting:
Peter 50
Jonni 49
Harry 49
Mary 47
Mikey 30
Paul 30
Notice how there are multiple values for the top 5 values? It's like golf, you can have more than one person in 2nd, 3rd, etc. place. You can end up with 10 people as "the top 5 finishers".
I'm probably really messing up the explanation. But would you know the equations to match the Conditional Formatting's "Top 10"?
Hi, i have a video on using COUNTIF to uniquely rank values - ua-cam.com/video/QFdsGGlSmrU/v-deo.html
When you have duplicate values it chooses the first name it comes to and list the name twice. How do I get the formula to give me both names that are tied to the duplicate values.
It seems that nobody knows, but it is the same question of me
This guy solved the problem here: ua-cam.com/video/rKDI-kdBsjY/v-deo.html
Is there a way to get the Index/Match function to work when there are duplicated numbers in the top 10 list?
Hi Becky, INDEX and MATCH would just return the first value it finds without some advanced adaptations.
I have this video on using COUNTIF to uniquely rank values to help create tables such as top 10 even when duplicates exist - ua-cam.com/video/QFdsGGlSmrU/v-deo.html
I would like to to use a couple of conditional statements to control the data selection where a top 10 list is drawn from, ideally 2 drop-down boxes, so i can see top 10 by either volume (kg) or turnover (kg), also top 10 by either customer or product. My column headers will be Customer Name, Product, total volume bought (kg) and total turnover (£). Can I put conditional statements in with a Large function to do this and control it with 2 drop-downs to choose the type of data you see? It would enable 1 list to show 4 different sets of data - top 10 volume by product, top ten value by product, top ten volume by customer, top ten value by customer. Does that make sense? Can it be done?
It can absolutely be done Al. Bit much to explain here though.
You could use the LARGE function to create top 10 lists for all 4 situations. This could be on a hidden calculations tab.
Then on the report sheet use either lookups or IF functions to show the right value dependent upon drop down list selection.
Thanks for the reply - sorted with 4 hidden tables and some radio buttons, and some nested IF's - rather crude solution but it works!
Sounds awesome Al. You don't always need the Porsche to get from A to B.
I want know the first highest second highest and so on in a list...will this work for me
Hi, If u want Top 2 pl close this formula by 1;2})
What if the top ten includes 3 different values of $950? The company name e.g. "Giles Co." would be repeated 3 times, instead of showing the three company with that value...
Any advice?
This video demonstrates the COUNTIF function uniquely ranking values when they have the same value - ua-cam.com/video/QFdsGGlSmrU/v-deo.html
Thank you! this helped me so much (:
Excellent! My pleasure.
Nice video
Thank you Sandeep.
I'm using this, but getting a strange result when values are tied (two+ names with the same value). Only one name shows in all three positions - instead of showing all three different names. Am I doing something wrong, or need to use a different formula?
The COUNTIF function can be used to avoid the duplication - ua-cam.com/video/QFdsGGlSmrU/v-deo.html
Hi can i do that but into page2 or as a report page ?
Absolutely. You can write these formulas anywhere you want. Just refer to the cells on the other sheet. No problem.
Dont you think so it was so elegant that i could barely move during your lecture..........
Thanx
No problem, Ali.
awesome!
Thank you Emma.
Thanx sir for prompt reply
You're welcome.
Thank you!
But I am getting some TIES here.
Could you PLEASE let us know how to get rid of those?
Thank you Marcelo. This video demonstrates a different way to uniquely rank values.
Here - ua-cam.com/video/QFdsGGlSmrU/v-deo.html
@@Computergaga And I thank you for that. I am trying to rank about 500 players and some of them are appearing two or more times in the first 10 places list. I am not sure why.
Is there a way to tie break the match function?
The COUNTIF in the video is a way I have done it in the past. I have created many ranking tables where I rank based on different criteria. But if the criteria all the same, than I just use the COUNTIF to make the rankings unique.
@@Computergaga - But I don't know how to use COUNTIF with this =LARGE(REALOVR,{1;2;3;4;5;6;7;8;9;10}) thing...
...that's the problem.
how do you highlight the column sales right after you typed "sales" in LARGE formula?
Sales is a named range. You can create these by highlighting the range and typing the name you want to use "sales" in the Name Box (left of Formula Bar).You can then enter this into formulas, chart ranges etc.
What about if one of the values is repeated. what's the formula to pull the second name?
Ah that is another story :) There are videos out on there on handling duplicates in this manner.
Thank you! any link you can share I tried searching and couldn't find any.
Here is one Karla - ua-cam.com/video/1ZVS0yC5hQg/v-deo.html
Demonstrates an array formula being used to return multiple items from a lookup.
Sir what if there is someone with similar value of sale
This formula will order them by values, so a similar value will be on another row, or if it's the 11th, not included.
@@Computergaga Thanks sir appreciate your reply
@@GOODVIBESTVPhil no worries 👍
tnx
My pleasure Reza.
Hi, I use above trick in my excel but it giving me the largest value into all 10 cells, please advise
Hi Pl see the video again if u want top 10 values in case u have to select 10 rows and write the formula on formula bar only.
Love it, however, what is happening is we have duplicated values in column b? Yes, it will take the first one... Anyway, I like it
what is the possible fix for this?
@@JP-zk6ef could anybody fix the similar value issue?
I adressed this by appending a random number to the source data for decimal places 4 onwards to limit the chances of getting a match for two values which "look" to be the same. Use the formula =YOURVALUE+RAND()/1000
EXCELLENT
Thank you Ram.
sir i have a problem in this formula i put large formula because i want top4 values in a range but in next stepp slaes not put in this formula plz solve my problem i have a number range what i do sir
Sorry I do not understand your question. You have the top 4 values?
yes sir i wana to show top 4 values in a range n excel what i do sir
That is what the video shows Shubham. I'm not sure where you want me to help.
great video! Look, i have a similar column containing the %of the total sales for different products. i would like to create a rank where 5 would be if the percentage is on the top 20 values, 4 the 20-40, 3 the 50-60 and so on. any idea on how to do it? thanks a lot
Once you work out if the values is in the top 20 values or not using a formula similar to the video. You can then use that result either in a lookup or a logical function like IF to provide the correct ranking.
@@Computergaga I just found out the formula Percentile.inc.with this one i can calculate if my percentage of net sales is in the upper 20% or 60%..etc..and then im using an if to give the ranking...a bit weird but it seems to work somehow..
Excellent. Nice work Gledison.
if have 2 value in column B, b4=b5, how to do. thanks bro
I would use a helper column to uniquely rank them first as shown in this video - ua-cam.com/video/QFdsGGlSmrU/v-deo.html
Then use a lookup formula to return them in order for a top 10. This is a technique I use in my sports league table course.
What if there are multiple cells with same value?
You can uniquely rank them with the COUNTIF function - ua-cam.com/video/QFdsGGlSmrU/v-deo.html
I'm planning on producing another video with other techniques on this soon.
what if the we have to find top 10 of row
my list contains an item the below it name of suppliers that provide the item now how can i find the top 10 suppliers for particular item
Sounds like the setup of the spreadsheet may complicate matters if the items are always on a row above the suppliers.
Typically your best bet would be a PivotTable for this task. Item in the Filters area, Supplier in Rows and sorted largest to smallest in the values area.
What if there are 2 names with the same sales?.
There a various approaches to this depending on what behaviour you want. This video shows a formula approach that merges the tied values to the same ranking ua-cam.com/video/sk3z8e2mvng/v-deo.html
how do you name the range? Intrinsically missing one of the important parts.
Select the range you want to name, click in the Name box (top left next to the Formula bar) and type the name you want to use. Press Enter.
what if you have two individuals with the same amount of sales?
Please see the comment by Karla Yazuri yesterday
How about if the Value is duplicated ?
I have this video handling duplicated values by ranking with dynamic arrays - ua-cam.com/video/sk3z8e2mvng/v-deo.html
You could also handle it by counting how many of any index are duplicated with COUNTIFS and adding that number. I have a video here using COUNTIFS - ua-cam.com/video/g-L0uvVcEoM/v-deo.html
So, how to find a name with repeated values?
We would ideally uniquely rank the values so that if there is a duplicate, both names appear. I have a video on unique ranking here - ua-cam.com/video/QFdsGGlSmrU/v-deo.html
How do you handle two values that are the same because it doesn't give you the names of the two companies, just one twice.
I have a video on uniquely ranking values here - ua-cam.com/video/QFdsGGlSmrU/v-deo.html
Combine this to return both names.
SIR I NEED THE FOLLOWING RANK IN EXCEL HOW IT WILL BE POSSIBLE (AUTO AS RANK NORMAL FORMULA DO IT)
ST MARKS RANK
WE 50 1
ER 49 2
YT 48 3
PR 48 3
PP 47 4
LL 42 5
KK 40 6
MM 40 6
NB 39 7
THANKS IN ADVANCE....
WAIT FOR RESPONSE
Wow
👍
Where is sales table in the file
It is the table in columns A and B.
How can write figure in Indian rupees like- 1,00,000/- one lakh, 10,00,000/- ten lakhs, 1,000/- thousands, 1,00,00,000/- one crore
Sir how can I do this ...?
This tutorial will show you how Naresh - chandoo.org/wp/2010/07/26/indian-currency-format-excel/
well error as usual. Formula parse error.
Lol this is useless and time consuming, why don't you just sort the list from Z to A with reference to column B
Because we are looking at automating the process. If you had a report that people in the company view 24/7 even when you are asleep, or out the office. A formula would also show the top ten value without the need for someone to go and sort values.
Well explained. Now I understand.
Thank you, it is very helpful
Great! Happy to help.
Thank you so much this video really helps me. But I have a problem when the value is the same few figure, they retrieve only one name. For example, if 3 people have the same marks, the data will capture the same name for all three. Please advise.
I have a video on using COUNTIF to uniquely rank items with the same value - ua-cam.com/video/QFdsGGlSmrU/v-deo.html
I also have this newer video on creating a top 10 rank and it also handles the problem you state - ua-cam.com/video/sk3z8e2mvng/v-deo.html
Thanx
Thank you, Ali.
what if there are 2 names that have same value ? how to rank it ?
That really depends on how you want to rank it. I have various videos at this channel on creating top 10 lists and how to handle duplicate values. This is one ua-cam.com/video/sk3z8e2mvng/v-deo.html
But I also show in other videos how to use other criteria to distinguish which one ranks higher. I use this in my sports league rankings.
Also, this is the new fastest way to create a top 10 list ua-cam.com/users/shortscfgx62N-dQo
@@Computergagathankyou, you are the best