How to use the RANK function - Easy Excel Tutorial

The RANK function in Excel returns the rank of a number in a list of numbers. Use RANK.AVG to return the average rank if more than one number has the same ... RANKinExcel   TheRANKfunctioninExcelreturnstherankofanumberinalistofnumbers.UseRANK.AVGtoreturntheaveragerankifmorethanonenumberhasthesamerank. 1.Ifthethirdargumentisomitted(or0),Excelranksthelargestnumberfirst,secondlargestnumbersecond,etc. Note:whenwedragthisformuladown,theabsolutereference($A$1:$A$9)staysthesame,whiletherelativereference(A1)changestoA2,A3,A4,etc. 2.Ifthethirdargumentis1,Excelranksthesmallestnumberfirst,secondsmallestnumbersecond,etc. Note:theRANKfunctionisanoldfunction.MicrosoftExcelrecommendsusingthenewRANK.EQfunctioninExcel2010orlater.There'snodifferenceatallbetweenthesetwofunctions. 3.TheRANK.AVGfunctioninExcel2010orlaterreturnstheaveragerankifmorethanonenumberhasthesamerank. 4.TocreateacleanRANKfunction,useanamedrangeinsteadofanabsoluterangereference. Explanation:thenamedrangeDatareferstotherangeA1:A9. Ifyou'reusingExcel365orExcel2021,createthefollowingRANKformula.Thisformulawillamazeandimpressyourco-workers. 5.Wewanttoshowthetop3results.Enterthevalue3intocellD2. 6.Tofindthethirdlargestscore,usetheLARGEfunctionbelow.ThenamedrangeScoresreferstotherangeB2:B8. 7.TheFILTERfunctionbelowshowsallstudentswithascoregreaterthanorequaltothethirdlargestscore. Note:theFILTERfunction,enteredintocellF2,fillsmultiplecells.Wow! 8.Finally,addtheSORTfunctiontosortbythesecondcolumn(secondargument),indescendingorder(thirdargument). Note:use1tosortinascendingorder,use-1tosortindescendingorder. 9.ThisRANKformulaworksforeveryN.Forexample,enterthevalue5intocellD2toshowthetop5results. Note:thisdynamicarrayformula,enteredintocellF2,fillsmultiplecells.ThisbehaviorinExcel365/2021iscalledspilling.Tip:downloadtheExcelfile(rightsideofthispage)andgiveitatry. 5/14Completed!Learnmoreaboutstatisticalfunctions> GotoNextChapter:Round Chapter StatisticalFunctions Learnmore,it'seasy Average NegativeNumberstoZero RandomNumbers Rank PercentilesandQuartiles BoxandWhiskerPlot AverageIf Forecast MaxIfsandMinIfs WeightedAverage Mode StandardDeviation Frequency DownloadExcelFile rank.xlsx FollowExcelEasy BecomeanExcelPro 300Examples   Rank•©2010-2022 ExcelisAwesome,we'llshowyou:Introduction•Basics•Functions•DataAnalysis•VBA