Excel Rank Function with different ranks for same values
Vložit
- čas přidán 3. 03. 2016
- This video will help you get Ranks in Excel, without repetative Ranks
The normal =Rank function in Excel gives you the same rank for 2 or more same values.
For example, if there are 4 values - 34, 30, 38, 34; ranking is done in descending order,
38 will get rank 1
34 will get rank 2
34 will get rank 2
30 will get rank 4
This can be avoided using =Rank with =Countif function.
Thus, 2 same values will get different ranks, one after the other.
So the new ranking will be,
38 will get rank 1
34 will get rank 2
34 will get rank 3
30 will get rank 4
---------------------
After this video, learn in-depth about Pivot tables and charts, Enroll for an Online course conducted by me on Master Business Reporting with Pivot tables and charts in Excel
Also, as you are a special Viewer/ Subscriber,
we would like to give out a special discount just for you,
so click here -
www.udemy.com/master-business...
---------------------------------------------
Click this link to learn how to manage your personal finance using Excel - • Personal Finance
SUBSCRIBE NOW!
/ @excelrush
Blog
Visit www.ExcelRush.com for my free blog to learn Excel in-depth.
Rushabh Shah is an expert in training Professionals & Students in Excel's Advanced features & formulas. These tutorials are simply a way to spread the knowledge and make people more productive using Excel.
brilliant...!
Thank you :)
TILL NOW! lol this is a great explanation - very straightforward
Thank you very much...I have been looking for this specific situation. In other videos, the total were ordered in a descending order.
Most Welcome :)
That was super clear and helpful. Thanks!
Worked perfectly! Thanks for sharing!
This one is very helpful for me ...
Thanks for making this beautiful video😍😍😍😍
thank you for explaining this so well!!!
wonderful !! thank you for this video. you saved me. keep it up
It works and helpful thanks a lot
thanks..i've been searching for this fourmula
Thank you so much... Great help... More power...
Bravo......Very Helpful.......
Thanks,it was very helpful. I wonder if there is a way to avoid repetitive ranks while ranking based on multiple criteria, for example football league table.
Thank you so much Mr.
Excellent, Excellent 👏👏👏👏Thanks a ton
thanks for this video
Solved my problem, thanks!
thank you so much, it was so helpful
Thanks you Sir. It is of great help.
It's helpfully video my jobs, so brilliant thanks
Clear, well explained, thanks
Thank you :)
Very helpful. Thank you
Wonderful, please continue to do more videos, videos are very useful
Excellent, thanks for this.
Thanks Stephen, do subscribe to the channel to never miss out! :)
Great, This is exactly what I need
What formula should I use to rank students of duplicate scores different on basis of another parameter, which must not be included in score?
For an example, if 2 students got same marks in exam the one with higher attendance will rank higher than the other.
bahut bahut dhanyubaad
thank you very very very very much sir.... you have solve my problem..
Thank you so much !!! :)
Oh dude you saved my day!
Thank you, this is what i looking for.. (y)
Thanksss...so helpfull for my case
The purpose of this video was well explained. However, you have the students ranked alphabetically for "total" then the "the new rank formula" just cedes the "total" I scrolled down on the info tab and couldn't find a previous video explain that pre ranking formula. In sports (football) could be rank based on points>goal diff>goals scored>goals against>alphabetically, etc...
Great stuff, thanks.
Thank you Thomas!
thanks 4d tutorial
Awesome. However, when using dollar values, you may need to round to the nearest dollar.
Thanks!
thank You
thanks it is helpful.
Excellent explanation
Superb
Thanks
Great.!!
THIS WAS ABSOLUTELY WONDERFUL! THANK YOU SO MUCH
So helpful
Hi. Just subscribed to your channel. I have a question on ranking
and formatting. I would like to format cells e.g. A1, C1, E1 and G1
based on their ranks i.e from the lowest to the highest assigning colors
based on what is there. Kindly assist. Thanks
Genius!!
Good one... What if a student get high PC and fail in a subject and I want to exclude him ?
would it be unfair for the student with the same scores to rank lower?
Can you please help
Please give the correct formula of RANK like this
A-100--1 rank
B-100--1 rank
C-100--1 rank
D-90--2 rank
E-80--3 rank
F-100--1rank
G-90--2 rank
H-100--1 rank
I-90--2 rank
J-80--3 rank
Here if the student getting same no they got all same rank
And there is no skip of any rank also
If a student got 100 then for every student it is rank 1 not 1,2,3,4....
Its Nice. Can you explain if someone got better marks in any particular sub. then his rank will be better?
Hi, we are taking a total of marks of all subjects, hence the formula is created for the Total. If you want for only 1 subject, run the same formula for marks of only that subject. Will work perfectly well. Hope that helps...
Nice video
nice
what if u have 3 times the same number?
I have a different requirement…let us say we have a table of students with scores for three subjects…(Maths, Science, social studies) apply rank and ranking will be there basis total score they have earned…if two students end up having same rank because of total score being equal, excel should check what’s the score against math for both students, whoever has higher score gets the higher rank..if math score is also matching then it checks science score..so on so forth…how can we achieve such ranking in excel
What happens Sir, if there are 3 of the rank 12 and sometimes 7 of the same rank. TQ
Thank you for producing an informative video. This is assuming that only 1 number i.e. 576 appeared twice, but if more than one number appeared twice, how to work out the ranking
Hi, the same will work even then. Try it out.
@@Excelrush Thanks I will.
@@Excelrush Thank you very much, I tried and it worked. In fact, I changed one of the digits to represent the same number thrice and it still worked.
Nice sir... I have one doubt ... Give ur suggestion... If some one fail in one subject, wat is a formula type in Excel.. can u give formula... If Fail student, I need no rank to be displayed...
From this formula rank comes 1,2,2,4,5,5,7 or 12345 but if we want rank with 1,2,2,3,4,4,5 than formula is?
how to do rank if total marks from students when total subject take it by student is not same. means student A take 10 subject and student B take 11 subject......cause rank base whose student get higher average. how to do Sir?
october sancho 10
october sancho 20
october sancho 30
october sancho 40
october neymar 50
how could i calculate the top 3 scores for sancho in october? i have a data set where 1000 players in october and i need to sum the top three for each player for that month. i tried to do this =IF(AND(E17=C17:C21,F16=B17:B21),SUM(LARGE(D17:D21,{1,2,3}))"") but due to the last part of the if function since it says neymar at the bottom it leaves it blank.
Thanks for good explanation, but what if a values comes more then 2 times....?
Hi, the same will work even then
IF RANKS ARE SAME THEN HOW WILL WE GIVE DIFFERENT RANK THROUGH DIFFERENT CRITERIA LIKE AGE SALARY ETC ?
Suppose 2 students get the same marks and get same rank . If both of them got 2 rank then the 3 rank doesn't show by applying this formula its start from 4 , how to set rank according like 1 2 2 3 4 5 5 6 7 8
Reply please
total mark GPA
850 5.00
870 4.96
840 5.00
866 4.96 How to rank
This formula is not working properly for large data which include blanks.
Sr, plz share this File...Thanks
What if 2 values appear 3 times?
how to put that dollar type sign while finding rank
+Rock to hell hi, select part of the formula that you want the dollar sign to be in, and press F4 on your keyboard.
could not work like this, "countif" return the same number for all the same
How to adding some conditions...If Above 400first rank 350 above second rank like thst
Hi, you need to learn the IF condition to achieve this
Supper and
Yes you have great idea but when 2 students get same mark they are able to get same rank, but the problem was just imagine to student get same rank 2 the next rank student get rank 3 but it was rank 4 come, how to solve it kind tale us
This doesn't work if the duplicates are more than two
38 to 1
34 to 2
34 to 2
32 to 3
Is there any method to get the ranks as above?
Thank you in advance.
There you go!! czcams.com/video/gbxJXUVaHpI/video.html
Thank you for inspiring me to create this video :)
Do subscribe to the channel!
Rank + countif-1 doesn't work. I want the rank as if we give prizes for a competition. 1st, 2nd, 3rd, etc.
38 to 1
34 to 2
34 to 2
32 to 3
Yes, i've made a video for this only, go through this link - czcams.com/video/gbxJXUVaHpI/video.html
Can u help me sir same as its in vba program
Hi, I didn't understand your query. Can you please elaborate more?
I need another formula
example if the student got the same marks at that time they got the same rank but another student give the next number
where is the 11 and 18 value in rank column.
Hi Naveed, the objective is to not get duplicate ranks here. See the entire video to know how to achieve this.
TELL ME HOW I CAN'T GET RANK FOR FAILED ONES
Hi, could you elaborate your question a bit more please?
Not satisfying...
Bcoz itstoo difficult and no.result found for me...
The rank is not sort fr me an follow your instructions
But if the students get the same total mark , then they should get the same rank.
This is confusing🤔🤔🤔🤔
This doesnt work for me.
My 1 become 2😢
I have faced a problem use rank function....
Problem:
When I find my ranking for A1:A5
A1.value =95
A2.value =75
A3.value =95
A4.value =90
A5.value =95
My answer is
(1,5,1,4,1)
I want it ranking continue
Like (1,3,1,2,1)
How solve it?
Thanks
R u get answers,if no I will give....
formula fails for 3rd position
Thanks