{"text_format":"1","workbook":"xlsx","n_sheets":13,"sheets":["Start","Basics","Introduction to Functions","AVERAGE","MIN & MAX","Date & Time","Joining text and numbers","IF statements","VLOOKUP","Conditional Functions","Function Wizard","Formula Errors","Learn more"],"not_rendered":{"charts":0,"images":27,"macros":0,"external_links":0,"defined_names":21,"comments":0,"pivot_tables":0},"unopened":[],"bounds":{"document_bytes":16777216,"sheet_cells":100000,"rows_first":2000,"rows_last":500,"rows_between":2000,"halvings":0,"cell_chars":16384,"cell_head_chars":4096},"cells_cut":0,"cells_raw":0,"read":"whole","date_system":"1900"}

{"sheet":"Start","index":0,"n_rows":4,"n_cols":1,"rows_kept":4,"cut":false,"hidden":false,"merged":[]}
[]
["Get started with formulas"]
["In just a few steps, you'll be up and running with building formulas and functions in Excel, the world's most powerful spreadsheet app."]
["Go back to top by pressing CTRL+HOME. To start the tour, press CTRL+PAGE DOWN."]

{"sheet":"Basics","index":1,"n_rows":43,"n_cols":10,"rows_kept":43,"cut":false,"hidden":false,"merged":[]}
["Basics: doing math with Excel"]
["You can Add, Subtract, Multiply, and Divide in Excel without using any built-in functions. You just need to use some basic operators: +, -, *, /. All formulas start with an equals (=) sign.\n",null,"Numbers to use:",null,"Operation:","Formulas:","Answers:"]
["To Add, select cell F3, type =C3+C4, then press Enter. \n",null,1,null,"Addition (+) ",null,{"$f":"C3+C4","v":3}]
["To Subtract, select cell F4, type =C3-C4, then press Enter. ",null,2,null,"Subtraction (-) ",null,{"$f":"C3-C4","v":-1}]
["To Multiply, select cell F5, type =C3*C4, then press Enter.\n",null,null,null,"Multiplication (*) ",null,{"$f":"C3*C4","v":2}]
["To Divide, select cell F6, type =C3/C4, then press Enter.\n",null,null,null,"Division (/) ",null,{"$f":"C3/C4","v":0.5}]
["Check this out: change the numbers in cells C3 and C4, and watch the formula results automatically change.",null,null,null,"Power (^) ",null,{"$f":"C3^C4","v":1}]
["EXTRA CREDIT: You can raise a value to a power by using the carat (^) symbol, like =A1^A2. Enter it with Shift+6. In cell F7, enter =C3^C4."]
["Dive down for more detail"]
["Next step"]
["More about formulas, cells, and ranges"]
["Excel is made up of individual cells that are grouped into rows and columns. Rows are numbered, and columns are lettered. There are over 1 million rows and 16,000 columns, and you can put formulas in any of them. \n"]
["Formulas can contain cell references, ranges of cell references, operators, and constants. The following are all examples of formulas:\n\n=A1+B1\n=10*20\n=SUM(A1:A10)\n\n"]
["You'll notice that in our third example above, we used the SUM function. A function is a pre-built command that takes a value or values, calculates them in a certain way, and returns a result. For instance, the SUM function takes the cell references or ranges you specify, and totals them. In this example it takes the cells A1 through A10, and totals them. Excel has over 400 functions, which you can explore on the Formulas tab.\n"]
["Formulas with functions start with an equals sign, then the function name follows with its arguments (the values a function uses to calculate) wrapped in parentheses. \n"]
["You confirm a formula by pressing Enter. Once you do that the formula will calculate, and the result will be displayed in the cell. To see the formula itself, you can look at the formula bar beneath the Ribbon, or press F2 to enter Edit mode, where you'll see the formula in the cell. Press Enter again to finalize the formula and calculate the result.\n\n"]
["Some formula explanations"]
["=10*20 is a formula, where 10 and 20 are constants, and the * sign is the operator."]
["=SUM(A1:A10) is a formula, where SUM is the function name, the opening and closing parentheses contain the formula arguments, and A1:A10 is the cell range for the function."]
["=SUM(A1:A10,C1:C10) is a formula, where SUM is the function name, the opening and closing parentheses contain the formula arguments, and A1:A10,C1:C10 are the cell ranges for the function separated by a comma."]
["GOOD TO KNOW: Constants are values that you enter in cells or formulas. While =10+20 might calculate the same as =A1+B1, constants aren't a good practice. Why? Because you can't easily see the constant without selecting the cell and looking for it. That can make it hard to change later. It's much easier to put your constants in cells, where they can be easily adjusted, and referenced in your formulas.\n\nFor example: Select the yellow cell with 12 below. You'll see we used the SUM function with a range of cells. We didn't type in \"4\" or \"8\" directly into the formula. \n"]
["Previous"]
["Next"]
["More information on the web"]
["Use Excel as a calculator"]
["Overview of formulas in Excel"]
["Excel functions (by category) "]
["Excel functions (alphabetical) "]
["Free Excel training online"]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,null,null,null,null,null,null,null,null,"Values"]
[null,null,null,null,null,null,null,null,null,4]
[null,null,null,null,null,null,null,null,null,8]
[null,null,null,null,null,null,null,null,null,{"$f":"SUM(J41:J42)","v":12}]

