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
