Table Of Content

(cid:1) (cid:1) Microsoft Excel Functions & Formulas Bernd Held Wordware Publishing, Inc. LibraryofCongressCataloging-in-PublicationData Held,Bernd. MicrosoftExcelfunctions&formulas/byBerndHeld. p. cm. Includesindex. ISBN-13:978-1-59822-011-7 ISBN-10:1-59822-011-X(pbk.) 1. MicrosoftExcel(Computerfile) 2. Business--Computerprograms. 3 Electronicspreadsheets. I. Title. II. Title:MicrosoftExcelfunctionsandformulas. HF5548.4.M523H452006 005.54--dc22 2006020361 ©2007,WordwarePublishing,Inc. AllRightsReserved 1100SummitAve.,Suite102 Plano,Texas75074 Nopartofthisbookmaybereproducedinanyformorbyanymeans withoutpermissioninwritingfromWordwarePublishing,Inc. PrintedintheUnitedStatesofAmerica ISBN-13:978-1-59822-011-7 ISBN-10:1-59822-011-X 10 9 8 7 6 5 4 3 2 1 0607 Microsoft,Excel,VisualBasic,andWindowsareregisteredtrademarksofMicrosoftCorporationinthe U.S. and other countries. Other brand names and product names mentioned in this book are trademarksorservicemarksoftheirrespectivecompanies.Anyomissionormisuse(ofanykind)of servicemarksortrademarksshouldnotberegardedasintenttoinfringeonthepropertyofothers.The publisherrecognizesandrespectsallmarksusedbycompanies,manufacturers,anddevelopersasa meanstodistinguishtheirproducts. Thisbookissoldasis,withoutwarrantyofanykind,eitherexpressorimplied,respectingthe contentsofthisbookandanydisksorprogramsthatmayaccompanyit,includingbutnotlimitedto impliedwarrantiesforthebook’squality,performance,merchantability,orfitnessforanyparticular purpose. Neither Wordware Publishing, Inc. nor its dealers or distributors shall be liable to the purchaseroranyotherpersonorentitywithrespecttoanyliability,loss,ordamagecausedoralleged tohavebeencauseddirectlyorindirectlybythisbook. All inquiries for volume purchases of this book should be addressed to Wordware Publishing,Inc.,attheaboveaddress.Telephoneinquiriesmaybemadebycalling: (972)423-0090 Contents Acknowledgments . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xiii Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xv Chapter1 Formulas in Excel . . . . . . . . . . . . . . . . . . . . . . . . . . 1 Productionperhour. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2 Calculatetheageofapersonindays . . . . . . . . . . . . . . . . . . . . . . 3 Calculateapricereduction . . . . . . . . . . . . . . . . . . . . . . . . . . . 4 Convertcurrency . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5 Convertfromhourstominutes. . . . . . . . . . . . . . . . . . . . . . . . . 6 Determinefuelconsumption . . . . . . . . . . . . . . . . . . . . . . . . . . 7 Calculateyouridealandrecommendedweight. . . . . . . . . . . . . . . . . 8 Thequickcalendar . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9 Designyourownto-dolist. . . . . . . . . . . . . . . . . . . . . . . . . . . 10 Incrementingrownumbers . . . . . . . . . . . . . . . . . . . . . . . . . . 11 Convertnegativevaluestopositive. . . . . . . . . . . . . . . . . . . . . . 12 Calculatetaxes. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13 Combinetextandnumbers . . . . . . . . . . . . . . . . . . . . . . . . . . 14 Combinetextanddate. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15 Combinetextandtime. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16 Aspecialrankinglist. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17 Determinetheaverageoutput. . . . . . . . . . . . . . . . . . . . . . . . . 18 Stocks—gainsandlosses. . . . . . . . . . . . . . . . . . . . . . . . . . . 19 Evaluateprofitability. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20 Determinepercentageofcompletion . . . . . . . . . . . . . . . . . . . . . 21 Convertmilesperhourtokmperhour. . . . . . . . . . . . . . . . . . . . 22 Convertfeetperminutetometerspersecond . . . . . . . . . . . . . . . . 23 Convertliterstobarrels,gallons,quarts,andpints. . . . . . . . . . . . . . 24 ConvertfromFahrenheittoCelsius. . . . . . . . . . . . . . . . . . . . . . 25 ConvertfromCelsiustoFahrenheit. . . . . . . . . . . . . . . . . . . . . . 26 Calculationwithpercentage. . . . . . . . . . . . . . . . . . . . . . . . . . 27 Monitorthedailyproductionplan . . . . . . . . . . . . . . . . . . . . . . . 28 Calculatenumberofhoursbetweentwodates . . . . . . . . . . . . . . . . 29 Determinethepriceperpound . . . . . . . . . . . . . . . . . . . . . . . . 30 Determinehowmanypiecestoputinabox . . . . . . . . . . . . . . . . . 31 Calculatemanpowerrequiredforaproject . . . . . . . . . . . . . . . . . . 32 Distributesales . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 33 Calculateyournetincome. . . . . . . . . . . . . . . . . . . . . . . . . . . 34 iii Calculatepercentageofpricereduction. . . . . . . . . . . . . . . . . . . . 35 Doublingeverythreehours . . . . . . . . . . . . . . . . . . . . . . . . . . 36 Calculatetheaveragespeed. . . . . . . . . . . . . . . . . . . . . . . . . . 37 Chapter2 Logical Functions . . . . . . . . . . . . . . . . . . . . . . . . . . 39 UsetheANDfunctiontocomparetwocolumns . . . . . . . . . . . . . . . 40 UsetheANDfunctiontoshowsalesforaspecificperiodoftime . . . . . . 41 UsetheORfunctiontocheckcellsfortext. . . . . . . . . . . . . . . . . . 42 UsetheORfunctiontocheckcellsfornumbers . . . . . . . . . . . . . . . 43 UsetheIFfunctiontocomparecolumnsandreturnaspecificresult . . . . 44 UsetheIFfunctiontocheckforlarger,equivalent,orsmallervalues . . . . 45 CombineIFwithANDtocheckseveralconditions. . . . . . . . . . . . . . 46 UsetheIFfunctiontodeterminethequarterofayear. . . . . . . . . . . . 47 UsetheIFfunctiontocheckcellsinworksheetsandworkbooks . . . . . . 48 UsetheIFfunctiontocalculatewithdifferenttaxrates . . . . . . . . . . . 49 UsetheIFfunctiontocalculatethecommissionsforindividualsales . . . . 50 UsetheIFfunctiontocomparetwocells . . . . . . . . . . . . . . . . . . . 51 UsetheINTfunctionwiththeIFfunction . . . . . . . . . . . . . . . . . . 52 UsetheTYPEfunctiontocheckforinvalidvalues . . . . . . . . . . . . . . 53 UsetheIFfunctioncombinedinonecellmorethanseventimes . . . . . . 54 UsetheIFfunctiontocheckwhetheradateisinthepastorfuture. . . . . 55 UsetheIFfunctiontocreateyourowntimesheet . . . . . . . . . . . . . . 56 Chapter3 Text Functions. . . . . . . . . . . . . . . . . . . . . . . . . . . . 57 UsetheLEFTandRIGHTfunctionstoseparateatextstringofnumbers . 58 UsetheLEFTfunctiontoconvertinvalidnumberstovalidnumbers . . . . 59 UsetheSEARCHfunctiontoseparatefirstnamefromlastname . . . . . . 60 UsetheMIDfunctiontoseparatelastnamefromfirstname. . . . . . . . . 62 UsetheMIDfunctiontosumthedigitsofanumber . . . . . . . . . . . . . 63 UsetheEXACTfunctiontocomparetwocolumns. . . . . . . . . . . . . . 64 UsetheSUBSTITUTEfunctiontosubstitutecharacters . . . . . . . . . . 65 UsetheSUBSTITUTEfunctiontosubstitutepartsofacell. . . . . . . . . 67 UsetheSUBSTITUTEfunctiontoconvertnumberstowords. . . . . . . . 68 UsetheSUBSTITUTEfunctiontoremoveword-wrappingincells . . . . . 69 UsetheSUBSTITUTEfunctiontocombineandseparate . . . . . . . . . . 70 UsetheREPLACEfunctiontoreplaceandcalculate. . . . . . . . . . . . . 71 UsetheFINDfunctiontocombinetextanddate . . . . . . . . . . . . . . . 73 UsetheUPPERfunctiontoconverttextfromlowercasetouppercase . . . 75 UsetheLOWERfunctiontoconverttextfromuppercasetolowercase. . . 76 UsethePROPERfunctiontoconvertinitialcharactersfrom lowercasetouppercase. . . . . . . . . . . . . . . . . . . . . . . . . . . 77 iv UsetheFIXEDfunctiontoroundandconvertnumberstotext . . . . . . . 78 UsetheTRIMfunctiontodeletespaces . . . . . . . . . . . . . . . . . . . 79 UsetheTRIMfunctiontoconvert“text-numbers”torealnumbers. . . . . 80 UsetheCLEANfunctiontoremoveallnonprintablecharacters. . . . . . . 81 UsetheREPTfunctiontoshowdataingraphicmode . . . . . . . . . . . . 82 UsetheREPTfunctiontoshowdatainachart. . . . . . . . . . . . . . . . 83 UsetheCHARfunctiontocheckyourfonts . . . . . . . . . . . . . . . . . 84 UsetheCHARfunctiontodeterminespecialcharacters. . . . . . . . . . . 85 UsetheCODEfunctiontodeterminethenumericcodeofacharacter . . . 86 UsetheDOLLARfunctiontoconvertnumberstocurrencyintextformat . 87 UsetheTfunctiontocheckforvalidnumbers . . . . . . . . . . . . . . . . 88 UsetheTEXTfunctiontocombineandformattext . . . . . . . . . . . . . 89 Chapter4 Date and Time Functions. . . . . . . . . . . . . . . . . . . . . . 91 Usecustomformattingtodisplaythedayoftheweek . . . . . . . . . . . . 92 UsetheWEEKDAYfunctiontodeterminetheweekend. . . . . . . . . . . 93 UsetheTODAYfunctiontocheckforfuturedates. . . . . . . . . . . . . . 94 UsetheTEXTfunctiontocalculatewiththeTODAYfunction. . . . . . . . 95 UsetheNOWfunctiontoshowthecurrenttime. . . . . . . . . . . . . . . 96 UsetheNOWfunctiontocalculatetime . . . . . . . . . . . . . . . . . . . 97 UsetheDATEfunctiontocombinecolumnswithdateparts. . . . . . . . . 98 UsetheLEFT,MID,andRIGHTfunctionstoextractdateparts. . . . . . . 99 UsetheTEXTfunctiontoextractdateparts . . . . . . . . . . . . . . . . 100 UsetheDATEVALUEfunctiontorecalculatedatesformattedastext . . . 101 UsetheYEARfunctiontoextracttheyearpartofadate . . . . . . . . . . 102 UsetheMONTHfunctiontoextractthemonthpartofadate . . . . . . . 103 UsetheDAYfunctiontoextractthedaypartofadate . . . . . . . . . . . 104 UsetheMONTHandDAYfunctionstosortbirthdaysbymonth. . . . . . 105 UsetheDATEfunctiontoaddmonthstoadate. . . . . . . . . . . . . . . 106 UsetheEOMONTHfunctiontodeterminethelastdayofamonth . . . . 107 UsetheDAYS360functiontocalculatewitha360-dayyear . . . . . . . . 108 UsetheWEEKDAYfunctiontocalculatewithdifferenthourlypayrates. . 110 UsetheWEEKNUMfunctiontodeterminetheweeknumber. . . . . . . 111 UsetheEDATEfunctiontocalculatemonths. . . . . . . . . . . . . . . . 112 UsetheWORKDAYfunctiontocalculateworkdays. . . . . . . . . . . . . 113 UsetheNETWORKDAYSfunctiontodeterminethenumberofworkdays 115 UsetheYEARFRACfunctiontocalculateagesofemployees . . . . . . . 116 UsetheDATEDIFfunctiontocalculatetheagesofemployees. . . . . . . 117 UsetheWEEKDAYfunctiontocalculatetheweeksofAdvent. . . . . . . 118 UsetheTIMEVALUEfunctiontoconverttexttotime . . . . . . . . . . . 119 Useacustomformattocreateatimeformat . . . . . . . . . . . . . . . . 120 UsetheHOURfunctiontocalculatewith100-minutehours . . . . . . . . 121 UsetheTIMEfunctiontocombinesingletimeparts . . . . . . . . . . . . 122 v Chapter5 Basic Statistical Functions. . . . . . . . . . . . . . . . . . . . . 125 UsetheMAXfunctiontodeterminethelargestvalueinarange. . . . . . 126 UsetheMINfunctiontofindtheemployeewiththelowestsales . . . . . 127 UsetheMINfunctiontodetectthesmallestvalueinacolumn. . . . . . . 128 UsetheSMALLfunctiontofindthesmallestvaluesinalist . . . . . . . . 129 UsetheLARGEfunctiontofindthehighestvalues. . . . . . . . . . . . . 130 UsetheINDEX,MATCH,andLARGEfunctionstodetermineand locatethebestsalesperson . . . . . . . . . . . . . . . . . . . . . . . . 131 UsetheSMALLfunctiontocomparepricesandselectthecheapestoffer . 132 UsetheAVERAGEfunctiontocalculatetheaverageoutput . . . . . . . . 133 UsetheSUBTOTALfunctiontosumafilteredlist . . . . . . . . . . . . . 134 UsetheCOUNTfunctiontocountcellscontainingnumericdata. . . . . . 135 UsetheCOUNTAfunctiontocountcellscontainingdata. . . . . . . . . . 136 UsetheCOUNTAfunctiontocountcellscontainingtext. . . . . . . . . . 137 UsetheCOUNTBLANKfunctiontocountemptycells. . . . . . . . . . . 138 UsetheCOUNTAfunctiontodeterminethelastfilledrow. . . . . . . . . 139 UsetheSUBTOTALfunctiontocountrowsinfilteredlists . . . . . . . . 140 UsetheRANKfunctiontodeterminetherankofsales. . . . . . . . . . . 141 UsetheMEDIANfunctiontocalculatethemediansales . . . . . . . . . . 142 UsetheQUARTILEfunctiontocalculatethequartiles . . . . . . . . . . . 143 UsetheSTDEVfunctiontodeterminethestandarddeviation . . . . . . . 144 Chapter6 Mathematical Functions. . . . . . . . . . . . . . . . . . . . . . 145 UsetheSUMfunctiontosumarange. . . . . . . . . . . . . . . . . . . . 146 UsetheSUMfunctiontosumseveralranges . . . . . . . . . . . . . . . . 147 UsetheSUMIFfunctiontodeterminesalesofateam . . . . . . . . . . . 148 UsetheSUMIFfunctiontosumcostshigherthan$1000. . . . . . . . . . 149 UsetheSUMIFfunctiontosumcostsuptoacertaindate . . . . . . . . . 150 UsetheCOUNTIFfunctiontocountphasesthatcostmorethan$1000 . . 151 UsetheCOUNTIFfunctiontocalculateanattendancelist . . . . . . . . . 152 UsetheSUMPRODUCTfunctiontocalculatethevalueoftheinventory . 153 UsetheSUMPRODUCTfunctiontosumsalesofaparticularteam . . . . 154 UsetheSUMPRODUCTfunctiontomultiplyandsumatonce. . . . . . . 155 UsetheROUNDfunctiontoroundnumbers . . . . . . . . . . . . . . . . 156 UsetheROUNDDOWNfunctiontoroundnumbersdown . . . . . . . . . 157 UsetheROUNDUPfunctiontoroundnumbersup . . . . . . . . . . . . . 158 UsetheROUNDfunctiontoroundtimevaluestowholeminutes . . . . . 159 UsetheROUNDfunctiontoroundtimevaluestowholehours. . . . . . . 160 UsetheMROUNDfunctiontoroundpricesto5or25cents . . . . . . . . 161 vi UsetheMROUNDfunctiontoroundvaluestothe nearestmultipleof10or50. . . . . . . . . . . . . . . . . . . . . . . . 162 UsetheCEILINGfunctiontoroundupvaluestothenearest100 . . . . . 163 UsetheFLOORfunctiontorounddownvaluestothenearest100. . . . . 164 UsethePRODUCTfunctiontomultiplyvalues. . . . . . . . . . . . . . . 165 UsethePRODUCTfunctiontomultiplyconditionalvalues. . . . . . . . . 166 UsetheQUOTIENTfunctiontoreturntheintegerportionofadivision. . 167 UsethePOWERfunctiontocalculatethesquareandcuberoots. . . . . . 168 UsethePOWERfunctiontocalculateinterest . . . . . . . . . . . . . . . 169 UsetheMODfunctiontoextracttheremainderofadivision. . . . . . . . 170 ModifytheMODfunctionfordivisorslargerthanthenumber . . . . . . . 171 UsetheROWfunctiontomarkeveryotherrow . . . . . . . . . . . . . . 172 UsetheSUBTOTALfunctiontoperformseveraloperations. . . . . . . . 173 UsetheSUBTOTALfunctiontocountallvisiblerowsinafilteredlist. . . 174 UsetheRANDfunctiontogeneraterandomvalues. . . . . . . . . . . . . 175 UsetheRANDBETWEENfunctiontogeneraterandom valuesinaspecifiedrange. . . . . . . . . . . . . . . . . . . . . . . . . 176 UsetheEVENandODDfunctionstodeterminethe nearesteven/oddvalue . . . . . . . . . . . . . . . . . . . . . . . . . . 177 UsetheISEVENandISODDfunctionstocheckifa numberisevenorodd. . . . . . . . . . . . . . . . . . . . . . . . . . . 178 UsetheISODDandROWfunctionstodetermineoddrows . . . . . . . . 179 UsetheISODDandCOLUMNfunctionstodetermineoddcolumns. . . . 180 UsetheROMANfunctiontoconvertArabicnumeralsto Romannumerals . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 181 UsetheSIGNfunctiontocheckforthesignofanumber. . . . . . . . . . 182 UsetheSUMSQfunctiontodeterminethesquaresum. . . . . . . . . . . 183 UsetheGCDfunctiontodeterminethegreatestcommondivisor . . . . . 184 UsetheLCMfunctiontodeterminetheleastcommonmultiple . . . . . . 185 Chapter7 Basic Financial Functions . . . . . . . . . . . . . . . . . . . . . 187 UsetheSYDfunctiontocalculatedepreciation . . . . . . . . . . . . . . . 188 UsetheSLNfunctiontocalculatestraight-linedepreciation . . . . . . . . 189 UsethePVfunctiontodecideamounttoinvest. . . . . . . . . . . . . . . 190 UsethePVfunctiontocompareinvestments . . . . . . . . . . . . . . . . 191 UsetheDDBfunctiontocalculateusingthedouble-declining balancemethod . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 192 UsethePMTfunctiontodeterminethepaymentofaloan . . . . . . . . . 194 UsetheFVfunctiontocalculatetotalsavingsaccountbalance . . . . . . . 195 UsetheRATEfunctiontocalculateinterestrate . . . . . . . . . . . . . . 197 vii Chapter8 Database Functions. . . . . . . . . . . . . . . . . . . . . . . . 199 UsetheDCOUNTfunctiontocountspecialcells . . . . . . . . . . . . . . 200 UsetheDCOUNTfunctiontocountcellsinarangebetweenxandy . . . 202 UsetheDCOUNTAfunctiontocountallcellsbeginning withthesamecharacter. . . . . . . . . . . . . . . . . . . . . . . . . . 203 UsetheDGETfunctiontosearchforaproductnumber . . . . . . . . . . 204 UsetheDMAXfunctiontofindthemostexpensiveproductinacategory. 205 UsetheDMINfunctiontofindtheleastexpensiveproduct. . . . . . . . . 206 UsetheDMINfunctiontofindtheoldestpersononateam . . . . . . . . 207 UsetheDSUMfunctiontosumsalesofaperiod . . . . . . . . . . . . . . 208 UsetheDSUMfunctiontosumallpricesofacategory thatareaboveaparticularlevel. . . . . . . . . . . . . . . . . . . . . . 209 UsetheDAVERAGEfunctiontodeterminetheaverage priceofacategory. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 210 Chapter9 Lookup and Reference Functions. . . . . . . . . . . . . . . . . 211 UsetheADDRESS,MAX,andMATCHfunctionsto findthelargestnumber . . . . . . . . . . . . . . . . . . . . . . . . . . 212 UsetheADDRESS,MATCH,andMAXfunctionsto findthesmallestnumber . . . . . . . . . . . . . . . . . . . . . . . . . 214 UsetheADDRESS,MATCH,andTODAYfunctionsto sumsalesuptotoday’sdate. . . . . . . . . . . . . . . . . . . . . . . . 215 UsetheVLOOKUPfunctiontolookupandextractdatafromadatabase. . 216 UsetheVLOOKUPfunctiontocompareoffersfromdifferentsuppliers . . 218 UsetheHLOOKUPfunctiontodeterminesalesandcostsofateam. . . . 219 UsetheHLOOKUPfunctiontodeterminesalesforaparticularday . . . . 221 UsetheHLOOKUPfunctiontogeneratealistforaspecificmonth . . . . 222 UsetheLOOKUPfunctiontogetthedirectoryofastore. . . . . . . . . . 223 UsetheLOOKUPfunctiontogettheindicator forthecurrenttemperature. . . . . . . . . . . . . . . . . . . . . . . . 225 UsetheINDEXfunctiontosearchfordatainasortedlist . . . . . . . . . 227 UsetheINDIRECTfunctiontoplay“BattleShip” . . . . . . . . . . . . . 228 UsetheINDIRECTfunctiontocopycellvalues fromdifferentworksheets. . . . . . . . . . . . . . . . . . . . . . . . . 229 UsetheINDEXfunctiontodeterminethelastnumberinacolumn . . . . 230 UsetheINDEXandCOUNTAfunctionstodeterminethe lastnumberinarow. . . . . . . . . . . . . . . . . . . . . . . . . . . . 231 UsetheOFFSETfunctiontosumsalesforaspecifiedperiod . . . . . . . 232 UsetheOFFSETfunctiontoconsolidatesalesforaday . . . . . . . . . . 234 viii UsetheOFFSETfunctiontofiltereveryothercolumn. . . . . . . . . . . 235 UsetheOFFSETfunctiontofiltereveryotherrow. . . . . . . . . . . . . 236 UsetheHYPERLINKfunctiontojumpdirectlytoacell insidethecurrentworksheet . . . . . . . . . . . . . . . . . . . . . . . 237 UsetheHYPERLINKfunctiontolinktotheInternet. . . . . . . . . . . . 239 Chapter10 Conditional Formatting with Formulas. . . . . . . . . . . . . . 241 UsetheWEEKDAYfunctiontodetermineweekendsandshadethem. . . 242 UsetheTODAYfunctiontoshowactualsales. . . . . . . . . . . . . . . . 244 Useconditionalformatstoindicateunavailableproducts . . . . . . . . . . 245 UsetheTODAYfunctiontoshadeaspecialcolumn. . . . . . . . . . . . . 246 UsetheWEEKNUMandMODfunctionstoshadeeveryotherTuesday. . 247 UsetheMODandROWfunctionstoshadeeverythirdrow . . . . . . . . 248 UsetheMODandCOLUMNfunctionstoshadeeverythirdcolumn. . . . 249 UsetheMAXfunctiontofindthelargestvalue . . . . . . . . . . . . . . . 250 UsetheLARGEfunctiontofindthethreelargestvalues. . . . . . . . . . 251 UsetheMINfunctiontofindthemonthwiththeworstperformance . . . 252 UsetheMINfunctiontosearchforthelowestnon-zeronumber. . . . . . 253 UsetheCOUNTIFfunctiontomarkduplicateinputautomatically. . . . . 254 UsetheCOUNTIFfunctiontocheckwhetheranumberexistsinarange. 255 Useconditionalformattingtocontrolfontstylesinaspecificrange . . . . 257 Useauser-definedfunctiontodetectcellswithformulas. . . . . . . . . . 258 Useauser-definedfunctiontodetectcellswithnumericvalues . . . . . . 260 UsetheEXACTfunctiontoperformacase-sensitivesearch. . . . . . . . 262 UsetheSUBSTITUTEfunctiontosearchfortext . . . . . . . . . . . . . 263 Useconditionalformattingtoshadeprojectstepswithmisseddeadlines. . 264 UseconditionalformattingtocreateaGanttchartinExcel. . . . . . . . . 265 UsetheORfunctiontoindicatedifferenceshigherthan5% andlowerthan–5% . . . . . . . . . . . . . . . . . . . . . . . . . . . . 266 UsetheCELLfunctiontodetectunlockedcells. . . . . . . . . . . . . . . 267 UsetheCOUNTIFfunctiontoshadematchingnumbersincolumnB . . . 268 UsetheISERRORfunctiontomarkerrors . . . . . . . . . . . . . . . . . 269 UsetheDATEDIFfunctiontodetermineallfriendsyoungerthan30 . . . 270 UsetheMONTHandTODAYfunctionstofind birthdays inthecurrentmonth . . . . . . . . . . . . . . . . . . . . . . . . . . . 271 Useconditionalformattingtobordersummedrows. . . . . . . . . . . . . 272 UsetheLEFTfunctioninaproductsearch . . . . . . . . . . . . . . . . . 273 UsetheANDfunctiontodetectemptyrowsinarange. . . . . . . . . . . 274 ix