{"sheet":"Introduction to Functions","index":2,"n_rows":88,"n_cols":7,"rows_kept":88,"cut":false,"hidden":false,"merged":[]}
["Introduction to functions"]
["Functions give you the ability to do a variety of things, like perform mathematical operations, look up values, or even calculate dates and times. Let's try a few ways to add up values with the SUM function.",null,"Fruit","Amount",null,"Meat","Amount"]
["Under the Amount column for Fruit (cell D7), enter =SUM(D3:D6), or type =SUM(, then select that range with the mouse, and press Enter. This will sum the values in cells D3, D4, D5, and D6. Your answer should be 170.\n",null,"Apples",50,null,"Beef",50]
["Now let's try AutoSum. Select the cell under the column for Meat (cell G7), then go to Formulas > AutoSum > select SUM. You'll see Excel automatically enter the formula for you. Press Enter to confirm it. The AutoSum feature has all of the most common functions.\n",null,"Oranges",20,null,"Chicken",30]
["Here's a neat keyboard shortcut. Select cell D15, then press Alt =, then Enter. This automatically enters SUM for you.",null,"Bananas",60,null,"Pork",10]
["Dive down for more detail",null,"Lemons",40,null,"Fish",50]
["EXTRA CREDIT\nTry the COUNT function using any of the methods you've already tried. The COUNT function counts the number of cells in a range that contain numbers.",null,"SUM > ",{"$f":"SUM(D3:D6)","v":170},null,"SUM > "]
["Next step"]
["More about functions",null,"Item","Amount",null,"Item","Amount"]
["Go to the Formulas tab and browse through the Function Library, where functions are listed by category, like Text, Date & Time, etc. Insert Function will let you search for functions by name, and launch a function wizard that can help you build your formula. \n\nWhen you start typing a function name after you press =, Excel will launch Intellisense, which will list all of the functions starting with the letters you type. When you find the one you want, press Tab, and Excel will automatically finish the function name and enter the opening parenthesis for you. It will also display the optional and required arguments. \n\n\n\nNow let's look at the anatomy of a few functions. The SUM function is structured like this:",null,"Bread",50,null,"Bread",50]
["If the SUM function could talk, it would say, return the sum of all the values in cells D38 to D41, and all of column H. SUM is the function name, D38:D41 is the first range argument, which is almost always required, and H:H is the second range argument, separated by a comma. Now, let's try one that doesn't require any arguments.\n",null,"Donuts",100,null,"Donuts",100]
["The TODAY function returns today's date. It will automatically update when Excel recalculates.",null,"Cookies",40,null,"Cookies",40]
["CHECK THIS OUT\nSelect these cells. Then in the lower-right corner of the Excel window, look for SUM: 170 in the bottom bar. That's called the Status Bar, and it's just another way to quickly find a total and other details about a selected cell or range. ",null,"Cakes",50,null,"Cakes",50]
["IMPORTANT DETAIL\nDouble-click this cell. You'll notice the 100 toward the end. Although it's possible to put numbers in a formula like this, we don't recommend it unless it's absolutely necessary. This is known as a constant, and it's easy to forget that it's there. We recommend referring to another cell instead, like cell F51. That way it's easily seen and not hidden inside a formula. ",null,"Pies",20,null,"Pies",20]
["More information on the web",null,"SUM > ",null,null,"COUNT >"]
["All about the SUM function"]
["Use AutoSum to sum numbers"]
["All about the COUNT function"]
["Free Excel training online"]
["Back to top"]
["Next step"]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,null,"Fruit","Amount"]
[null,null,"Apples",50]
[null,null,"Oranges",20]
[null,null,"Bananas",60]
[null,null,"Lemons",40]
[null,null,"SUM > ",{"$f":"SUM(D35:D38)","v":170}]
[]
[]
[]
[]
[null,null,"Item","Amount"]
[null,null,"Cars",20]
[null,null,"Trucks",10]
[null,null,"Bikes",10]
[null,null,"Skates",40]
[]
[null,null,null,"Total:",null,"Additional Value","New Total"]
[null,null,null,{"$f":"SUM(D45:D48,100)","v":180},null,100,{"$f":"SUM(D45:D48,F51)","v":180}]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
["Free Excel training online"]
["Go back to top by pressing CTRL+HOME. To proceed to the next step, press CTRL+PAGE DOWN."]

