Excel Rank Function with different ranks for same values

Подписаться 24 тыс.

Просмотров 167 тыс.

50% 1

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/...
---------------------------------------------
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.

Опубликовано:

8 сен 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 110

@eyadnahhas5352 7 лет назад

brilliant...!

@Excelrush 7 лет назад

Thank you :)

@SyedPitu Месяц назад

Thanks! I have just resolved an issue related to your tutorial...... May Allah bless you!

@gautamsane1499 5 лет назад

TILL NOW! lol this is a great explanation - very straightforward

@chi-chiowunwanne8384 7 лет назад

Thank you very much...I have been looking for this specific situation. In other videos, the total were ordered in a descending order.

@Excelrush 7 лет назад

Most Welcome :)

@IkhlasAzali 3 года назад

thank you very very very very much sir.... you have solve my problem..

@khalidjamal971 5 лет назад

This one is very helpful for me ... Thanks for making this beautiful video😍😍😍😍

@md.saifulislamtuku9303 4 года назад

It's helpfully video my jobs, so brilliant thanks

@alejandroleiva2061 Год назад

Worked perfectly! Thanks for sharing!

@nickbell6311 Год назад

Wonderful, please continue to do more videos, videos are very useful

@zachariahkuch3787 2 года назад

wonderful !! thank you for this video. you saved me. keep it up

@tonijon4465 6 лет назад

Bravo......Very Helpful.......

@ainidamayanti8012 4 года назад

Thanksss...so helpfull for my case

@Erwin0929 4 года назад

Thank you so much... Great help... More power...

@alphamoron8518 5 лет назад

thanks..i've been searching for this fourmula

@ESCTom 3 года назад

thank you for explaining this so well!!!

@khansvirtualdiary 2 года назад

thanks 4d tutorial

@arunkumar-ws1pk 5 месяцев назад

It works and helpful thanks a lot

@kalaiarasi5104 4 года назад

Excellent, Excellent 👏👏👏👏Thanks a ton

@AujieAlamban Год назад

Very helpful. Thank you

@ReR7474 Год назад

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...

@AnindyaDasAdhikary 5 лет назад

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.

@maalappakoluragi3221 3 года назад

Great.!!

@cyrusamini1988 3 года назад

thank you so much, it was so helpful

@d.jeetesh9835 Год назад

thanks for this video

@khansvirtualdiary 2 года назад

bahut bahut dhanyubaad

@user-xh2qt3gr2u Год назад

thanks it is helpful.

@untoldstories7496 4 года назад

Oh dude you saved my day!

@madelbrosula3036 3 года назад

Thanks!

@gehangunawardena3750 2 года назад

Thank you so much !!! :)

@123umayanga 11 месяцев назад

Thanks

@GFXCROWD 5 лет назад

Excellent explanation

@satyam0504instaid Год назад

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

@pallikkaljabbar8013 11 месяцев назад

Reply please

@Abkhanrj 11 часов назад

Same issue

@wynandgoosen2568 7 лет назад

Clear, well explained, thanks

@Excelrush 7 лет назад

Thank you :)

@LongPiset 3 года назад

thank You

@ericklim7242 3 года назад

Genius!!

@terrymadeley 5 лет назад

That was super clear and helpful. Thanks!

@AmitGupta-fq9vu 4 года назад

Superb

@Innervoice_Sukoon Месяц назад

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....

@rajeshchoudhary2520 5 лет назад

Nice video

@TheSlarge 6 лет назад

Excellent, thanks for this.

@Excelrush 6 лет назад

Thanks Stephen, do subscribe to the channel to never miss out! :)

@GabrielMartinez-ez9ue 7 лет назад

Thank you so much Mr.

@Abdurrahman-qq3du 4 года назад

So helpful

@michaelanibom5135 5 лет назад

Thanks you Sir. It is of great help.

@ociramlap5207 Месяц назад

would it be unfair for the student with the same scores to rank lower?

@Melodician_7 3 года назад

Great, This is exactly what I need

@apexrose 4 года назад

THIS WAS ABSOLUTELY WONDERFUL! THANK YOU SO MUCH

@TheMcallist1 7 лет назад

