Excel formula: Minimum value if | Exceljet

The Excel MIN function returns the smallest numeric value in the data provided. The MIN function ignores empty cells, the logical values TRUE and FALSE, and ... Skiptomaincontent Minimumvalueif Genericformula  {=MIN(IF(range=criteria,values))} Summary  Togetaminimumvaluebasedoncriteria,youcanusetheMINfunctiontogetherwiththeIFfunction.Intheexampleshown,theformulainG6is: {=MIN(IF(names=F6,times))} Where"names"isthenamedrangeB6:B17,andtimesisthenamedrangeD6:D17. ThisisanarrayformulaandmustbeenteredwithControl+Shift+Enter. Explanation  TheIFfunctionisevaluatedfirstwiththefollowinglogicaltest: names=F6 ThisgeneratesanarrayofTRUE/FALSEvalues,whereTRUEcorrespondstorowswherethenamematchesthevalueinF6: {TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE} The"valueiftrue"forIFisthenamedrange"times",whichreturnsthefullsetoftimes.Theresultisthatthearrayfromthelogicaltesteffectively"filters"thetimevalues.WhenaresultisTRUE,thetimeispassedintoarrayreturnedbyIF.WhenaresultisFALSE,thetimevalueisreplacedbythebooleanFALSE: {0.000868055555555556;FALSE;FALSE;0.000902777777777778;FALSE;FALSE;0.000914351851851852;FALSE;FALSE;0.000833333333333333;FALSE;FALSE} Note:Exceltimesarefractionalvalues,whichexplainsthelongdecimalvalues. TheIFfunctionactslikeafilter.OnlytimevaluesassociatedwithTRUEmakeitthroughthefilter,othervaluesarereplacedwithFALSE. TheIFfunctiondeliversthisarraydirectlytotheMINfunction,returnstheminimumvalueinthearray.FALSEvaluesareautomaticallyignored. Emptycells EmptycellswillcausezerovaluestobepassedintoMIN,whichcancauseunexpectedresults.To"filterout"emptycellsyoucanaddanothernestedIFlikethis: =MIN(IF(names=F6,IF(times<>"",times))) WithMINIFS TheMINIFSfunction,introducedinExcel2016viaOffice365,isdesignedtocalculateminimumsbasedononeormorecriteriawithouttheneedforanarrayformula.WithMINIFS,theformulainG6is: =MINIFS(times,names,F6) Author  DaveBruns Relatedformulas  Maximumvalue TheMAXfunctionacceptsoneormorearguments,whichcanbeamixofconstants,cellreferences,andranges.MAXthenreturnsthemaximumvalueinthedataprovided.Textvaluesandemptycellsareignored.Inthisexample,eachstudenthasfive... Minimumvalue TheMINfunctionacceptsoneormorearguments,whichcanbeamixofconstants,cellreferences,andranges.TheMINfunctionreturnsthemaximumvalueindataprovided.Textvaluesandemptycellsareignored.Inthisexample,eachstudenthas... Maximumvalueif TheIFfunctionisevaluatedfirst.Thelogicaltestisanexpressionthattestsallnames:IF(names=F6//logicaltestTheresultisanarrayofTRUE/FALSEvalueslikethis:{TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE... Minimumifmultiplecriteria Thisexampleusesthefollowingnamedranges:"color"=B6:B14,"item"=C6:C14,and"price"=E6:E14.Intheexample,wehavepricingonitemsinvariousregions.Thegoalistofindtheminimumpriceforagivencoloranditem.Thisformulauses... Maximumifmultiplecriteria Thisexampleusesthefollowingnamedranges:"color"=B6:B14,"item"=C6:C14,and"price"=E6:E14.Thegoalistofindthemaximumpriceforagivencoloranditem.ThisformulausestwonestedIFfunctions,wrappedinsideMAXtoreturnthe... Getnextscheduledevent ThefirstpartofthesolutionusestheMINandTODAYfunctionstofindthe"nextdate"basedonthedatetoday.ThisisdonebyfilteringthedatesthroughtheIFfunction:IF((date>=TODAY()),date)Thelogicaltestgeneratesanarrayof... Relatedfunctions  ExcelMINFunction TheExcelMINfunctionreturnsthesmallestnumericvalueinthedataprovided.TheMINfunctionignoresemptycells,thelogicalvaluesTRUEandFALSE,andtextvalues. ExcelIFFunction TheIFfunctionrunsalogicaltestandreturnsonevalueforaTRUEresult,andanotherforaFALSEresult.Forexample,to"pass"scoresabove70:=IF(A1>70,"Pass","Fail").MorethanoneconditioncanbetestedbynestingIFfunctions.TheIF... ExcelMINIFSFunction TheExcelMINIFSfunctionreturnsthesmallestnumericvaluethatmeetsoneormorecriteriainarangeofvalues.MINIFScanbeusedwithcriteriabasedondates,numbers,text,andotherconditions.MINIFSsupportslogicaloperators(>,... ExcelFormulaTraining FormulasarethekeytogettingthingsdoneinExcel. Inthisacceleratedtraining,you'lllearnhowtouseformulastomanipulatetext,workwithdatesandtimes,lookupvalueswithVLOOKUPandINDEX&MATCH,countandsumwithcriteria,dynamicallyrankvalues,andcreatedynamicranges.You'llalsolearnhowtotroubleshoot,traceerrors,andfixproblems.Instantaccess. Seedetails here. 500Formulas| 101Functions Download100+ImportantExcelFunctions Getover100ExcelFunctionsyoushouldknowinonehandyPDF. Email HP Topics FormulaBasics Formula Examples DynamicArrayFormulas ConditionalFormatting PivotTables ExcelTables Shortcuts More... Keyfunctions IFfunction VLOOKUP function XLOOKUP function FILTER function SUMIFSfunction COUNTIFSfunction SUMPRODUCTfunction INDEXandMATCH Morefunctions... Hi-I'mDaveBruns,andIrunExceljetwithmywife,Lisa.OurgoalistohelpyouworkfasterinExcel.Wecreate shortvideos,andclearexamplesofformulas,functions,pivottables,conditionalformatting,andcharts. Readmore. justwanttosaythatthiswebpageisamasterpieceinExcelworldofart:)hundredsoftimesithelpsmeinExcel.Manythanksandkeepupagoodworkpls!-Pawel Excelvideotraining Quick,clean,andtothepoint. Learnmore