Excel RANK without Skipping Numbers (Top 3 Report with Duplicates)
Learn how to rank duplicate values in Excel without skipping numbers in the sequence. I also show you how the RANK function works and the difference between Rank.EQ and Rank.AVG functions. We also take a look at understanding how complex Excel array formulas such as SUMPRODUCT with criteria works by breaking down the formula.
0:00 How to use the Excel RANK function
0:51 RANK Function & RANK.EQ
3:30 RANK duplicates but don't skip numbers in between
6:51 Top 3 Report
8:51 SUMPRODUCT & COUNTIF Excel Array formula explained
You might need to create a top 10 or top 3 report in Excel. For example you'd like to get the top 3 values but there are two categories that have the exact same value and both are considered number 2. How can you show both categories as number 2 and not just the first one? VLOOKUP will not help here, because it will return the first match. You'd like ALL matches returned. The solution uses the SUMPRODUCT function together with the Excel COUNTIF function to get the ranking. We then use the TEXTJOIN and IF functions together as an array to get the category names ranked in ascending order.
⯆ DOWNLOAD the workbook here: https://www.xelplus.com/excel-rank-without-skipping-numbers/
LINKS to related videos:
Excel TextJoin Function - https://youtu.be/TMZEUlFGp1U
Excel Lookup Formulas Playlist: https://www.youtube.com/playlist?list=PLmHVyfmcRKyxpMnh_KKfAgp5DF9ydawmi
★ My Online Excel Courses ► https://courses.xelplus.com
✉ Subscribe & get my TOP 10 Excel formulas e-book for free
https://www.xelplus.com/free-ebook/
EXCEL RESOURCES I Recommend: https://www.xelplus.com/resources/
Get Office 365: https://microsoft.msafflnk.net/15OEg
Microsoft Surface: https://microsoft.msafflnk.net/c/1327040/451518/7593
GEAR
Screen recorder: http://techsmith.pxf.io/c/1252781/347799/5161
Main Camera: https://amzn.to/3a5ldBs
Backup Camera: https://amzn.to/2FLiFho
Main Lens: https://amzn.to/39apgeD
Zoom Lens: https://amzn.to/3bd5pN7
Audio Recorder: https://amzn.to/2Uo5rLm
Microphone: https://amzn.to/2xYy9em
Lights: http://amzn.to/2eJKg1U
More resources on my Amazon page: https://www.amazon.com/shop/leilagharani
Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!
#MsExcel
Видео Excel RANK without Skipping Numbers (Top 3 Report with Duplicates) канала Leila Gharani
0:00 How to use the Excel RANK function
0:51 RANK Function & RANK.EQ
3:30 RANK duplicates but don't skip numbers in between
6:51 Top 3 Report
8:51 SUMPRODUCT & COUNTIF Excel Array formula explained
You might need to create a top 10 or top 3 report in Excel. For example you'd like to get the top 3 values but there are two categories that have the exact same value and both are considered number 2. How can you show both categories as number 2 and not just the first one? VLOOKUP will not help here, because it will return the first match. You'd like ALL matches returned. The solution uses the SUMPRODUCT function together with the Excel COUNTIF function to get the ranking. We then use the TEXTJOIN and IF functions together as an array to get the category names ranked in ascending order.
⯆ DOWNLOAD the workbook here: https://www.xelplus.com/excel-rank-without-skipping-numbers/
LINKS to related videos:
Excel TextJoin Function - https://youtu.be/TMZEUlFGp1U
Excel Lookup Formulas Playlist: https://www.youtube.com/playlist?list=PLmHVyfmcRKyxpMnh_KKfAgp5DF9ydawmi
★ My Online Excel Courses ► https://courses.xelplus.com
✉ Subscribe & get my TOP 10 Excel formulas e-book for free
https://www.xelplus.com/free-ebook/
EXCEL RESOURCES I Recommend: https://www.xelplus.com/resources/
Get Office 365: https://microsoft.msafflnk.net/15OEg
Microsoft Surface: https://microsoft.msafflnk.net/c/1327040/451518/7593
GEAR
Screen recorder: http://techsmith.pxf.io/c/1252781/347799/5161
Main Camera: https://amzn.to/3a5ldBs
Backup Camera: https://amzn.to/2FLiFho
Main Lens: https://amzn.to/39apgeD
Zoom Lens: https://amzn.to/3bd5pN7
Audio Recorder: https://amzn.to/2Uo5rLm
Microphone: https://amzn.to/2xYy9em
Lights: http://amzn.to/2eJKg1U
More resources on my Amazon page: https://www.amazon.com/shop/leilagharani
Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!
#MsExcel
Видео Excel RANK without Skipping Numbers (Top 3 Report with Duplicates) канала Leila Gharani
Показать
Комментарии отсутствуют
Информация о видео
Другие видео канала
Properly Calculate Percentile and Percentile RANK in Excel (inc vs exc)7 Things You're Doing WRONG in ExcelCreate an Interactive Top N Report in Excel (includes duplicate values) with Filter FunctionExcel Magic Trick 1354: Rank with Criteria: Rank Football Teams by Total Wins & Division WinsConsolidate & Clean Multiple Excel Sheets in One Pivot TableMaster Excel's SUMPRODUCT FormulaExcel Rank Function with different ranks for same valuesExcel Tips for Teachers Episode 3: Ranking results in ExcelUnique Rank in Excel - Combining Rank and CountIFExcel shortcut keys you SHOULD know!Excel: Extract unique items for dynamic data validation drop down listWhy you SHOULD be USING Custom Number Formatting in Excel3 EASY Ways to Find and Remove Duplicates in Excel4 SMART Ways to use Custom Formatting instead of Conditional Formatting in Excel - Part 1When Should you Use the Hash Sign (#) in Excel Formulas?Excel Rank Top N Values Inc Tied Values with Dynamic Array Formulas5 Excel Functions YOU NEED in 2021 & 2022excel rank without skipping numbers