Great stuff, thanks.

@Excelrush 7 лет назад

Thank you Thomas!

@matthewvovk3545 7 лет назад

Awesome. However, when using dollar values, you may need to round to the nearest dollar.

@lyladrayson5680 5 лет назад

Solved my problem, thanks!

@majdsyrian5378 Год назад

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.

@pratikshasarvaya7998 5 лет назад

nice

@sasikumarkumar1145 3 года назад

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...

@vivekmusafir4492 6 месяцев назад

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

@manojkumarexcel 3 года назад

Supper and

@digitaledubd 6 лет назад

total mark GPA 850 5.00 870 4.96 840 5.00 866 4.96 How to rank

@ricotakaliuang6259 6 лет назад

Thank you, this is what i looking for.. (y)

@rukkyneel2174 4 года назад

Thanks for good explanation, but what if a values comes more then 2 times....?

@Excelrush 4 года назад

Hi, the same will work even then

@noorzafizahbintizaharikpm-1025 2 года назад

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?

@sayanss8936 7 лет назад

Its Nice. Can you explain if someone got better marks in any particular sub. then his rank will be better?

@Excelrush 7 лет назад

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...

@unknwn_usr3077 3 года назад

Good one... What if a student get high PC and fail in a subject and I want to exclude him ?

@user-zo4ws2bo6e 4 месяца назад

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?

@petercatrysse5310 3 года назад

what if u have 3 times the same number?

@AlexGarcia-oi9up 3 года назад

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.

@jasvinder1958 4 года назад

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

@Excelrush 4 года назад

Hi, the same will work even then. Try it out.

@jasvinder1958 4 года назад

@@Excelrush Thanks I will.

@jasvinder1958 4 года назад

@@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.

@Avargh01 7 лет назад

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.

@Excelrush 7 лет назад

There you go!! ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-gbxJXUVaHpI.html Thank you for inspiring me to create this video :) Do subscribe to the channel!

@Avargh01 7 лет назад

Rank + countif-1 doesn't work. I want the rank as if we give prizes for a competition. 1st, 2nd, 3rd, etc.

@Avargh01 7 лет назад

38 to 1 34 to 2 34 to 2 32 to 3

@Excelrush 7 лет назад

Yes, i've made a video for this only, go through this link - ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-gbxJXUVaHpI.html

@rajeshakkineni5127 3 года назад

How to adding some conditions...If Above 400first rank 350 above second rank like thst

@Excelrush 3 года назад

Hi, you need to learn the IF condition to achieve this

@usmaniqbal1836 4 года назад

Sr, plz share this File...Thanks

@blackdragonsports7336 2 года назад

What happens Sir, if there are 3 of the rank 12 and sometimes 7 of the same rank. TQ

@ShikhaSharma-qw4px 5 лет назад

This formula is not working properly for large data which include blanks.

@mcoc4268 5 лет назад

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

@AirdropHunters4u 4 года назад

Can u help me sir same as its in vba program

@Excelrush 4 года назад

Hi, I didn't understand your query. Can you please elaborate more?

@user-dd6vw3rl2t 7 лет назад

how to put that dollar type sign while finding rank

@Excelrush 7 лет назад

+Rock to hell hi, select part of the formula that you want the dollar sign to be in, and press F4 on your keyboard.

@naman881 3 года назад

IF RANKS ARE SAME THEN HOW WILL WE GIVE DIFFERENT RANK THROUGH DIFFERENT CRITERIA LIKE AGE SALARY ETC ?

@dawitadbib 4 года назад

This doesn't work if the duplicates are more than two

@newsodisha1560 5 лет назад

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

@amrelkholy6662 4 года назад

could not work like this, "countif" return the same number for all the same

@umakantbaviskar7857 4 года назад

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

@mc090204666 6 лет назад

where is the 11 and 18 value in rank column.

@Excelrush 6 лет назад

Hi Naveed, the objective is to not get duplicate ranks here. See the entire video to know how to achieve this.

@Siam12348 4 месяца назад

But if the students get the same total mark , then they should get the same rank. This is confusing🤔🤔🤔🤔

@farooqtaj6811 5 лет назад

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