RANK Function - Formula, Examples, How to Use Rank in Excel

The RANK Function[1] is categorized as an Excel Statistical function. The function returns the statistical rank of a given value within a ... Home›Resources›ExcelResources›Functions›RANKFunction RANKFunction Returnsthestatisticalrankofagivenvaluewithinanarrayofvalues Writtenby CFITeam UpdatedJune7,2022 WhatistheRANKFunction? TheRANKFunction[1]iscategorizedasanExcelStatisticalfunction.Thefunctionreturnsthestatisticalrankofagivenvaluewithinasuppliedarrayofvalues.Thus,itdeterminesthepositionofaspecificvalueinanarray.   Formula =RANK(number,ref,[order]) TheRANKfunctionusesthefollowingarguments: Number(requiredargument)–Thisisthevalueforwhichweneedtofindtherank. Ref(requiredargument)–Canbealistof,oranarrayof,orreferenceto,numbers. Order(optionalargument)–Thisisanumberthatspecifieshowtherankingwillbedone(ascendingordescendingorder). 0–isusedfordescendingorder 1–isusedforascendingorder Ifweomittheargument,itwilltakeadefaultvalueof0(descendingorder).Itwilltakeanynon-zerovalueasthevalue1(ascendingorder). Beforeweproceed,weneedtoknowthattheRANKfunctionhasbeenreplacedbyRANK.EQandRANK.AVG.Toenablebackwardcompatibility,RANKstillworksinExcel2016(latestversion),butitmaynotbeavailableinthefuture.IfyoutypethisfunctioninExcel2016,itwillshowayellowtrianglewithanexclamationpoint. HowtousetheRANKFunctioninExcel? Asaworksheetfunction,RANKcanbeenteredaspartofaformulainacellofaworksheet.Tounderstandtheusesofthefunction,let’sconsiderafewexamples: Example1 Assumewearegivenalistofemployeeswiththeirrespectivemedicalreimbursementexpenses.Wewishtorankthemaccordingtototalexpenditure. Torankindescendingorder,wewillusetheformula=RANK(B2,($C$5:$C$10),0),asshownbelow: Theresultwegetisshownbelow: Asseenabove,theRANKfunctiongivesduplicatenumbersthesamerank.However,thepresenceofduplicatenumbersaffectstheranksofsubsequentnumbers.Forexample,asshownabove,inalistofintegerssortedinascendingorder,thenumber100appearstwicewitharankof4.Thenextvalue(25)willberanked6(nonumberwillberanked5). Ifwewantuniqueranks,wecanusetheformula: =RANK(C5,$C$5:C$10,0)+COUNTIF($C$5:C5,C5)-1 Wewillgettheresultsbelow: Forascendingorder,theformulawouldbe: =RANK.EQ(C5,$C$5:C$10,1)+COUNTIF($C$5:C5,C5)-1 Inbothformulas,it’stheCOUNTIFfunctionthatdoesthetrick.WeusedCOUNTIFtofindoutthenumberoftimestherankednumberoccurred.IntheCOUNTIFformula,therangeconsistsofasinglecell($C$5:C5).Aswelockedonlythefirstreference($C$5),thelastrelativereference(C5)changesbasedontherowwheretheformulaiscopied.Thus,forrow7,therangeexpandsto$C$5:C10,andthevalueinC10iscomparedtoeachoftheabovecells. Thus,foralluniquevaluesandfirstoccurrencesofduplicatevalues,COUNTIFreturns1;andwesubtract1attheendoftheformulatorestoretheoriginalrank. Forranksoccurringthesecondtime,COUNTIFreturns2.Bysubtracting1,weincreasedtherankby1point,thuspreventingduplicates.Iftherehappentobe3+occurrencesofthesamevalue,COUNTIF()-1wouldadd2totheirranking,andsoon.    ThingstorememberabouttheRANKFunction #N/A!error–Occurswhenthegivennumberisnotpresentinthesuppliedreference.Also,theRANKfunctiondoesnotrecognizetextrepresentationsofnumbersasnumericvalues,sowewillalsogetthe#N/Aerrorifthevaluesinthesuppliedrefarrayaretextvalues. Ifweprovidelogicalvalues,wewillgetthe#VALUE!error. ClickheretodownloadthesampleExcelfile AdditionalResources ThanksforreadingCFI’sguidetoimportantExcelfunctions!Bytakingthetimetolearnandmasterthesefunctions,you’llsignificantlyspeedupyourfinancialanalysis.Tolearnmore,checkouttheseadditionalCFIresources: ExcelFunctionsforFinance AdvancedExcelFormulasCourse AdvancedExcelFormulasYouMustKnow ExcelShortcutsforPCandMac   ArticleSources RANKFunction FreeExcelTutorial TomastertheartofExcel,checkoutCFI’sFREEExcelCrashCourse,whichteachesyouhowtobecomeanExcelpoweruser. Learnthemostimportantformulas,functions,andshortcutstobecomeconfidentinyourfinancialanalysis.   LaunchCFI’sFreeExcelCoursenow totakeyourcareertothenextlevelandmoveuptheladder! Sharethisarticle CorporateFinanceInstitute BacktoWebsite 0searchresultsfor‘’ Peoplealsosearchfor: excel Free freecourses accounting 2 Balancesheet DCF ExploreOurCertifications FinancialModeling&ValuationAnalyst(FMVA)® Learnmore CommercialBanking&CreditAnalyst(CBCA)™ Learnmore CapitalMarkets&SecuritiesAnalyst(CMSA)® Learnmore CertifiedBusinessIntelligence&DataAnalyst(BIDA)™ Learnmore Resources ExcelShortcutsPCMac ListofExcelShortcuts Excelshortcuts[citation... FreeFinancialModelingGuide ACompleteGuidetoFinancialModeling Thisresourceisdesignedtobethebestfreeguidetofinancialmodeling!Wewillbreakdownthemostimportant... SQLDataTypes WhatareSQLDataTypes? TheStructuredQueryLanguage(SQL)comprisesseveraldifferentdatatypesthatallowittostoredifferenttypesofinformation... StructuredQueryLanguage(SQL) WhatisStructuredQueryLanguage(SQL)? StructuredQueryLanguage(SQL)isaspecializedprogramminglanguagedesignedforinteractingwithadatabase.... SeeAllResources SeeAll RecentSearches Suggestions FreeCourses ExcelCourses FinancialModeling&ValuationAnalyst(FMVA)®