Transcription
Excel FormulasThe Computer Workshop, hop.com
Lesson Notes
Microsoft Excel FormulasRel 4.3, 6/15/2016Course Number: 0200-240-16-WCourse Release Number: 4.3Software Release Number: 2016June 15, 2016Developed by:Brian Ireson, Marissa Kain, Thelma C. TippieEdited by:Suzanne Hixon, Chad Carr, Thelma Tippie, and Catherine DestadioPublished by:The Computer Workshop, Inc.5131 Post Road, Suite 102Dublin, Ohio 43017(614) 798-9505dbaRoundTown Publishing5131 Post Road, Suite 102Dublin, Ohio 43017Copyright 2016 by RoundTown Publishing. No reproduction or transmittal of any part of this publication,in any form or by any means, mechanical or electronic, including photocopying, recording, storage in aninformation retrieval system, or otherwise, is permitted without the prior consent of RoundTown Publishing.Disclaimer:Round Town Publishing produced this manual with great care to make it of good quality and accurate, andtherefore, provides no warranties for this publication whatsoever, including, but not limited to, the impliedwarranties of merchantability or fitness for specific uses. Changes may be made to this document without notice.Trademark Notices:The Computer Workshop, Inc. and The Computer Workshop logo are registered trademarks of The ComputerWorkshop, Inc. [Microsoft], [Windows], [PowerPoint], [Excel], [Word], [Word for Windows], and [Works]are registered trademarks of Microsoft Corporation. [InDesign] is a registered trademark of Adobe SystemsIncorporated. All other product names and services identified throughout this book are trademarks or registeredtrademarks of their respective companies. Using any of these trade names is for editorial purposes only and in noway is intended to convey endorsement or other affiliation with this manual.
Table ofContentsTable of Contents. iiUsing this Manual. vTo Download Data Files. vConventions. viConventions Used in this Manual. viLesson 1: Logical & Lookup FunctionsLogical Functions.3Names.4Naming Cells.4Editing Names.4IF Functions.7IF Functions.7Nested IF Functions.9Nested IF Statements.9AND Functions.11Using an AND in an IF Function.11OR Functions.13OR Functions.13Using an OR in an IF Function.13NOT Functions.15NOT Functions.15Using a NOT Function in an IF Function.15Lookup Functions.17VLOOKUP Functions.18VLOOKUP Function.18HLOOKUP Function.22HLOOKUP Function.22Data Validation List.24Data Validation List.24Creating a Data Validation List.24MATCH Function.26MATCH Function.26INDEX Function.29INDEX Function.29INDEX Array Form.29INDEX Reference form.30Lesson 2: Complex Summing FunctionsFiltering Data for Unique Values.35Using the Advanced Filter.35SUMIF Functions.38Using the SUMIF Formula.38AVERAGEIF Function.41Using the AVERAGEIF Formula.41Page iiExcel Formulas, Rel 4.3, 6/15/16
Table ofContents,continuedCOUNTIF Functions.43Using the COUNTIF Formula.43SUMIFS Functions.45Using the SUMIFS Formula.45COUNTIFS Functions.46Using the COUNTIFS Formula.46Lesson 3: Date & Time FunctionsTime.53Using the Time Functions.53Adding and Subtracting Times.54Custom Time Formatting Codes.56NOW Function.59Using the NOW Function.59Refreshing the NOW Function.59Dates.61Understanding Dates.61Date Functions.62Using the TODAY Function.62Finding the Difference Between Two Dates.62Using the DATEDIF Function.63Using the Days Formula.65WORKDAY Functions.68Using the WORKDAY Formula.68Using the EDATE Formula.68NETWORKDAYS Functions.71Calculating Working Days.71Finding the Day of the Week.75Using the WEEKDAY Formula.75Finding Week Number.77Using the WeekNum Formula.77Using the ISOWEEKNUM Formula.78Lesson 4: Formula AuditingFormula Auditing.83The Formula Auditing Group.83Tracing Formulas.84Tracing Precedents .84Tracing Dependents.85Removing Arrows.85Errors.88Errors.88Error Messages.88Error Checking Options.89Error Checking.89Excel Formulas, Rel 4.3, 6/15/16Page iii
Table ofContents,continuedPage ivError Checking.90Formula Options.91Evaluating Formulas.95Using the Evaluate Formula Tool.95Watch Window.99Using the Watch Window.99To Add a Cell to the Watch Window.99To Delete a Cell from the Watch Window.100Calculations.102Changing the Calculation Option.102Excel Formulas, Rel 4.3, 6/15/16
Using thisManualWelcome to the Excel Formulas course. This manual and the datafiles are designed to be used for learning, review and referenceafter the class. The data files can be downloaded any time fromThe Computer Workshop website:http://www.tcworkshop.comThere is no login or password required to access these files. Youwill also find handouts and supplementary materials on thewebsite in the Download section.To Download Data FilesOnce on The Computer Workshop website, locate and click theStudent Resources link in the top navigation bar. When on theStudent Resources page, click the Data Files button.1. Data Files page displays a list of general application types.2. Click once on the Microsoft Office Courses link.3. Click once on the software related to the course.4. Click once on the version related to the course.5. If there are multiple folders, click on the TCW folder.6. Click on the course name to download the data files.You can choose to open or save the zipped folders content toyour computer.While on the Student Resources page, you can also accesshandouts by clicking the Handouts button. Handouts are inPDF format and also available to you without login or password.Simply open the PDF and either print or save to your computer.Excel Formulas, Rel 4.3, 6/15/16Page v
ConventionsConventions Used in this ManualThe hands-on exercises (Actions) are written in a two-columnformat. The left column (“Instructions”) gives numberedinstructions, such as what to type, keys to press, commandsto choose from menus, etc. The right column (“Results/Comments”), contains comments describing results of, reasonsfor, quick keys, etc. for the instructions listed on the left. Key names and Functions are bold and enclosed insquare brackets:[Enter], [Tab], [F5], [F10] Keys you press simultaneously are separated by a plus( ) sign, typed in bold and enclosed in square brackets.You do not press the plus.[Shift F5] Keys you press in sequence are separated by a space,bold and enclosed in square brackets.[Home] [Down Arrow] Ribbon tab names are in bold and italic: Example:Home Group names are in bold: Example: Font Dialog box names are in italic: Example: Save As Button names are bold and enclosed in square brackets:Example: [Sort] Information you are to type will be in bold. Example:This is the first day of the rest of your life. Information that you need to supply will be indicatedwith pointed brackets. Example: Type: your name .Page viExcel Formulas, Rel 4.3, 6/15/16
Lesson 1: Logical &Lookup FunctionsLesson OverviewYou will cover the following concepts in thischapter: Logical Functions Names IF Functions Nested IF Functions AND Functions OR Functions NOT Functions Lookup Functions VLOOKUP Functions HLOOKUP Function Data Validation List MATCH Function INDEX FunctionExcel: Formulas4.3, 6/15/2016
Lesson Notes
Lesson 1: Logical & Lookup FunctionsLogicalFunctionsA logical function is one that evaluates an expression or valueand returns a value based upon whether the expression is Trueor False. These are formulas that are commonly used to see if acondition is met or not to determine one action or another.You use a logical function to check a condition. For example, ifyou wanted to determine if your actual expenses are over budgetor determine if a client is eligible for a discount.If you aresearching for textor mathematicalexpressions, rememberto wrap the searchvalues in quotations.Testing whether conditions are true or false and making logicalcomparisons between expressions are common to many tasks.You can use the AND, OR, NOT, and IF functions to createconditional formulas.In the [Logical] formulas drop-down button in the FunctionLibrary Group on the Formulas Tab you will notice TRUE andFALSE functions. These formulas don’t require any argumentsand will simply return the same value as the function. In essenceyou just as easily write True or False into the cell and be donewith it. TRUE() returns True FALSE() returns FalseAlso within this group of functions you will find the IF ,AND,OR, and NOT formulas. These can be used as stand alonefunctions and also in conjunction with an IF to expand thecapabilities of the IF to much broader parameters.Excel: Formulas, Rel. 4.3, 6/15/2016Page 3
Lesson 1: Logical & Lookup FunctionsNamesNaming CellsInstead of referring to cell addresses in formulas it is easier torefer to the cell’s name. This concept was introduced in the Excel2016 Level 2 book in Lesson 5. Therefore, this will be a shortreview of using Names in formulas.Naming cells Select the cell or cells you want to name. Click into the Name Box and type in the name. Remember not to use special characters in thename. Do not use blank spaces. Use underscores or camelcase multi-word names. Press the [Enter] key or the name will not be applied.Editing Names Click the [Name Manager] button in the DefinedNames Group on the Formulas Tab. In the Name Manager dialog box, select the name thatneeds to be edited and click the [Edit] button.Page 4Excel: Formulas, Rel. 4.3, 6/15/2016
Lesson 1: Logical & Lookup FunctionsNames, In the Edit Name dialog box.continued In the Name: field you can fix typos or rename thecell or range. Th Scope: filed is grayed out because that wasdefined when the name was created. Whenassigning names with the Name Box the scope is bydefault global. In the Comment: field you can explain the name. In the Refers to: field you can redefine the cell orcells addresses.If it is only the Refers to: field that needs to be edited, then thiscan be done in the Name Manager dialog box. Select the name to be edited. Click into the Refers To: field at the bottom of thedialog box. Click the [Expand/Collapse] dialog button andhighlight the correct range of cells. Click the [Expand/Collapse] to redisplay the NameManager dialog box. Click the [Check] button to apply the edit. If you forgetthis last step the edit will not be applied. Click the [Close] button to exit the Name Managerdialog box.Excel: Formulas, Rel. 4.3, 6/15/2016Page 5
Action 1 – Naming CellsInstructions:1. Open Loan.xlsx.Results/ Comments:The file is located in the Data Files folder2. Save the file as MyLoan.[F12].Formulas can become quite complex. Toaid you in writing the formulas, create thefollowing range names.3. Select cell B6.This is the first cell to assign a name to.4. Click into the Name Box and type: Statethen, press the [Enter] key.The Name Box will now display the nameState instead of the address B4.5. Repeat steps 3 and 4 for the followingnames.Cells that will referred to in formulas arenow named.RangeA13:C22B25B26I5I7I11Range NameTableMiTaxOhTaxRateYearsFinal Price6. Click the drop-down arrow on the NameBox to see that all the names are listed.If one or more of the names are not listedgo back to the cell and apply the name.7. Save the file.[Ctrl S].Excel: Formulas, Rel. 4.3, 6/15/2016Lesson 1: Logical & Lookup Functions, Page 6
Lesson 1: Logical & Lookup FunctionsIF FunctionsIF FunctionsOne of the most common logical functions is the IF functionwhich performs a comparison, or test, and then displays theresult in the active cell based on the outcome of that test. The IFfunction will return one value if a condition you specify is TRUEand another value if it is FALSE.There are three arguments required in the IF function:IF(logical test,value if true,value if false) logical test: is any value or expression that can beevaluated as true or false value if true: is the value returned if the expression istrue. value if false: is the value returned if the expression isfalse.For example, you need to determine if a salesperson has soldabove a set goal in order to earn a bonus. The goal level is a valueof 5000. If they sold more than that amount they will receive abonus of 5% of their total sales; if not they will not receive theirbonus. The sales total is in cell F5 and the bonus calculation is incell G5. The formula in cell G5 would be as follows:FunctionValue if True If(F5 5000,F5*5%,0)Logical TestValue if FalseIf the salesperson’s sales total is above 5000 then the sales totalwill be multiplied by 5% to determine the amount of the bonus.Should the sales total be less than 5000 then a zero value isreturned.Excel: Formulas, Rel. 4.3, 6/15/2016Page 7
Action 2 – IF FunctionInstructions:1. MyLoan should still be open. If not, openit.Results/ Comments:2. Select cell I9.This cell will show that appropriate salestax based on which state is entered in cellB6 (State).3. Enter this formula. IF(State ”oh”,OhTax,MiTax)The first argument of this formula willdetermine if cell B6 (State) contains thevalue Oh. Since Oh is text it must bewrapped in quotation marks. Shouldcell B6 (State) contain Oh, the formuladisplays the second argument, OhTax; ifnot, then the third argument is returned,MiTax.4. Click into cell B6 (State).5. Type in Oh .6. Reselect cell B6 (State).7. Type in Mi .8. Save the file.This is the cell that is being checked by theIF function in cell I9.The value in cell I9 now shows the valuefrom the cell named OhTax. The IFfunction Logical test argument is not casesensitive.You will change the state to change thesales tax to reflect this change.The value in cell I9 now shows the valuefrom the cell named MiTax.[Ctrl S].Excel: Formulas, Rel. 4.3, 6/15/2016Lesson 1: Logical & Lookup Functions, Page 8
Lesson 1: Logical & Lookup FunctionsNested IFFunctionsA Primary IF canhave up to sevenIFs nested in asingle formula.Nested IF StatementsAt times you will want to check for multiple conditions. This canbe done in a single formula by nesting multiple IF’s inside theformula. The subsequent IF’s are placed in the Value if False partof the formula. Should the Logical Test return a True value thenthe formula stops calculating since an answer has been found. Ifthe Logical Test returns a False value then the next IF function isrun, this continues until either a True response is found or thereare no other Logical Tests to run and the False is returned.Example of a Nested IF statement:Primary IF Function IF(F5 5000,F5*5%,IF(F5 2000,F5*3%,0))Nested IF FunctionIn this example the sales total is in cell F5. If the salesperson’ssales total is greater than 5000 then the sales total will bemultiplied by 5% to determine the bonus. If not, next IF is run tosee if the sales total is greater than 2000. If this is True, then thesales total will be multiplied by 3%. If neither of these LogicalTests prove True, the formula will return a value of 0.Excel: Formulas, Rel. 4.3, 6/15/2016Page 9
Action 3 – Nesting IF FunctionsInstructions:1. MyLoan should still be open. If not, openit.Results/ Comments:2. Select cell A27.We are going to add another state to theregion of states we work in.3. Type in Indiana and press the [Tab]key.This is the additional state.4. Select cell B27.This cell will contain the sales tax rate forthe state.5. Type in 6% and press the [Enter] key.6. Select cell B27 and apply the name InTax.Instead of using the cell address in theformula we want to continue using names.7. Select cell I9.The IF function needs to be editted toinclude the additional state.8. Enter this formula. IF(State ”oh”,OhTax,IF(State ”mi”,MiTax,IF(State ”in”,InTax,“out of region”)))If B6 (State) contains the value oh theformula returns the OhTax value. If not,then the False argument begins a new IFfunction, to a maximum of seven nestedIFs. The final False in this case returns atext value of out of region. In the formulaany text values must be wrapped inquotations marks.9. Select cell B6 (State).To test the formula.10. Type in In and press the [Enter] key.The value in cell I9 now shows the valuefrom the cell named InTax.11. Save and close the file.[Ctrl S] and [Ctrl W].Excel: Formulas, Rel. 4.3, 6/15/2016Lesson 1: Logical & Lookup Functions, Page 10
Lesson 1: Logical & Lookup FunctionsAND FunctionsThere are times when you will need to check multiple cells to seeif they meet a set of parameters and return either a True or Falseanswer. If all the conditions are met then the formula will returna True value. If any of the conditions are not met then the formulareturns a False value. One common use for the AND function isto expand the usefulness of other functions that perform logicaltests. For example, the IF function performs a Logical Test andthen returns one value if the test evaluates to TRUE and anothervalue if the test evaluates to FALSE. By using the AND functionas the Logical Test argument of the IF function, you can test manydifferent conditions instead of just one.The AND function syntax has the following arguments: logical1: The first condition that you want to testthat can evaluate to either a TRUE or FALSE value. Arequired argument. logical2: Additional conditions that you want to testwhich can evaluate to either a TRUE or FALSE value.An optional argument, there can be up to a maximumof 255 conditions.The AND function is written like this:FunctionLogical Test 2 AND(F5 5000, A5 10)Logical Test 1If the value in cell F5 is greater than 5000 and the value in cell A5is equal to 10 then the formula will return a True value. If eitherof the values does not match the parameters of the Logical Tests inthe AND function then the formula will return a False value.Using an AND in an IF FunctionTo perform multiple Logical Tests in an IF function, use the ANDfunction as the Logical Test of the IF. In the example below, ifboth conditions of the AND are true, then the IF returns the ValueIf True. Should either of the AND’s Logical Tests not be true, thenthe IF will return the Value If False.Primary IF formulaValue IF False IF(AND(F5 5000,A5 10),F5*5%,0)Nested AND formulaExcel: Formulas, Rel. 4.3, 6/15/2016Value IF TruePage 11
Action 4 – AND FunctionsInstructions:1. Open the Logical Formulas file.Results/ Comments:2. Save the file as My Logical Formulas.3. Click on the Summary sheet tab.4. Select cell F4.We are going to see if two conditions aremet in order to get a TRUE return.5. Enter this formula, AND(D4 1800,E4 ”yes”)This formula is to check if the commissionearned is above 1800 and if the contentin cell E4 is Yes. If both conditions aremet then the formulas will return a TRUEvalue.6. Using autofill, fill cells F5:F14.There should a mix of TRUE and FALSEvalues in the cells.7. Select cells F4:F14.We are going to edit all the cells.8. Click into the formula bar to edit theformula.Editing in the formula bar will allow usto apply the edit to all the selected cellssimultaneously by pressing [Ctrl Enter].9. Enter this formula IF(AND(D4 1800,E4 ”yes”),“Gold”,IF(AND(D4 1600,E4 ”Yes”),“Silver”,”Not Eligible”))then, press [Ctrl Enter].This nested formula uses two AND’s intwo IF’s to check for multiple variables todetermine which membership level shouldbe returned.10. Save the file.[Ctrl S].Excel: Formulas, Rel. 4.3, 6/15/2016Lesson 1: Logical & Lookup Functions, Page 12
Lesson 1: Logical & Lookup FunctionsOR FunctionsOR FunctionsWhile the AND function requires that all Logical Tests be true toreturn a TRUE value, the OR function allows any of the LogicalTests to return a true value for the formula to return a TRUEvalue. You may have a situation where multiple conditions mustbe checked for. If any one is true, then the formula will return aTRUE value. Use the OR function, one of the logical functions,to return a TRUE if any argument is TRUE. It returns FALSE if allarguments are false.The OR function syntax uses the following arguments: logical1: The first condition that you want to test thatcan evaluate to either TRUE or FALSE. A requiredargument. logical2: Subsequent logical values are optional. 1to 255 conditions you want to test that can be eitherTRUE or FALSE.The OR function is written like this,FunctionLogical Test 2 OR(F5 5000,A5 10)Logical Test 1In this example, if either condition is true, the formula will returna TRUE value. Both conditions would have to be false for theformula to return a FALSE value.Using an OR in an IF FunctionThis again can be nested in an IF function to expand the scope ofthe IF functionPrimary IF formulaValue If False IF(OR(F5 5000,A5 10),F5*5%,0)Nested OR formulaValue If TrueThe IF formula will return the Value If True if either one of theOR formulas Logical Tests conditions is met. Should both the ORLogical Tests conditions not be met, then the IF will return theValue If False.Excel: Formulas, Rel. 4.3, 6/15/2016Page 13
Action 5 – OR FunctionsInstructions:Results/ Comments:1. My Logical Formulas should still beopen. If not, open it.2. Select cell G4 on the Summary Tab.This is the cell where we will determineif this salesperson has exceeded a 10,000total in any quarter, which will entitlethem for a higher bonus.3. Enter this formula OR(Click on sheet QT1 and highlight cell F4and type: 10000press the [Comma] keyClick on sheet QT2 and highlight cell F4and type: 10000press the [Comma] keyClick on sheet QT3 and highlight cell F4and type: 10000press the [Comma] keyClick on sheet QT4 and highlight cell F4and type: 10000then, press the [Enter] keyWhen finished the formula will read as OR(‘QT1’!F4 10000,’QT2’!F4 10000,’QT3’!F4 10000,’QT4’!F4 10000).If any of these Logical Tests prove true thenthe formula returns a TRUE value.4. Use the autofill to put the formula intocells G5:G14.There should a mix of TRUE and FALSEvalues in the cells.5. Select cell G4 again.We will now nest the OR function in an IFto determine the bonus level they earned.6. Double click into the cell.To edit the formula in the cell instead ofthe formula bar.7. Add these changes to the formula IF(OR(‘QT1’!F4 10000,’QT2’!F4 10000,’QT3’!F4 10000,’QT4’!F4 10000),C4*0.07,C4*0.03) .Add only the bold text to the formula.8. Use the autofill to put the formula intocells G5:G14.If any of the sales team sold more than 10,000 in a quarter they receive a 7%bonus otherwise they receive a 3% bonus.9. Save the file.[Ctrl S].Excel: Formulas, Rel. 4.3, 6/15/2016Lesson 1: Logical & Lookup Functions, Page 14
Lesson 1: Logical & Lookup FunctionsNOT FunctionsNOT FunctionsUse NOT functions when you want to make sure a value is notequal to one particular value since this function reverses thevalue of its argument. If the Logical Test is FALSE, NOT returns aTRUE value; if the Logical Test is TRUE, NOT returns a FALSEvalue. Unlike the AND or OR functions which can containmultiple Logical Tests, the NOT only contains a single Logical Test.The NOT function syntax has the following arguments: Logical Test: A value or expression that can beevaluated as TRUE or FALSE. A required argument.The NOT function is written like this:Function NOT(F5 5000)Logical TestIf the value in cell F5 is less than 5000 then the NOT functionwill return a TRUE value. Should the value in cell F5 prove tobe greater than 5000 then the NOT function will return a FALSEvalue.Using a NOT Function in an IF FunctionUse this combination when you are making sure a parameter youdefine is not met in order to get a positive result. Once the NOTlogical test is met, the IF function will return the Value if Falseargument of the IF formula.Primary IF formulaValue IF True IF(NOT(F5 5000),0,F5*5%)Nested NOT formulaValue IF FalseIn the example above, until the value in cell F5 is above 5000the NOT formula will return a True result and return the ValueIf True argument of the IF. As the value in F5 is greateer than5000 the NOT returns a False value and in turn the IF returnsthe Value if False argument if the IF statement. So you willknow when the value in F5 is greater than 5000 when the cellcontaining the formula begins returning values instead of 0.Excel: Formulas, Rel. 4.3, 6/15/2016Page 15
Action 6 – NOT FunctionsInstructions:Results/ Comments:1. Logical Formulas should still be open. Ifnot, open it.2. Select cell H4.We now want to see if the salespersonshould be under review.3. Enter this formula NOT(F4 ”Gold”).If cell F4 contains the word Gold then theNOT formula returns a FALSE, for anyother content in cell F4 the formula returnsa TRUE.4. Use the autofill to put the formula intocells H5:H14.Only Gold level members should haveFALSE values. These people do not requirereviews.5. Select cell H4.We will modify the formula to return amore easily understood response of YES orNO.6. Add these changes to the formula IF(NOT(F4 ”Gold”),”Yes”,”No”)Add only the bold text to the formula.7. Use the autofill to put the formula intocells H5:H14.Now only the Gold level members have aNO value in the review column, all othershave YES values.8. Save the file.[Ctrl S].Excel: Formulas, Rel. 4.3, 6/15/2016Lesson 1: Logical & Lookup Functions, Page 16 pag
Page 4 Excel: Formulas, Rel. 4.3, 6/15/2016 Names Naming Cells Instead of referring to cell addresses in formulas it is easier to refer to the cell's name. This concept was introduced in the Excel 2016 Level 2 book in Lesson 5. Therefore, this will be a short review of using Names in formulas. Naming cells Select the cell or cells you want to .