{"sheet":"AVERAGE","index":3,"n_rows":46,"n_cols":7,"rows_kept":46,"cut":false,"hidden":false,"merged":[]}
["AVERAGE function"]
["Use the AVERAGE function to get the average of numbers in a range of cells.",null,"Fruit","Amount",null,"Meat","Amount"]
["Select cell D7, then use AutoSum to add an AVERAGE function.",null,"Apples",50,null,"Beef",50]
["Now select cell G7, and enter an AVERAGE function by typing =AVERAGE(G3:G6). ",null,"Oranges",20,null,"Chicken",30]
["In cell D15, you can use either AutoSum, or type to enter another AVERAGE function. ",null,"Bananas",60,null,"Pork",10]
["CHECK THIS OUT\nSelect any range of numbers, then look in the Status Bar for an instant Average.\n",null,"Lemons",40,null,"Fish",50]
["Activate the previous sheet",null,"AVERAGE >",null,null,"AVERAGE >"]
["Go to the next sheet"]
["EXTRA CREDIT\nTry using MEDIAN or MODE here. \n\nMEDIAN gives you the value in the middle of the data set, while \nMODE gives the one that occurs the most frequently.\n",null,"Item","Amount",null,"Item","Amount"]
["Links for more information on the web",null,"Bread",50,null,"Bread",50]
["Select to learn all about the AVERAGE function on the web",null,"Donuts",100,null,"Donuts",100]
["Select to learn all about the MEDIAN function on the web",null,"Cookies",40,null,"Cookies",40]
["Select to learn all about the MODE function on the web",null,"Cakes",50,null,"Cakes",50]
["Select to learn about free Excel training on the web",null,"Pies",20,null,"Pies",20]
[null,null,"AVERAGE >"]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,null,"More about the SUM function "]
[null,null,"In some of the above tips, we taught you how to use the SUM function. Here are more details about it."]
[null,null,"Cells C37 through D41 contain data with two columns: Fruit and Amount. "]
[null,null,"The formula in cell D42: =SUM(D38:D41)."]
[null,null,"If the SUM function in cell D42 could talk, it would say this: Sum up the values in cells D38, D39, D40, and D41."]
[null,null,"Here's another way it can be used:"]
[null,null,"Cells C47 through D48 contain data with two columns: Item and Amount. "]
[]
[]
[]
[]
[]
[null,null,"More information on the web"]
[null,null,"All about the SUM function"]
[null,null,"All about the SUMIF function"]
[null,null,"Use Excel as your calculator"]
[null,null,"Free Excel training online"]
[null,null,"Go back to top by pressing CTRL+HOME. To proceed to the next step, press CTRL+PAGE DOWN."]

{"sheet":"MIN & MAX","index":4,"n_rows":25,"n_cols":7,"rows_kept":25,"cut":false,"hidden":false,"merged":[]}
["MIN and MAX functions"]
["Use the MIN function to get the minumum number in a range of cells.",null,"Fruit","Amount",null,"Meat","Amount"]
["Use the MAX function to get the maximum number in a range of cells.",null,"Apples",50,null,"Beef",50]
["Select cell D7, then use the AutoSum Wizard to add a MIN function.\n",null,"Oranges",20,null,"Chicken",30]
["Now select cell G7, and enter a MAX function by typing =MAX(D3:D6).\n",null,"Bananas",60,null,"Pork",10]
["In cell D15, you can use either the AutoSum Wizard, or type to enter a MIN or MAX function. \n",null,"Lemons",40,null,"Fish",50]
["More information on the web\n",null,"MIN >",null,null,"MAX >"]
["All aobut the MIN function"]
["All about the MAX function",null,"Item","Amount",null,"Item","Amount"]
["Free Excel training online",null,"Bread",50,null,"Bread",50]
["GOOD TO KNOW\nYou can use either MIN or MAX with multiple ranges, or values to show the greater or lesser of those values, like =MIN(A1:A10,B1:B10), or =MAX(A1:A10,B1), where B1 contains a threshold value, like 10, in which case the formula would never return a result less than 10.\n",null,"Donuts",100,null,"Donuts",100]
[null,null,"Cookies",40,null,"Cookies",40]
[null,null,"Cakes",50,null,"Cakes",50]
[null,null,"Pies",20,null,"Pies",20]
[null,null,"MIN or MAX >",null,null,null,{"$f":"MIN(G10:G14,10)","v":10}]
[]
[]
[]
["You can use either MIN or MAX with multiple ranges, or values to show the greater or lesser of those values, like =MIN(A1:A10,B1:B10), or =MAX(A1:A10,10)."]
[]
["More information on the web"]
["All about the MIN function"]
["All about the MAX function"]
["Use Excel as your calculator"]
["Free Excel training online"]

