This case will use an Excel Workbook as a Tutorial Tool (with learning content incorprated into each worksheet). Within the Workbook there will be multiple worksheets that the student will need to work through and answer questions (creating forumulas and functions). There is a brief video below that can also assist the student in learning about the various Excel formula and function formats. If you're new to Excel, or even if you have some experience with it, you can walk through Excel's most common formulas in this Excel formula tutorial template. With real-world examples and helpful visuals, you'l be able to Sum, Count, Average, and VLOOKUP like a pro with the help of this Excel formula tutorial template. Download the Excel Template, work through each Worksheet (Begin in the Excel Workbook at the START worksheet). Be sure to rename your worksheet as "yourname-Excel Case 1" ".xlsw, i.e. example "johncovone-Excel Case 1.xlsw" Excel Formulas and Functions Video: Note: be sure to Enable Editing in Excel when you open the Excel Workbook. Where it requires that you input a forumula or fiunction, the cell will be highlighted in Yellow and the instructions will direct you to what to insert. When you first open the Excel Workbook, begin with the Start worksheet. Each Worksheet will detail a different skill and provide plenty of information for you to learn and apply that skill, Once you have worked through all the Worksheets and answered all of the questions, upload the spreadsheet to the dropbox. \begin{tabular}{|l|l|l|l|l|l|l|} Start & Basics & Introduction to Functions & AVERAGE & MIN \& MAX & Date \& Time \\ \hline Joining text a \end{tabular} The nest traluation wer itiut in an stiot. This case will use an Excel Workbook as a Tutorial Tool (with learning content incorprated into each worksheet). Within the Workbook there will be multiple worksheets that the student will need to work through and answer questions (creating forumulas and functions). There is a brief video below that can also assist the student in learning about the various Excel formula and function formats. If you're new to Excel, or even if you have some experience with it, you can walk through Excel's most common formulas in this Excel formula tutorial template. With real-world examples and helpful visuals, you'l be able to Sum, Count, Average, and VLOOKUP like a pro with the help of this Excel formula tutorial template. Download the Excel Template, work through each Worksheet (Begin in the Excel Workbook at the START worksheet). Be sure to rename your worksheet as "yourname-Excel Case 1" ".xlsw, i.e. example "johncovone-Excel Case 1.xlsw" Excel Formulas and Functions Video: Note: be sure to Enable Editing in Excel when you open the Excel Workbook. Where it requires that you input a forumula or fiunction, the cell will be highlighted in Yellow and the instructions will direct you to what to insert. When you first open the Excel Workbook, begin with the Start worksheet. Each Worksheet will detail a different skill and provide plenty of information for you to learn and apply that skill, Once you have worked through all the Worksheets and answered all of the questions, upload the spreadsheet to the dropbox. \begin{tabular}{|l|l|l|l|l|l|l|} Start & Basics & Introduction to Functions & AVERAGE & MIN \& MAX & Date \& Time \\ \hline Joining text a \end{tabular} The nest traluation wer itiut in an stiot. This case will use an Excel Workbook as a Tutorial Tool (with learning content incorprated into each worksheet). Within the Workbook there will be multiple worksheets that the student will need to work through and answer questions (creating forumulas and functions). There is a brief video below that can also assist the student in learning about the various Excel formula and function formats. If you're new to Excel, or even if you have some experience with it, you can walk through Excel's most common formulas in this Excel formula tutorial template. With real-world examples and helpful visuals, you'l be able to Sum, Count, Average, and VLOOKUP like a pro with the help of this Excel formula tutorial template. Download the Excel Template, work through each Worksheet (Begin in the Excel Workbook at the START worksheet). Be sure to rename your worksheet as "yourname-Excel Case 1" ".xlsw, i.e. example "johncovone-Excel Case 1.xlsw" Excel Formulas and Functions Video: Note: be sure to Enable Editing in Excel when you open the Excel Workbook. Where it requires that you input a forumula or fiunction, the cell will be highlighted in Yellow and the instructions will direct you to what to insert. When you first open the Excel Workbook, begin with the Start worksheet. Each Worksheet will detail a different skill and provide plenty of information for you to learn and apply that skill, Once you have worked through all the Worksheets and answered all of the questions, upload the spreadsheet to the dropbox. Use the AVERAGE function to get the average of fumbers in a fange of oelk- Select cell DT, then use AutoSum to add an AVERAGE function. Now select ceil GZ, and enter aA AVERAGE function by fyping AVERAGE(G3,G6). In cell D15, you cant uie eitwor AutoSumt'or type to enter another AvERAGe function More information on the web Tiy uning AtotAN or Ma0t here. Use the MiN function to get the smallest number in a range of cells Use the MAX function to get the largest number in a range of cells. Select cell D7, then use the AutoSum Wizard to add a MIN function. Now select cell G, and enter a MAX function by typing = MAX(D3:D6). In cell D15, you can use either the AutoSum Wizard, or type to enter a MIN or MAX function. More information on the web You can use either MuN or Max with multiple ranges, or values to show the geater or lesser of thone values, like imin(Arato,01:1010), of Allabout the MiN function MaN(A1A10,B1), where B1 contain a threshold value, like 10, in which case the formula would All about the MAX function Excel can give you the current date, based on your computer's regional settings. You can also add and subtract Dates. Oheck out the TODAY function, which gives you Todar's date. These are live. or volatie functians, so when you apen your workbock tomortow, it will have tomorrows date. Enter =TODAYO in cell D6. Subtract Dates - Enter your next birthday in MMDDNY format in cell D7. and watch Excel tell you how many days awoy it is by using = D7.D6 in ceet D8. Add Dates - Lef's say you want to know what date a bili is due, of when you need to return a library book. You can add days to a date to find out, in ceat. D10, enter a random number of dsys, in cell 011, we added = D6. D10 to calculate the dive date from today: Deel keeps ifates and times based on the number of deps starting from laruary 1, 1900. Times are bept in fractionad portions of a dey based on minutes is 01/01/2017 1230DHA is whtualy stoced as a27a6. 5200. if the Time or Dage thow up as numbers like that, then. vou can reess Cultt > Number s select a Date or Time Excel can give you the current time, based an your computer's regional settings. You can also add and subtract times. for instance you might need to keep track of how many hours an employee worked each week, and calculate their pay and overtime 1 If cell D29 enter = NOWO, which will give the current time, and will update each time Excel calculates, if you need to change the Time format. you can 90 to Ctrl+1 >. Number > Time > Seiect the folmat you want. 2 Add up hours between times - In cell D36 weve entered w ((D35-D32) (D34-D33) '24, which calculates someone's start and end times, then subracts the time they took for hunch. The 24 at the end of the formula corverts the fractional portion of the day that Excel sees into hours. Youil need to format the cell as a Number though. To do that 90 to Home : Format Cells (Ctri+1)> Number > Number >2 decimals. 3 . If thin formula could tak, it would say, Thake the lime Out and rubtract at from the Time in then suttract the tunch Cut/in Times, then itwitiply thole by 24 to conveit Eecels fractional time to houts", or "((Time In . Time Out)-(Lunch in + Lunch Out) 24. 724 to conver wacefa. If this formula could talk, it would say, Take the Time Out and subtrnct it from the Time in, then subtract the Lunch Out/ln Times, then multiply thase by 24 to cocivert Excel's fractional time to hours, or = (CTime In Time Out)-(Lunch ln - Lunch Out) )24. You can use keyboard shorteuts to enter Dutes and Times that wont continuously thinge: The inrer parentheser () make sure excen cescuatos unm Date - Ctrle: parts of the formula by themselves. The cuter parentheses make time c Ctritshifte: sure Dxcel multiplies the final inner result by 24. Joining text from different cells There afe many times in Excel when you want to join text that's in different cells. Thisexample is very common, where you have first and last names, and want to combine them as first name, last name, or full name. Fortunately, Excel lets us do that with the Ampersand (8e) sign, which you can enter with Shift +7. In cell E3, enter =D38cC3 to join the last and first names. SmithNaricy doesn't look quite night though. We need to add a comma and a space. To do that we il use quotes to create a new text string. This: time, enter =D3de", "86C3. The 8x,2 portion lets us join a corama and space with the text in the celis. To create the full name, we'll join first and last name, but use a space without a comma. In F3, enter = C38xe"QDD. Now we'il use the \& to join text and numbers, not just text and text Look at cells C280029. See how the date and times are in separate cells? You can join them together with the \& symbol like youll see in cells C32C33, but that doesn' look right, does it? Unfortunately, Excel doesn't know how you want to format the numbers, so it breaks them down to their basest format, which is the the Serial date in this case. We need to explicity tell Excel how to format the number portion of the formula, so it displays the way you want in the resulting text string, You can do that with the TEXT function and a format code. In cell C36, enter =C288" "gLTEXT(D28, "MM/DD/YYYY"). MM/DDFYYY is the US format code for MontlvDay/Year, like 09/25/2017. In cell C37, enter = C298" "E2TEXT(D29, "HH:MM AM/PM"). HMM AM/PM is the US format code for Hours Minutes AM or PM, like 1:30 PM. If you don't know what format code to use, you can we ctriti > Number to format any cell the wav you want. Then select the Custom option. You can copy the format code that's diaplayed back to your formula. IF statenents allow you to make logical comparisons between condisions. An IF statement generally says that if one condition is true do something, otherwise do something else. The formulas can return text, values, of even more cakculations. In cell D9 enter =IF(C9="Apple", TRUE, FALSE). The conrect answer is TRUE. Copy D9 to D10. The answer here should be FALSE, because an orange is not an apple. Try another example by looking at the formula in cell D12. We got you started with = IF(C12 Name. antwehe in the workbouk: Miansen. Cick here to learn more More information on the web Al about the If furstion Allibout the IfS furstion A tranand IF gutements Frse Frodi training onoline VLOOKUP is one of the most widely used functions in Excel fand one of our favontes tool). VLOOKUP lets you look up a value in a column on the left, then returns infomation in another column to the right if it finds a match. VLOOKUP says: 1 In cell D22, enter = VLOOKUP(C22,C17:D20,2,FALSE). The correct answer for Apples is 50. VLOOKUP looked for Apples, found it, then went over one columin to the right, and returned the amount. Invariably, you'l run into a situation where vLOOKUP can't find what you asked it to. and it returns an error (NN/A). Sornetimes, it's because the lookup value simply doesn't exist, or it can because the reference cell doesn't have a value yet. 1 If you know your lookup value exists, but want to hide the error if the lookup cell is blank, you can use an If statement. In this case, weil wrap our existing VLOOKUP formela like this in celi D43: = IF(C43 -*", VLOOKUP(C43,C37:D41,2,FALSE)) This says, 7 oell C4.3 equals nothing (7) then retuin nothing, otherwise. return the VLOOKUP's results:. Note the second closing parenthesis at the end of the formula. This coses the If statement. 2. If you're not sure your iookup value exists, but you still want to suppress the UN/A error, you can use an error handling function called IFERROR in ceII GA3: = IFERROR(VLOOKUP(F43,F37:G41,2,FALSE), "). IFERROR says. "If the VLOOKUP returns a valid resuit, then display that, otherwise, display nothing ( (7) '. We displayed nothing here ("), but you can also use numbers (0,1,2, etel, or text, such as "Formula isnt correct" Conditional functions let you sunt average, coumt of get the min or max of a range based on a given condition, or criterib you specify such as, out of al the fiuts in the. ist, how many are apples? Or, how nury oranges are the Honida type? 1. SUMIF hats you sum in one range based on a peccte onteria you look for in another range, ake how micy Apples you have. select oel 017 and type. - SUMIF(C3:C14,C17,D3:014) sUMIF is structured lite this: 2. SUMAFS is the cene as sumif, but in lats you vite multple cateria. So in this example, you can look for fruit and Type, instead of just by fruit Select cell Hif and bye sUMIF50H3:H14,F3-F14, 517, 53:614,617) suMiFs is structived ince this: suMiFs is the same is SUME, but it lets you use multiple criteria. So in this example, you can look for Fiuit and Type, instead of just by finat. Select ceb H17 ahd type nSMIFS(H3:H14,F3:F14,F17, G3.G14,617) SUMIFS is structured like thes: COUNTIF and COUNTIFS let you court values in a range based on a cititeria you specily. Theyre a bit different from the other if and Ifs functions, in that they only have a criteria range. and criteria. They dont tevalute one range, then look in another to summarize. 1 Select cell D64 and type = COUNTIF(C50,C61,C64). COUNTIF is structured What value (text or number) do you sant to look fer? COUNTIFS in the same as sUmis, but it lets you use multiple critecia. 50 in thit example you can laok foc fruit and Type, initead of just by Friat. Select cell H54 and type = COUNTHF (F 50 .f61,F64,G50.G61,664). COUNTIFS is structured the thil: More conditional functions Youlve already secn SUMif, SUMiFs, COUNNTi,, and COu INTIFS. Now you can try on your own with the other functioer, such as AVCRAGEIF/S. MAXIFS. MINIFS. They're all structured the same way, so once yod get one tormula writsen you can just replace the function name with the one you want. We've written all the functions yourll need for cell E106, so you can copyopaste these, or try to bye them yourself for practicei. SUMIF with a value argument Here's an wample of the SUMIF function using greater than (>) to find all values greater than a given amount: =SUMIF(D118:D122,">50n) NoTE: if you find you are making a lot of conditional foemulas you might find that a If you know the name of the function you want, but you're not sure how to build it, you can use the Function Wizard to help you out. 1. Select cell D10, then go to Formulas > Insert Function > type VLOOKUP in the Search for a function box, and press GO. When you see VLOOKUP highlighted, dick OK at the bottom. When you select a function in the list, Excel will display its syntax. Next, enter the function arguments in their respective text boxes, As you enter each one, Excel will evaluate it, and show you its result, with the final result at the bottom. Press OK when you're done, and Excel will enter the formula for you. You shiould end up with -viookun(c10,Cscto8,2.FAist) You can type cell and range refenences, or select them with your mouse. At some point in time, you'll run across a formula that has an error, which Excel will display with fErrorName. Errecs can be helptul, because they point out when something's not working right, but they can be challenging to foc fortunately, there are several options that can help you track down the source of the-error, and fox it. Error checking - Go to Formulas > Error Checking. This will load a dialog that will tell you the general cause for your specific error, In cell D9, the \#N/A error is caused because there is no value matching "Apple", You can. fix this by using a value that does exist, suppress the error with IFERROR. or ignore it and know it will go away when you do use a value that exists. If you click Help on this Error, a help topic specific to the error message will open. If you click Show Calculation Steps, an Evaluate Formula Cicking Options will iet you vet the dialog will lood. rules for when errors in Excel are sisplyed or ignored. Evaluate formuta. fieference Fraluabse Fermula Enarststss - ogcuidecieic30e2tatse The neit rualuation will revit in an errer. 3. Each time you cick Evaluate, Excel will step through the formula one section at a time. It won't necessarily tell you wiyy an error occurs, but it will point out where. from there, look at the help topic to deduce what went wrong with your formula. Use the content presented during this weeks module related to Closing the Books in the Accounting Cycle, answer the following: 1. Should a Closing Date be Set and Why? 2. What are some of the "potential problems" of making adjustments to a companies accounting records after the year has ended and been closed. How can we limit these types of issues? This case will use an Excel Workbook as a Tutorial Tool (with learning content incorprated into each worksheet). Within the Workbook there will be multiple worksheets that the student will need to work through and answer questions (creating forumulas and functions). There is a brief video below that can also assist the student in learning about the various Excel formula and function formats. If you're new to Excel, or even if you have some experience with it, you can walk through Excel's most common formulas in this Excel formula tutorial template. With real-world examples and helpful visuals, you'l be able to Sum, Count, Average, and VLOOKUP like a pro with the help of this Excel formula tutorial template. Download the Excel Template, work through each Worksheet (Begin in the Excel Workbook at the START worksheet). Be sure to rename your worksheet as "yourname-Excel Case 1" ".xlsw, i.e. example "johncovone-Excel Case 1.xlsw" Excel Formulas and Functions Video: Note: be sure to Enable Editing in Excel when you open the Excel Workbook. Where it requires that you input a forumula or fiunction, the cell will be highlighted in Yellow and the instructions will direct you to what to insert. When you first open the Excel Workbook, begin with the Start worksheet. Each Worksheet will detail a different skill and provide plenty of information for you to learn and apply that skill, Once you have worked through all the Worksheets and answered all of the questions, upload the spreadsheet to the dropbox. \begin{tabular}{|l|l|l|l|l|l|l|} Start & Basics & Introduction to Functions & AVERAGE & MIN \& MAX & Date \& Time \\ \hline Joining text a \end{tabular} The nest traluation wer itiut in an stiot. This case will use an Excel Workbook as a Tutorial Tool (with learning content incorprated into each worksheet). Within the Workbook there will be multiple worksheets that the student will need to work through and answer questions (creating forumulas and functions). There is a brief video below that can also assist the student in learning about the various Excel formula and function formats. If you're new to Excel, or even if you have some experience with it, you can walk through Excel's most common formulas in this Excel formula tutorial template. With real-world examples and helpful visuals, you'l be able to Sum, Count, Average, and VLOOKUP like a pro with the help of this Excel formula tutorial template. Download the Excel Template, work through each Worksheet (Begin in the Excel Workbook at the START worksheet). Be sure to rename your worksheet as "yourname-Excel Case 1" ".xlsw, i.e. example "johncovone-Excel Case 1.xlsw" Excel Formulas and Functions Video: Note: be sure to Enable Editing in Excel when you open the Excel Workbook. Where it requires that you input a forumula or fiunction, the cell will be highlighted in Yellow and the instructions will direct you to what to insert. When you first open the Excel Workbook, begin with the Start worksheet. Each Worksheet will detail a different skill and provide plenty of information for you to learn and apply that skill, Once you have worked through all the Worksheets and answered all of the questions, upload the spreadsheet to the dropbox. \begin{tabular}{|l|l|l|l|l|l|l|} Start & Basics & Introduction to Functions & AVERAGE & MIN \& MAX & Date \& Time \\ \hline Joining text a \end{tabular} The nest traluation wer itiut in an stiot. This case will use an Excel Workbook as a Tutorial Tool (with learning content incorprated into each worksheet). Within the Workbook there will be multiple worksheets that the student will need to work through and answer questions (creating forumulas and functions). There is a brief video below that can also assist the student in learning about the various Excel formula and function formats. If you're new to Excel, or even if you have some experience with it, you can walk through Excel's most common formulas in this Excel formula tutorial template. With real-world examples and helpful visuals, you'l be able to Sum, Count, Average, and VLOOKUP like a pro with the help of this Excel formula tutorial template. Download the Excel Template, work through each Worksheet (Begin in the Excel Workbook at the START worksheet). Be sure to rename your worksheet as "yourname-Excel Case 1" ".xlsw, i.e. example "johncovone-Excel Case 1.xlsw" Excel Formulas and Functions Video: Note: be sure to Enable Editing in Excel when you open the Excel Workbook. Where it requires that you input a forumula or fiunction, the cell will be highlighted in Yellow and the instructions will direct you to what to insert. When you first open the Excel Workbook, begin with the Start worksheet. Each Worksheet will detail a different skill and provide plenty of information for you to learn and apply that skill, Once you have worked through all the Worksheets and answered all of the questions, upload the spreadsheet to the dropbox. Use the AVERAGE function to get the average of fumbers in a fange of oelk- Select cell DT, then use AutoSum to add an AVERAGE function. Now select ceil GZ, and enter aA AVERAGE function by fyping AVERAGE(G3,G6). In cell D15, you cant uie eitwor AutoSumt'or type to enter another AvERAGe function More information on the web Tiy uning AtotAN or Ma0t here. Use the MiN function to get the smallest number in a range of cells Use the MAX function to get the largest number in a range of cells. Select cell D7, then use the AutoSum Wizard to add a MIN function. Now select cell G, and enter a MAX function by typing = MAX(D3:D6). In cell D15, you can use either the AutoSum Wizard, or type to enter a MIN or MAX function. More information on the web You can use either MuN or Max with multiple ranges, or values to show the geater or lesser of thone values, like imin(Arato,01:1010), of Allabout the MiN function MaN(A1A10,B1), where B1 contain a threshold value, like 10, in which case the formula would All about the MAX function Excel can give you the current date, based on your computer's regional settings. You can also add and subtract Dates. Oheck out the TODAY function, which gives you Todar's date. These are live. or volatie functians, so when you apen your workbock tomortow, it will have tomorrows date. Enter =TODAYO in cell D6. Subtract Dates - Enter your next birthday in MMDDNY format in cell D7. and watch Excel tell you how many days awoy it is by using = D7.D6 in ceet D8. Add Dates - Lef's say you want to know what date a bili is due, of when you need to return a library book. You can add days to a date to find out, in ceat. D10, enter a random number of dsys, in cell 011, we added = D6. D10 to calculate the dive date from today: Deel keeps ifates and times based on the number of deps starting from laruary 1, 1900. Times are bept in fractionad portions of a dey based on minutes is 01/01/2017 1230DHA is whtualy stoced as a27a6. 5200. if the Time or Dage thow up as numbers like that, then. vou can reess Cultt > Number s select a Date or Time Excel can give you the current time, based an your computer's regional settings. You can also add and subtract times. for instance you might need to keep track of how many hours an employee worked each week, and calculate their pay and overtime 1 If cell D29 enter = NOWO, which will give the current time, and will update each time Excel calculates, if you need to change the Time format. you can 90 to Ctrl+1 >. Number > Time > Seiect the folmat you want. 2 Add up hours between times - In cell D36 weve entered w ((D35-D32) (D34-D33) '24, which calculates someone's start and end times, then subracts the time they took for hunch. The 24 at the end of the formula corverts the fractional portion of the day that Excel sees into hours. Youil need to format the cell as a Number though. To do that 90 to Home : Format Cells (Ctri+1)> Number > Number >2 decimals. 3 . If thin formula could tak, it would say, Thake the lime Out and rubtract at from the Time in then suttract the tunch Cut/in Times, then itwitiply thole by 24 to conveit Eecels fractional time to houts", or "((Time In . Time Out)-(Lunch in + Lunch Out) 24. 724 to conver wacefa. If this formula could talk, it would say, Take the Time Out and subtrnct it from the Time in, then subtract the Lunch Out/ln Times, then multiply thase by 24 to cocivert Excel's fractional time to hours, or = (CTime In Time Out)-(Lunch ln - Lunch Out) )24. You can use keyboard shorteuts to enter Dutes and Times that wont continuously thinge: The inrer parentheser () make sure excen cescuatos unm Date - Ctrle: parts of the formula by themselves. The cuter parentheses make time c Ctritshifte: sure Dxcel multiplies the final inner result by 24. Joining text from different cells There afe many times in Excel when you want to join text that's in different cells. Thisexample is very common, where you have first and last names, and want to combine them as first name, last name, or full name. Fortunately, Excel lets us do that with the Ampersand (8e) sign, which you can enter with Shift +7. In cell E3, enter =D38cC3 to join the last and first names. SmithNaricy doesn't look quite night though. We need to add a comma and a space. To do that we il use quotes to create a new text string. This: time, enter =D3de", "86C3. The 8x,2 portion lets us join a corama and space with the text in the celis. To create the full name, we'll join first and last name, but use a space without a comma. In F3, enter = C38xe"QDD. Now we'il use the \& to join text and numbers, not just text and text Look at cells C280029. See how the date and times are in separate cells? You can join them together with the \& symbol like youll see in cells C32C33, but that doesn' look right, does it? Unfortunately, Excel doesn't know how you want to format the numbers, so it breaks them down to their basest format, which is the the Serial date in this case. We need to explicity tell Excel how to format the number portion of the formula, so it displays the way you want in the resulting text string, You can do that with the TEXT function and a format code. In cell C36, enter =C288" "gLTEXT(D28, "MM/DD/YYYY"). MM/DDFYYY is the US format code for MontlvDay/Year, like 09/25/2017. In cell C37, enter = C298" "E2TEXT(D29, "HH:MM AM/PM"). HMM AM/PM is the US format code for Hours Minutes AM or PM, like 1:30 PM. If you don't know what format code to use, you can we ctriti > Number to format any cell the wav you want. Then select the Custom option. You can copy the format code that's diaplayed back to your formula. IF statenents allow you to make logical comparisons between condisions. An IF statement generally says that if one condition is true do something, otherwise do something else. The formulas can return text, values, of even more cakculations. In cell D9 enter =IF(C9="Apple", TRUE, FALSE). The conrect answer is TRUE. Copy D9 to D10. The answer here should be FALSE, because an orange is not an apple. Try another example by looking at the formula in cell D12. We got you started with = IF(C12 Name. antwehe in the workbouk: Miansen. Cick here to learn more More information on the web Al about the If furstion Allibout the IfS furstion A tranand IF gutements Frse Frodi training onoline VLOOKUP is one of the most widely used functions in Excel fand one of our favontes tool). VLOOKUP lets you look up a value in a column on the left, then returns infomation in another column to the right if it finds a match. VLOOKUP says: 1 In cell D22, enter = VLOOKUP(C22,C17:D20,2,FALSE). The correct answer for Apples is 50. VLOOKUP looked for Apples, found it, then went over one columin to the right, and returned the amount. Invariably, you'l run into a situation where vLOOKUP can't find what you asked it to. and it returns an error (NN/A). Sornetimes, it's because the lookup value simply doesn't exist, or it can because the reference cell doesn't have a value yet. 1 If you know your lookup value exists, but want to hide the error if the lookup cell is blank, you can use an If statement. In this case, weil wrap our existing VLOOKUP formela like this in celi D43: = IF(C43 -*", VLOOKUP(C43,C37:D41,2,FALSE)) This says, 7 oell C4.3 equals nothing (7) then retuin nothing, otherwise. return the VLOOKUP's results:. Note the second closing parenthesis at the end of the formula. This coses the If statement. 2. If you're not sure your iookup value exists, but you still want to suppress the UN/A error, you can use an error handling function called IFERROR in ceII GA3: = IFERROR(VLOOKUP(F43,F37:G41,2,FALSE), "). IFERROR says. "If the VLOOKUP returns a valid resuit, then display that, otherwise, display nothing ( (7) '. We displayed nothing here ("), but you can also use numbers (0,1,2, etel, or text, such as "Formula isnt correct" Conditional functions let you sunt average, coumt of get the min or max of a range based on a given condition, or criterib you specify such as, out of al the fiuts in the. ist, how many are apples? Or, how nury oranges are the Honida type? 1. SUMIF hats you sum in one range based on a peccte onteria you look for in another range, ake how micy Apples you have. select oel 017 and type. - SUMIF(C3:C14,C17,D3:014) sUMIF is structured lite this: 2. SUMAFS is the cene as sumif, but in lats you vite multple cateria. So in this example, you can look for fruit and Type, instead of just by fruit Select cell Hif and bye sUMIF50H3:H14,F3-F14, 517, 53:614,617) suMiFs is structived ince this: suMiFs is the same is SUME, but it lets you use multiple criteria. So in this example, you can look for Fiuit and Type, instead of just by finat. Select ceb H17 ahd type nSMIFS(H3:H14,F3:F14,F17, G3.G14,617) SUMIFS is structured like thes: COUNTIF and COUNTIFS let you court values in a range based on a cititeria you specily. Theyre a bit different from the other if and Ifs functions, in that they only have a criteria range. and criteria. They dont tevalute one range, then look in another to summarize. 1 Select cell D64 and type = COUNTIF(C50,C61,C64). COUNTIF is structured What value (text or number) do you sant to look fer? COUNTIFS in the same as sUmis, but it lets you use multiple critecia. 50 in thit example you can laok foc fruit and Type, initead of just by Friat. Select cell H54 and type = COUNTHF (F 50 .f61,F64,G50.G61,664). COUNTIFS is structured the thil: More conditional functions Youlve already secn SUMif, SUMiFs, COUNNTi,, and COu INTIFS. Now you can try on your own with the other functioer, such as AVCRAGEIF/S. MAXIFS. MINIFS. They're all structured the same way, so once yod get one tormula writsen you can just replace the function name with the one you want. We've written all the functions yourll need for cell E106, so you can copyopaste these, or try to bye them yourself for practicei. SUMIF with a value argument Here's an wample of the SUMIF function using greater than (>) to find all values greater than a given amount: =SUMIF(D118:D122,">50n) NoTE: if you find you are making a lot of conditional foemulas you might find that a If you know the name of the function you want, but you're not sure how to build it, you can use the Function Wizard to help you out. 1. Select cell D10, then go to Formulas > Insert Function > type VLOOKUP in the Search for a function box, and press GO. When you see VLOOKUP highlighted, dick OK at the bottom. When you select a function in the list, Excel will display its syntax. Next, enter the function arguments in their respective text boxes, As you enter each one, Excel will evaluate it, and show you its result, with the final result at the bottom. Press OK when you're done, and Excel will enter the formula for you. You shiould end up with -viookun(c10,Cscto8,2.FAist) You can type cell and range refenences, or select them with your mouse. At some point in time, you'll run across a formula that has an error, which Excel will display with fErrorName. Errecs can be helptul, because they point out when something's not working right, but they can be challenging to foc fortunately, there are several options that can help you track down the source of the-error, and fox it. Error checking - Go to Formulas > Error Checking. This will load a dialog that will tell you the general cause for your specific error, In cell D9, the \#N/A error is caused because there is no value matching "Apple", You can. fix this by using a value that does exist, suppress the error with IFERROR. or ignore it and know it will go away when you do use a value that exists. If you click Help on this Error, a help topic specific to the error message will open. If you click Show Calculation Steps, an Evaluate Formula Cicking Options will iet you vet the dialog will lood. rules for when errors in Excel are sisplyed or ignored. Evaluate formuta. fieference Fraluabse Fermula Enarststss - ogcuidecieic30e2tatse The neit rualuation will revit in an errer. 3. Each time you cick Evaluate, Excel will step through the formula one section at a time. It won't necessarily tell you wiyy an error occurs, but it will point out where. from there, look at the help topic to deduce what went wrong with your formula. Use the content presented during this weeks module related to Closing the Books in the Accounting Cycle, answer the following: 1. Should a Closing Date be Set and Why? 2. What are some of the "potential problems" of making adjustments to a companies accounting records after the year has ended and been closed. How can we limit these types of issues