{"sheet":"Date & Time","index":5,"n_rows":47,"n_cols":6,"rows_kept":47,"cut":false,"hidden":false,"merged":[]}
["Date functions"]
["Excel can give you the current date, based on your computer's regional settings. You can also add and subtract Dates."]
["Check out the TODAY function, which gives you Today's date. These are live, or volatile functions, so when you open your workbook tomorrow, it will have tomorrow's date. Enter =TODAY() in cell D6. \n"]
["Subtract Dates - Enter your next birthday in MM/DD/YY format in cell D7, and watch Excel tell you how many days away it is by using =D7-D6 in cell D8.\n"]
["Add Dates - Let's say you want to know what date a bill is due, or when you need to return a library book. You can add days to a date to find out. In cell D10, enter a random number of days. In cell D11, we added =D6+D10 to calculate the due date from today.\n",null,"Date functions"]
["GOOD TO KNOW\nExcel keeps dates and times based on the number of days starting from January 1, 1900. Times are kept in fractional portions of a day based on minutes. So 01/01/2017 12:30 PM is actually stored as 42736.5208. If the Time or Date show up as numbers like that, then you can press Ctrl+1 > Number > select a Date or Time format. ",null,"Today's date:",null,null,{"$f":"IF(D6=TODAY(),\"You got it!\",\"\")","v":""}]
["IMPORTANT DETAIL\nIf you don't want Excel to display a negative number because you haven't entered your birthday yet, you can use an IF function like this: =IF(D7=\"\",\"\",D7-D6), which says, \"IF D7 equals nothing, then show nothing, otherwise show D7 minus D6\".\n",null,"Your birthday:"]
["Time functions",null,"Days until your birthday:",{"$f":"D7-D6","v":0}]
["Excel can give you the current time, based on 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.\n"]
["In cell D28, enter =NOW(), which will give the current time, and will update each time Excel calculates. If you need to change the Time format, you can go to Ctrl+1 > Number > Time > Select the format you want.\n",null,"Grace period days:"]
["Add up hours between times - In cell D36 we've entered =((D35-D32)-(D34-D33))*24, which calculates someone's start and end times, then subracts the time they took for lunch. The *24 at the end of the formula converts the fractional portion of the day that Excel sees into hours. You'll need to format the cell as a Number though. To do that, go to Home > Format > Cells (Ctrl+1) > Number > Number > 2 decimals.\n\n",null,"Bill due on:",{"$f":"D6+D10","v":"00:00:00"}]
["If this formula could talk, it would say, \"Take the Time Out and subtract it from the Time In, then subtract the Lunch Out/In Times, then multiply those by 24 to convert Excel's fractional time to hours\", or =((Time In - Time Out)-(Lunch In - Lunch Out))*24."]
["Previous"]
["Next"]
["More information on the web"]
["All about the TODAY function"]
["All about the NOW function"]
["All about the DATE function"]
["Free Excel training online"]
[]
[]
[]
[]
[]
[]
[]
[null,null,"Time functions"]
[null,null,"Current Time:"]
[]
[]
[null,null,"Daily Hours Worked"]
[null,null,"Time In:","08:00:00"]
[null,null,"Lunch Out:","12:00:00"]
[null,null,"Lunch In:","13:00:00"]
[null,null,"Time Out:","17:00:00"]
[null,null,"Total Hours:",{"$f":"((D35-D32)-(D34-D33))*24","v":8.0000000000000018}]
[]
[]
[]
[]
[]
[]
[]
[]
[null,null,"Static Date & Time"]
[null,null,"Date:","2017-09-27"]
[null,null,"Time:","08:52:00"]

{"sheet":"Joining text and numbers","index":6,"n_rows":37,"n_cols":6,"rows_kept":37,"cut":false,"hidden":false,"merged":[]}
["Joining text from different cells"]
["There are many times in Excel when you want to join text that's in different cells. This example 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 (&) sign, which you can enter with Shift+7.\n",null,"First Name","Last Name","Last Name, First Name","Full Name"]
["In cell E3, enter =D3&C3 to join the last and first names. \n",null,"Nancy","Smith",{"$f":"D3&\", \"&C3","v":"Smith, Nancy"},{"$f":"C3&\" \"&D3","v":"Nancy Smith"}]
["SmithNancy doesn't look quite right though. We need to add a comma and a space. To do that we'll use quotes to create a new text string. This time, enter =D3&\", \"&C3. The &\", \"& portion lets us join a comma and space with the text in the cells.\n",null,"Andy","North"]
["To create the full name, we'll join first and last name, but use a space without a comma. In F3, enter =C3&\" \"&D3.\n",null,"Jan","Kotas"]
["Dive down for more detail",null,"Mariya","Jones"]
["Next",null,"Steven","Thorpe"]
["Using text and numbers together",null,"Michael","Neipper"]
["Now we'll use the & to join text and numbers, not just text and text\n\nLook at cells C28:D29. See how the date and times are in separate cells? You can join them together with the & symbol like you'll see in cells C32:C33, but that doesn't 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.",null,"Robert","Zare"]
["In cell C36, enter =C28&\" \"&TEXT(D28,\"MM/DD/YYYY\"). MM/DD/YYYY is the US format code for Month/Day/Year, like 09/25/2017.\n",null,"Yvonne","McKay"]
["In cell C37, enter =C29&\" \"&TEXT(D29,\"HH:MM AM/PM\"). HH:MM AM/PM is the US format code for Hours:Minutes AM or PM, like 1:30 PM.\n\n"]
["CHECK THIS OUT\nFormulas, especially big ones, can sometimes be hard to read, but you can break up their parts with spaces like this:\n\n=C28 & \" \" & TEXT(D28,\"MM/DD/YYYY\")"]
["WORTH EXPLORING\nIf you don't know what format code to use, you can use Ctrl+1 > Number to format any cell the way you want.  Then select the Custom option. You can copy the format code that's displayed back to your formula."]
["More information on the web"]
["All about the TEXT function"]
["Combine text and numbers"]
["Free Excel training online"]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,null,"Using text & numbers"]
[null,null,"Today's date:",{"$f":"TODAY()","v":"2021-07-05"}]
[null,null,"Current time:",{"$f":"NOW()","v":44382.099458564815}]
[]
[null,null,"Joining text & numbers"]
[null,null,{"$f":"C28&\" \"&D28","v":"Today's date: 44382"}]
[null,null,{"$f":"C29&\" \"&D29","v":"Current time: 44382.0994585648"}]
[]
[null,null,"Formatting text & numbers"]
[null,null,{"$f":"C28 &\" \"& TEXT(D28,\"MM/DD/YYYY\")","v":"Today's date: 07/05/2021"}]
[null,null,{"$f":"C29&\" \"&TEXT(D29,\"HH:MM AM/PM\")","v":"Current time: 02:23 AM"}]

{"sheet":"IF statements","index":7,"n_rows":37,"n_cols":6,"rows_kept":37,"cut":false,"hidden":false,"merged":[]}
["IF statements"]
["IF statements allow you to make logical comparisons between conditions. An IF statement generally says that if one condition is true do something, otherwise do something else. The formulas can return text, values, or even more calculations."]
["In cell D9 enter =IF(C9=\"Apple\",TRUE,FALSE). The correct answer is TRUE. \n"]
["Copy D9 to D10. The answer here should be FALSE, because an orange is not an apple.\n"]
["Try another example by looking at the formula in cell D12. We got you started with =IF(C12<100,\"Less than 100\",\"Greater than or equal to 100\"). What happens if you enter a number greater than 100 in cell C12?\n"]
["IMPORTANT DETAIL\nTRUE and FALSE are unlike other words in Excel formulas in that they don't need to be in quotes, and Excel will automatically capitalize them. Numbers don't need to be in quotes either. Regular text, like Yes or No does need to be in quotes like this: \n=IF(C3=\"Apple\",\"Yes\",\"No\")"]
["Dive down for more detail"]
["Next",null,"IF statements"]
["IF statement with another function",null,"Apple"]
["IF statements can also force additional calculations to be performed if a certain condition is met. Here we're going to evaluate a cell to see if Sales Tax should be charged, and calculate it if the condition is true.\n",null,"Orange"]
["In cell F33, we've entered =IF(E33=\"Yes\",F31*SalesTax,0), where we set up SalesTax as a Named Range with a value of 0.0825. Our formula says If cell E33 equals Yes, then multiply cell F31 times SalesTax, otherwise return a 0.\n\nTry changing Yes to No in cell E33 to see the calculation change."]
["Next we've added an IF statement to calculate shipping if it's required. In cell F35 you'll see =IF(E35=\"Yes\",SUM(D28:D29)*1.25,0). This says \"If cell E35 is Yes, then take the sum of the Quantity column in the table above, and multiply it by 1.25, otherwise return a 0\".\n",null,50,{"$f":"IF(C12<100,\"Less than 100\",\"Greater than or equal to 100\")","v":"Less than 100"}]
["Next, change the 1.25 in the formula in cell F35 to \"Shipping\". As you start typing, Excel's auto-correct, should find it for you. When it does, press Tab to enter it. This is a Named Range, and we entered it from Formulas > Define Name. Now, if you ever need to change your shipping cost, you only have to do it in one place, and you can use the Shipping name anywhere in the workbook.\n\n"]
["GOOD TO KNOW\nWhen you create a formula, Excel will automatically place colored borders around any ranges referenced in the formula, and the corresponding ranges in the formula will be the same color. You can see this if you select cell F33 and press F2 to edit the formula."]
["EXPERT TIP\nNamed Ranges allow you to define terms or values in a single place, and then reuse them throughout a workbook. You can see all of the named ranges in this workbook by going to Formulas > Name Manager. Click here to learn more.\n"]
["Previous"]
["Next"]
["More information on the web"]
["All about the IF function"]
["All about the IFS function"]
["Advanced IF statements"]
["Free Excel training online"]
[]
[]
[]
[]
[null,null,"Item","Quantity","Cost","Total"]
[null,null,"Widget",2,9.7607115856835538,{"$f":"'IF statements'!$E$28:$E$29*'IF statements'!$D$28:$D$29","v":19.521423171367108}]
[null,null,"Doohickey",3,3.4189202461080024,{"$f":"'IF statements'!$E$28:$E$29*'IF statements'!$D$28:$D$29","v":10.256760738324008}]
[]
[null,null,null,"Sub-Total",{"$f":"SUM('IF statements'!$E$28:$E$29)","v":13.179631831791557},{"$f":"SUM('IF statements'!F28:F29)","v":29.778183909691116}]
[]
[null,null,null,"Sales Tax?","Yes",{"$f":"IF(E33=\"Yes\",F31*SalesTax,0)","v":2.456700172549517}]
[]
[null,null,null,"Shipping?","Yes",{"$f":"IF(E35=\"Yes\",SUM(D28:D29)*1.25,0)","v":6.25}]
[]
[null,null,null,"Total",null,{"$f":"SUM(F33,F31,F35)","v":38.484884082240633}]

{"sheet":"VLOOKUP","index":8,"n_rows":43,"n_cols":7,"rows_kept":43,"cut":false,"hidden":false,"merged":[]}
["VLOOKUP"]
["VLOOKUP is one of the most widely used functions in Excel (and one of our favorites too!). VLOOKUP lets you look up a value in a column on the left, then returns information in another column to the right if it finds a match. VLOOKUP says:\n"]
["=VLOOKUP(A1,B:C,2,FALSE)"]
["What do you want to look for?"]
["If you find it, how many columns to the right do you want to get a value?"]
["Where do you want to look for it?"]
["Do you want an exact, or approximate match?"]
["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 column to the right, and returned the amount.\n\n"]
["Now try for yourself in the Meat section, in cell G22. You should end up with =VLOOKUP(F22,F17:G20,2,FALSE).\n\n"]
["EXPERIMENT\nTry selecting different items from the drop down lists. You'll see the result cells instantly update themselves with new values."]
["Dive down for more detail"]
["Next"]
["VLOOKUP and #N/A"]
["Invariably, you'll run into a situation where VLOOKUP can't find what you asked it to, and it returns an error (#N/A). Sometimes, it's because the lookup value simply doesn't exist, or it can because the reference cell doesn't have a value yet.\n"]
["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, we'll wrap our existing VLOOKUP formula like this in cell D43:\n\n=IF(C43=\"\",\"\",VLOOKUP(C43,C37:D41,2,FALSE))\n\nThis says if cell C43 equals nothing (\"\"), then return nothing, otherwise return the VLOOKUP's results. Note the second closing parenthesis at the end of the formula. This closes the IF statement.\n"]
["If you're not sure your lookup value exists, but you still want to suppress the #N/A error, you can use an error handling function called IFERROR in cell G43: =IFERROR(VLOOKUP(F43,F37:G41,2,FALSE),\"\"). IFERROR says if the VLOOKUP returns a valid result, then display that, otherwise, display nothing (\"\"). We displayed nothing here (\"\"), but you can also use numbers (0,1, 2, etc.), or text, such as \"Formula isn't correct\".\n",null,"Fruit","Amount",null,"Meat","Amount"]
["IMPORTANT DETAIL\nIFERROR is what's known as a blanket error handler, meaning it will suppress any error your formula might throw. This can cause problems if Excel is giving you a notification that your formula has a legitimate error that needs to be fixed.\n\nA rule of thumb is to not add error handlers to your formulas until you're absolutely certain they work properly.",null,"Apples",50,null,"Beef",50]
["Previous",null,"Oranges",20,null,"Chicken",30]
["Next",null,"Bananas",60,null,"Pork",10]
["More information on the web",null,"Lemons",40,null,"Fish",50]
["All about the VLOOKUP function"]
["All about the INDEX/MATCH functions",null,"Apples",null,null,"Pork"]
["All about the IFERROR function"]
["Use PivotTables to analyze worksheet data"]
["Free Excel training online"]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,null,"If the SUM function in cell D42 could talk, it would say this: Sum up the values in cells D38, D39, D40, and D41."]
[null,null,"Item","Amount",null,"Item","Amount"]
[null,null,"Bread",50,null,"Bread",50]
[null,null,"Donuts",100,null,"Donuts",100]
[null,null,"Cookies",40,null,"Cookies",40]
[null,null,"Cakes",50,null,"Cakes",50]
[null,null,"Pies",20,null,"Pies",20]
[]
[null,null,null,{"$f":"VLOOKUP(C43,C37:D41,2,FALSE)","v":{"$error":"#N/A"}},null,"Pastry",{"$f":"IFERROR(VLOOKUP(F43,F37:G41,2,FALSE),\"\")","v":""}]

{"sheet":"Conditional Functions","index":9,"n_rows":123,"n_cols":34,"rows_kept":123,"cut":false,"hidden":false,"merged":[]}
["Conditional functions - SUMIF"]
["Conditional functions let you sum, average, count or get the min or max of a range based on a given condition, or criteria you specify. Such as, out of all the fruits in the list, how many are apples? Or, how many oranges are the Florida type?",null,"Fruit","Amount",null,"Fruit","Type","Amount",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Fruit",null,"Apples",null,"Oranges",null,"Bananas",null,"Lemons"]
["SUMIF lets you sum in one range based on a specifc criteria you look for in another range, like how many Apples you have. Select cell D17 and type =SUMIF(C3:C14,C17,D3:D14). SUMIF is structured like this:",null,"Apples",50,null,"Apples","Fuji",50,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Apples",null,"Fuji",null,"Florida",null,"Cavendish",null,"Rough"]
["=SUMIF(C3:C14,C17,D3:D4)",null,"Oranges",20,null,"Oranges","Florida",20,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Oranges",null,"Honeycrisp",null,"Navel",null,"Lady Finger",null,"Eureka"]
["What range do you want to look at?",null,"Bananas",60,null,"Bananas","Cavendish",60,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Bananas"]
["What value (text or number) do you want to look for?",null,"Lemons",40,null,"Lemons","Rough",40,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Lemons"]
["For each match found, what range do you want to sum in?",null,"Apples",50,null,"Apples","Honeycrisp",50]
["SUMIFS is the same as SUMIF, but it lets you use multiple criteria. So in this example, you can look for Fruit and Type, instead of just by Fruit. Select cell H17 and type =SUMIFS(H3:H14,F3:F14,F17,G3:G14,G17). SUMIFS is structured like this:\n",null,"Oranges",20,null,"Oranges","Navel",20]
["=SUMIFS(H3:H14,F3:F14,F17,G3:G14,G17)",null,"Bananas",60,null,"Bananas","Lady Finger",60]
["What range do you want to sum?",null,"Lemons",40,null,"Lemons","Eureka",40]
["This is the first range to look in for matches",null,"Apples",50,null,"Apples","Honeycrisp",50]
["This is the criteria for the first match",null,"Oranges",20,null,"Oranges","Navel",20]
["This is the second range to look in for matches",null,"Bananas",60,null,"Bananas","Cavendish",60]
["This is the criteria for the second match",null,"Lemons",40,null,"Lemons","Eureka",40]
["EXPERT TIP\nEach one of the Fruit and Type cells has a drop-down list where you can select different fruits. Try it, and watch the formulas automatically update."]
["Dive down for more detail",null,"Fruit","SUMIF",null,"Fruit","Type","SUMIFS"]
["Next step",null,"Apples",null,null,"Oranges","Florida"]
["Conditional functions - COUNTIF"]
["COUNTIF and COUNTIFS let you count values in a range based on a criteria you specify. They're a bit different from the other IF and IFS functions, in that they only have a criteria range, and criteria. They don't evalute one range, then look in another to summarize."]
["Select cell D64 and type =COUNTIF(C50:C61,C64). COUNTIF is structured like this:"]
["=COUNTIF(C50:C61,C64)"]
["What range do you want to look at?"]
["What value (text or number) do you want to look for?"]
["COUNTIFS is the same as SUMIF, but it lets you use multiple criteria. So in this example, you can look for Fruit and Type, instead of just by Fruit. Select cell H64 and type =COUNTIFS(F50:F61,F64,G50:G61,G64). COUNTIFS is structured like this:\n"]
["=COUNTIFS(F50:F61,F64,G50:G61,G64)"]
["This is the first range to count"]
["This is the criteria for the first match"]
["This is the second range to count"]
["This is criteria for the second match"]
["Dive down for more detail"]
["Next"]
["More conditional functions"]
["You've already seen SUMIF, SUMIFS, COUNTIF, and COUNTIFS. Now you can try on your own with the other functions, such as AVERAGEIF/S, MAXIFS, MINIFS. They're all structured the same way, so once you get one formula written, you can just replace the function name with the one you want. We've written all the functions you'll need for cell E106, so you can copy/paste these, or try to type them yourself for practice.\n\nSUMIF \t=SUMIF(C92:C103,C106,E92:E103) \nSUMIFS \t=SUMIFS(E92:E103,C92:C103,C106,D92:D103,D106) \nAVERAGEIF \t=AVERAGEIF(C92:C103,C106,E92:E103) \nAVERAGEIFS\t=AVERAGEIFS(E92:E103,C92:C103,C106,D92:D92,D106)\nCOUNTIF \t=COUNTIF(C92:C103,C106)\nCOUNTIFS \t=COUNTIFS(C92:C103,C106,D92:D103,D106) \nMAXIFS \t=MAXIFS(E92:E103,C92:C103,C106,D92:D103,D106)\nMINIFS \t=MINIFS(E92:E103,C92:C103,C106,D92:D103,D106)\n"]
["Dive down for more detail"]
["Next"]
["SUMIF with a value argument"]
["Here's an example of the SUMIF function using greater than (>) to find all values greater than a given amount:"]
[{"$f":"SUMIF(D118:D122,\">=50\")","v":200}]
["Sum up some values based on this criterion:"]
["....Look through these cells...\n "]
["...and if the value is greater than 50, sum it up.\n "]
["NOTE: If you find you are making a lot of conditional formulas, you might find that a PivotTable is a better solution. See this PivotTable article for more information."]
["More information on the web"]
["All about the SUMIF function"]
["All about the SUMIFS function"]
["All about the COUNTIF function"]
["All about the COUNTIFS function"]
["All about the AVERAGEIF function"]
["All about the AVERAGEIFS function",null,"Fruit","Amount",null,"Fruit","Type","Amount"]
["All about the MINIFS function",null,"Apples",50,null,"Apples","Fuji",50]
["All about the MAXIFS function",null,"Oranges",20,null,"Oranges","Florida",20]
["Create a drop-down list",null,"Bananas",60,null,"Bananas","Cavendish",60]
["Free Excel training online",null,"Lemons",40,null,"Lemons","Rough",40]
["Back to top",null,"Apples",50,null,"Apples","Honeycrisp",50]
["Next",null,"Oranges",20,null,"Oranges","Navel",20]
[null,null,"Bananas",60,null,"Bananas","Lady Finger",60]
[null,null,"Lemons",40,null,"Lemons","Eureka",40]
[null,null,"Apples",50,null,"Apples","Honeycrisp",50]
[null,null,"Oranges",20,null,"Oranges","Navel",20]
[null,null,"Bananas",60,null,"Bananas","Cavendish",60]
[null,null,"Lemons",40,null,"Lemons","Eureka",40]
[]
[null,null,"Fruit","COUNTIF",null,"Fruit","Type","COUNTIFS"]
[null,null,"Apples",{"$f":"COUNTIF(C50:C61,C64)","v":3},null,"Oranges","Florida",{"$f":"COUNTIFS(F50:F61,F64,G50:G61,G64)","v":1}]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,null,"Fruit","Type","Amount"]
[null,null,"Apples","Fuji",50]
[null,null,"Oranges","Florida",20]
[null,null,"Bananas","Cavendish",60]
[null,null,"Lemons","Rough",40]
[null,null,"Apples","Honeycrisp",50]
[null,null,"Oranges","Navel",20]
[null,null,"Bananas","Lady Finger",60]
[null,null,"Lemons","Eureka",40]
[null,null,"Apples","Honeycrisp",50]
[null,null,"Oranges","Navel",20]
[null,null,"Bananas","Cavendish",60]
[null,null,"Lemons","Eureka",40]
[]
[null,null,"Fruit","Type","Try it"]
[null,null,"Lemons","Eureka",{"$f":"SUMIFS(E92:E103,C92:C103,C106,D92:D103,D106)","v":80}]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,null,"Item","Amount"]
[null,null,"Bread",50]
[null,null,"Donuts",100]
[null,null,"Cookies",40]
[null,null,"Cakes",50]
[null,null,"Pies",20]
[null,null,null,{"$f":"SUMIF(D118:D122,\">=50\")","v":200}]

{"sheet":"Function Wizard","index":10,"n_rows":14,"n_cols":4,"rows_kept":14,"cut":false,"hidden":false,"merged":[]}
["Let the Function Wizard guide you"]
["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.\n"]
["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, click OK at the bottom. When you select a function in the list, Excel will display its syntax.\n"]
["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. As you enter each section, the criteria for each argument is listed at the bottom of the form.  Press OK when you're done, and Excel will enter the formula for you.\n\n\n",null,"Fruit","Amount"]
["CHECK THIS OUT\nYou should end up with =VLOOKUP(C10,C5:D8,2,FALSE)",null,"Apples",50]
["GOOD TO KNOW\nYou can type cell and range references, or select them with your mouse.",null,"Oranges",20]
["GOOD TO KNOW\nAs you enter each argument's section, the argument's description will be displayed toward the bottom of the form, above the Formula result.\n",null,"Bananas",60]
["Previous",null,"Lemons",40]
["Next"]
["More information on the web",null,"Apples",{"$f":"VLOOKUP(C10,C5:D8,2,FALSE)","v":50}]
["Overview of formulas in Excel"]
["Excel functions (by category)"]
["Excel functions (alphabetical)"]
["Free Excel training online"]

{"sheet":"Formula Errors","index":11,"n_rows":36,"n_cols":4,"rows_kept":36,"cut":false,"hidden":false,"merged":[]}
["Fixing formula errors"]
["At some point in time, you'll run across a formula that has an error, which Excel will display with #ErrorName. Errors can be helpful, because they point out when something's not working right, but they can be challenging to fix. Fortunately, there are several options that can help you track down the source of the error, and fix it.\n"]
["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.\n",null,"Fruit","Amount"]
["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 dialog will load.\n",null,"Apples",50]
["Each time you click Evaluate, Excel will step through the formula one section at a time. It won't necessarily tell you why an error occurs, but it will point out where. From there, look at the help topic to deduce what went wrong with your formula.\n",null,"Oranges",20]
["EXPERIMENT\nWhat's wrong here? Hint: We're trying to SUM up all the items.",null,"Bananas",60]
["GOOD TO KNOW\nClicking Options will let you set the rules for when errors in Excel are displayed or ignored.\n",null,"Lemons",40]
["Previous"]
["Next",null,"Apple",{"$f":"VLOOKUP(C9,C3:D7,2,FALSE)","v":{"$error":"#N/A"}}]
["More information on the web"]
["Detect errors in formulas"]
["How to avoid broken formulas"]
["Evaluate a nested formula one step at a time"]
["Free Excel training online"]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,null,"Fruit","Amount"]
[null,null,"Apples",50]
[null,null,"Oranges",20]
[null,null,"Bananas",60]
[null,null,"Lemons",40]
[]
[null,null,"Total",{"$f":"sume(D31:D34)","v":{"$error":"#NAME?"}}]

{"sheet":"Learn more","index":12,"n_rows":6,"n_cols":1,"rows_kept":6,"cut":false,"hidden":false,"merged":[]}
["More questions about Excel?"]
["Press ALT+Q and type what you want to know."]
["Keep going. There is more to learn with Excel:"]
["LinkedIn Learning: Video courses for all levels—from beginner to advanced. Take at your own pace.\n"]
["Community: Ask questions and connect with other Excel fans.\n"]
["What else is new?\nOffice 365 subscribers get continual updates and new features.\n\n"]