title
Excel Formulas and Functions | Full Course

description
🔥 Go from Excel novice to data analysis ninja in just 2 hours with my Excel for Beginners course: https://kevinstratvert.thinkific.com In this step-by-step Microsoft Excel tutorial, learn how to use Formulas and Functions. We start with the fundamentals of entering formulas into Excel. Then we advance to the most common formulas and functions that you'll encounter in Excel. This video is perfect for beginners who are new to formulas and functions, and even more advanced users who want to pick up a new trick or two. 📚 RESOURCES - Excel Workbook without formulas to follow along: https://1drv.ms/x/s!AmxrofZZlZ-whN9F9EUuTyuaf_kIbQ?e=g2ocvA - Completed Workbook with all formulas and Functions: https://1drv.ms/x/s!AmxrofZZlZ-whN9QhwPmlmuTzwgZBg?e=m0L8am - Learn the fundamentals of Excel in just 2 hours: https://kevinstratvert.thinkific.com ⌚ TIMESTAMPS 00:00 Introduction 00:45 Status bar metrics 02:03 Enter formula to add 02:50 Edit formulas 03:06 Relative references 05:29 Fill handle 06:13 Subtract 06:57 Multiply 07:40 Absolute reference 09:46 Reference cells on other sheets 10:45 Name manager 12:03 Divide 12:44 Order of operations 14:40 Basic functions 15:04 Sum function 17:46 Formula text function 18:13 Discovering functions 18:37 Auto sum 19:37 Max function 20:15 Min function 20:48 Large function 21:54 Small function 22:35 Count function 23:00 Counta function 23:49 Countblank function 24:08 Average function 24:44 Median function 25:00 Mode function 25:23 Sumif function 27:29 Wildcards 28:36 Sumifs function 31:26 Proper function 32:00 Trim function 32:25 Concat function 33:39 & function 34:05 Textjoin function 34:52 Right, left and mid functions 35:27 Len function 35:46 Find and search functions 36:40 Left together with find 38:23 Logical functions 39:21 And function 39:53 Or function 40:14 If function 41:27 If together with and 42:23 If together with or 43:00 Nested if 44:41 Ifs function 45:32 Vlookup function 47:18 Xlookup function 49:24 Today and now functions 49:51 Bonus 52:04 Wrap up 📺 RELATED VIDEOS - Playlist with all my videos on Excel: https://youtube.com/playlist?list=PLlKpQrBME6xLYoubjOqowzcCCd0ivQVLY 🙌 SUPPORT THE CHANNEL - Hit the THANKS button in any video! - Amazon affiliate link: https://amzn.to/3kCP2yz (Purchasing through this link gives me a small commission to support videos on this channel -- the price to you is the same) 🔽 CONNECT WITH ME - Official web site: http://www.kevinstratvert.com - LinkedIn: https://www.linkedin.com/in/kevinstratvert/ - Discord: https://bit.ly/KevinStratvertDiscord - Twitter: https://twitter.com/kevstrat - Facebook: https://www.facebook.com/Kevin-Stratvert-101912218227818 - TikTok: https://www.tiktok.com/@kevinstratvert - Instagram: https://www.instagram.com/kevinstratvert/ 🎒 MY COURSES - Excel for Beginners in under 2 hours: https://kevinstratvert.thinkific.com/ 🙏 REQUEST VIDEOS https://forms.gle/BDrTNUoxheEoMLGt5 🔔 SUBSCRIBE ON YOUTUBE https://www.youtube.com/user/kevlers?sub_confirmation=1 #stratvert #MicrosoftAmbassador

detail
{'title': 'Excel Formulas and Functions | Full Course', 'heatmap': [], 'summary': 'The full course on excel formulas & functions covers fundamental concepts like entering formulas, cell referencing, absolute references, and various functions such as sum, max, min, concat, and logical functions. it demonstrates practical applications achieving specific totals and sums, and explains the advantages of xlookup over vlookup in returning multiple values.', 'chapters': [{'end': 117.497, 'segs': [{'end': 129.628, 'src': 'embed', 'start': 99.467, 'weight': 0, 'content': [{'end': 104.313, 'text': 'Now down below when I have these three values highlighted, I could also go to the status bar and right click.', 'start': 99.467, 'duration': 4.846}, {'end': 112.136, 'text': "And here if I scroll down this menu, you'll see that I also get the average, the count, here I could get the min, the max, and here I have the sum.", 'start': 105.074, 'duration': 7.062}, {'end': 117.497, 'text': 'So here if I check this on, here I could also see the max now down below on the status bar.', 'start': 112.556, 'duration': 4.941}, {'end': 122.879, 'text': 'So this is one way to get some quick answers without even needing to enter in a formula.', 'start': 117.958, 'duration': 4.921}, {'end': 129.628, 'text': 'Now of course this video is all about formulas and functions and we should probably enter in our first formula.', 'start': 123.584, 'duration': 6.044}], 'summary': 'Using status bar to quickly calculate average, count, min, max, and sum without formula input.', 'duration': 30.161, 'max_score': 99.467, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Y8xhrUa3KH4/pics/Y8xhrUa3KH499467.jpg'}], 'start': 0.289, 'title': 'Excel formulas & functions', 'summary': 'Covers the fundamentals of entering formulas in excel, commonly used functions, and demonstrates basic metrics like sum, average, count, min, and max, enabling viewers to efficiently perform calculations and analysis in excel.', 'chapters': [{'end': 117.497, 'start': 0.289, 'title': 'Excel formulas & functions', 'summary': 'Covers the fundamentals of entering formulas in excel, commonly used functions, and demonstrates basic metrics like sum, average, count, min, and max, enabling viewers to efficiently perform calculations and analysis in excel.', 'duration': 117.208, 'highlights': ['The chapter demonstrates how to easily calculate the sum, average, count, min, and max of selected cells using the status bar, providing a quick way to obtain basic metrics for analysis.', 'It emphasizes the efficiency of obtaining basic metrics by using the status bar to perform calculations, showcasing the ease of adding up values and accessing various functions like sum, average, count, min, and max.']}], 'duration': 117.208, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Y8xhrUa3KH4/pics/Y8xhrUa3KH4289.jpg', 'highlights': ['The chapter demonstrates how to easily calculate the sum, average, count, min, and max of selected cells using the status bar, providing a quick way to obtain basic metrics for analysis.', 'It emphasizes the efficiency of obtaining basic metrics by using the status bar to perform calculations, showcasing the ease of adding up values and accessing various functions like sum, average, count, min, and max.']}, {'end': 475.771, 'segs': [{'end': 186.224, 'src': 'embed', 'start': 160.334, 'weight': 5, 'content': [{'end': 164.637, 'text': 'To calculate this formula now, all I need to do is to press the enter key.', 'start': 160.334, 'duration': 4.303}, {'end': 170.308, 'text': 'Congratulations! You just entered your first formula in Excel.', 'start': 165.397, 'duration': 4.911}, {'end': 178.756, 'text': 'To go back and edit this formula, here I can click into this cell and here we see the formula up above, so I can modify the values up here.', 'start': 170.908, 'duration': 7.848}, {'end': 186.224, 'text': 'Or alternatively, I could also double click into this cell and this will once again show me the formula and then I can make modifications here.', 'start': 179.577, 'duration': 6.647}], 'summary': 'Excel formula entered using enter key, edit by clicking or double-clicking cell.', 'duration': 25.89, 'max_score': 160.334, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Y8xhrUa3KH4/pics/Y8xhrUa3KH4160334.jpg'}, {'end': 231.425, 'src': 'embed', 'start': 201.824, 'weight': 1, 'content': [{'end': 206.026, 'text': "so as these values over here change, my formula doesn't change.", 'start': 201.824, 'duration': 4.202}, {'end': 212.729, 'text': 'So instead of hardcoding these numbers or values in, instead I want to reference the different cells.', 'start': 206.566, 'duration': 6.163}, {'end': 218.592, 'text': 'So here for example, instead of typing in 10, I want to use cell B2.', 'start': 213.908, 'duration': 4.684}, {'end': 224.258, 'text': "So here let me delete 10 and I can type in B and then I'll enter 2.", 'start': 219.373, 'duration': 4.885}, {'end': 226.5, 'text': 'And here you see that the cell has now highlighted.', 'start': 224.258, 'duration': 2.242}, {'end': 231.425, 'text': "And here also for the 8, instead of hard coding that, here I'm going to type in C2.", 'start': 227.421, 'duration': 4.004}], 'summary': 'Adapting formula to reference cells instead of hardcoding numbers, like using cell b2 for 10 and c2 for 8.', 'duration': 29.601, 'max_score': 201.824, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Y8xhrUa3KH4/pics/Y8xhrUa3KH4201824.jpg'}, {'end': 298.413, 'src': 'embed', 'start': 269.911, 'weight': 0, 'content': [{'end': 276.558, 'text': 'Now, of course, typing in a cell reference works, but it also requires quite a bit of effort.', 'start': 269.911, 'duration': 6.647}, {'end': 282.263, 'text': 'And instead, we could use our keyboard or our mouse to reference specific cells.', 'start': 277.299, 'duration': 4.964}, {'end': 288.767, 'text': "Here once again, in the total cell I'll enter the equal sign and instead of typing in B2,", 'start': 283.203, 'duration': 5.564}, {'end': 292.389, 'text': 'I can simply use the arrows on my keyboard to select that cell.', 'start': 288.767, 'duration': 3.622}, {'end': 298.413, 'text': "Here I'll type in the plus sign and I can arrow over and once again I can arrow over and then hit enter.", 'start': 293.29, 'duration': 5.123}], 'summary': 'Using keyboard shortcuts to reference specific cells reduces effort and increases efficiency.', 'duration': 28.502, 'max_score': 269.911, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Y8xhrUa3KH4/pics/Y8xhrUa3KH4269911.jpg'}, {'end': 480.492, 'src': 'embed', 'start': 454.127, 'weight': 4, 'content': [{'end': 458.968, 'text': "And here I'll select 10% and then I could press enter and it looks like the tax is 0.6 or 60 cents.", 'start': 454.127, 'duration': 4.841}, {'end': 465.909, 'text': 'Now here once again, I want to calculate the tax for all of my different cookie types.', 'start': 461.528, 'duration': 4.381}, {'end': 469.329, 'text': 'So just like we did before, we could use the fill handle.', 'start': 466.469, 'duration': 2.86}, {'end': 473.15, 'text': "Here once again, I'll select that and let me drag it over one column.", 'start': 469.97, 'duration': 3.18}, {'end': 475.771, 'text': 'But here you see that the tax is zero.', 'start': 473.71, 'duration': 2.061}, {'end': 480.492, 'text': "Now from a business standpoint, that's good news, but I don't think it's calculated correctly.", 'start': 475.791, 'duration': 4.701}], 'summary': 'Calculating tax for different cookie types, 10% tax yields 60 cents, but seems incorrectly calculated.', 'duration': 26.365, 'max_score': 454.127, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Y8xhrUa3KH4/pics/Y8xhrUa3KH4454127.jpg'}], 'start': 117.958, 'title': 'Entering and referencing formulas in excel', 'summary': 'Explains entering formulas in excel with equal sign, cell referencing, and dynamic updates, and demonstrates cell referencing, formula reproduction, and tax calculation with examples of updates and adjustments based on relative cell references.', 'chapters': [{'end': 249.102, 'start': 117.958, 'title': 'Entering formulas in excel', 'summary': 'Explains how to enter formulas in excel, including using the equal sign, referencing cells instead of hardcoding values, and editing formulas, ultimately allowing for dynamic updates based on changing values.', 'duration': 131.144, 'highlights': ['Entering a formula in Excel involves using the equal sign and referencing cells instead of hardcoding values. The chapter explains that to enter a formula in Excel, one needs to use the equal sign and reference cells instead of hardcoding values.', 'Editing formulas in Excel can be done by clicking into the cell or double-clicking to modify the formula. It is mentioned that formulas in Excel can be edited by clicking into the cell or double-clicking to modify the formula.', 'Dynamic updates in Excel are achieved by referencing cells, allowing for automatic adjustments when values change. The chapter demonstrates that by referencing cells instead of hardcoding values, Excel allows for dynamic updates, automatically adjusting when values change.']}, {'end': 475.771, 'start': 249.862, 'title': 'Excel cell referencing and formulas', 'summary': 'Demonstrates how to reference specific cells using keyboard or mouse, reproduce formulas across multiple cells, and use multiplication to calculate tax, with examples showing updates and adjustments in the formula based on relative cell references.', 'duration': 225.909, 'highlights': ['The chapter demonstrates how to reference specific cells using keyboard or mouse The speaker illustrates using keyboard arrows or mouse clicks to reference specific cells in Excel, providing an alternative to typing in cell references.', 'Reproducing formulas across multiple cells is shown with examples of updates and adjustments in the formula based on relative cell references The process of reproducing formulas across multiple cells in Excel is demonstrated, showing how the formula adjusts relatively and updates all references based on the movement of the formula.', 'Using multiplication to calculate tax is illustrated with examples of applying the multiplication sign and updating the tax calculation for different cookie types The use of multiplication to calculate tax in Excel is shown, with examples of applying the multiplication sign, demonstrating how the tax calculation is updated for different cookie types.']}], 'duration': 357.813, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Y8xhrUa3KH4/pics/Y8xhrUa3KH4117958.jpg', 'highlights': ['Entering a formula in Excel involves using the equal sign and referencing cells instead of hardcoding values.', 'Dynamic updates in Excel are achieved by referencing cells, allowing for automatic adjustments when values change.', 'The chapter demonstrates how to reference specific cells using keyboard or mouse.', 'Reproducing formulas across multiple cells is shown with examples of updates and adjustments in the formula based on relative cell references.', 'Using multiplication to calculate tax is illustrated with examples of applying the multiplication sign and updating the tax calculation for different cookie types.', 'Editing formulas in Excel can be done by clicking into the cell or double-clicking to modify the formula.']}, {'end': 1019.587, 'segs': [{'end': 586.42, 'src': 'embed', 'start': 519.755, 'weight': 0, 'content': [{'end': 529.917, 'text': "We can press the F4 key or the function 4 key on our keyboard and you'll notice that it adds a dollar sign before the G and a dollar sign before the 2..", 'start': 519.755, 'duration': 10.162}, {'end': 534.679, 'text': 'The dollar sign tells Excel that we want to lock both the column and the row.', 'start': 529.917, 'duration': 4.762}, {'end': 544.936, 'text': "Here I'll press enter and now, if I pull this over, you'll notice the formula works as we expect it to, and when I click in here,", 'start': 535.95, 'duration': 8.986}, {'end': 551.7, 'text': 'you can see that it adjusts this first cell relatively and then it locks in the tax rate.', 'start': 544.936, 'duration': 6.764}, {'end': 554.462, 'text': 'and here it did that for all the different tax calculations.', 'start': 551.7, 'duration': 2.762}, {'end': 558.265, 'text': "This is what's referred to as an absolute reference.", 'start': 555.323, 'duration': 2.942}, {'end': 567.069, 'text': "As a quick note here, when I'm in this cell and we see the formula up above with the dollar signs, if I press F4 again now,", 'start': 559.205, 'duration': 7.864}, {'end': 568.83, 'text': 'you just see a dollar sign on the row.', 'start': 567.069, 'duration': 1.761}, {'end': 573.073, 'text': 'If I press F4 again, you just see the dollar sign on the column.', 'start': 569.151, 'duration': 3.922}, {'end': 574.974, 'text': 'so you could just lock the column.', 'start': 573.073, 'duration': 1.901}, {'end': 576.975, 'text': 'or you could just lock the row.', 'start': 574.974, 'duration': 2.001}, {'end': 583.439, 'text': "and here, if I press F4 again, it removes it from everything, and once again it's purely just making a relative reference.", 'start': 576.975, 'duration': 6.464}, {'end': 586.42, 'text': "but once again I want to lock it, so I'll press the F4 key.", 'start': 583.439, 'duration': 2.981}], 'summary': 'Pressing f4 key in excel adds dollar signs for locking cell references, ensuring absolute reference in formulas.', 'duration': 66.665, 'max_score': 519.755, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Y8xhrUa3KH4/pics/Y8xhrUa3KH4519755.jpg'}, {'end': 671.509, 'src': 'embed', 'start': 645.97, 'weight': 3, 'content': [{'end': 652.856, 'text': "Now this seems to work well where I'm referencing a different sheet, but when you look at the formula itself, it's a little bit messy.", 'start': 645.97, 'duration': 6.886}, {'end': 655.458, 'text': 'You have the sheet name and then you have the cell reference.', 'start': 652.896, 'duration': 2.562}, {'end': 665.186, 'text': "I'll go back to the tax rate sheet and I can give this cell a name so I don't have to refer to it as A2, but instead I could use the name reference.", 'start': 656.639, 'duration': 8.547}, {'end': 671.509, 'text': 'Right up here where you see A2, here I can simply type in tax rate and then press enter.', 'start': 666.167, 'duration': 5.342}], 'summary': 'Referring to cells by name improves formula readability and efficiency.', 'duration': 25.539, 'max_score': 645.97, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Y8xhrUa3KH4/pics/Y8xhrUa3KH4645970.jpg'}, {'end': 881.031, 'src': 'embed', 'start': 853.705, 'weight': 4, 'content': [{'end': 860.088, 'text': 'To do that, I can use parentheses and that way I can define what I want the order to be.', 'start': 853.705, 'duration': 6.383}, {'end': 863.649, 'text': "Here I'll put a parentheses around b2 minus b3.", 'start': 860.628, 'duration': 3.021}, {'end': 870.612, 'text': "That way Excel will evaluate this, it'll take the result, and then it'll multiply it by 10%.", 'start': 864.229, 'duration': 6.383}, {'end': 872.115, 'text': 'Now, when I press enter, I get .', 'start': 870.612, 'duration': 1.503}, {'end': 876.202, 'text': "6 and that's exactly the same as what we got on the previous sheet.", 'start': 872.115, 'duration': 4.087}, {'end': 881.031, 'text': 'So, one thing to be aware of when you enter formulas, there is an order of operations.', 'start': 876.623, 'duration': 4.408}], 'summary': 'Using parentheses in excel formulas can define order of operations, illustrated with an example.', 'duration': 27.326, 'max_score': 853.705, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Y8xhrUa3KH4/pics/Y8xhrUa3KH4853705.jpg'}, {'end': 989.191, 'src': 'embed', 'start': 961.078, 'weight': 5, 'content': [{'end': 966.703, 'text': 'Now once again, I want to add up all these values or another way of saying that I want to sum up all these values.', 'start': 961.078, 'duration': 5.625}, {'end': 968.664, 'text': "So let's type in sum.", 'start': 967.223, 'duration': 1.441}, {'end': 973.846, 'text': "Then I'll click on go and here it looks like there is a function called sum.", 'start': 969.565, 'duration': 4.281}, {'end': 978.248, 'text': "There are also some other options and we'll get into these later, but for now we just want to sum these up.", 'start': 974.026, 'duration': 4.222}, {'end': 980.108, 'text': "Let's click on OK.", 'start': 979.068, 'duration': 1.04}, {'end': 984.01, 'text': 'Here now it asks me what numbers I want to sum up.', 'start': 980.949, 'duration': 3.061}, {'end': 989.191, 'text': "I'll move this over just a little bit so we can see the numbers we want to add up and here it says number one and number two.", 'start': 984.35, 'duration': 4.841}], 'summary': 'Demonstrating the process of summing up values using a function called sum.', 'duration': 28.113, 'max_score': 961.078, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Y8xhrUa3KH4/pics/Y8xhrUa3KH4961078.jpg'}], 'start': 475.791, 'title': 'Excel formulas and functions', 'summary': 'Discusses the importance of absolute references in excel formulas and covers referencing cells on different sheets, assigning names to cells, the order of operations, and using functions to simplify calculations.', 'chapters': [{'end': 586.42, 'start': 475.791, 'title': 'Excel absolute references', 'summary': 'Discusses the importance of absolute references in excel formulas, highlighting the use of the dollar sign to lock both the column and the row for specific cell references, ensuring formula accuracy and consistent calculations.', 'duration': 110.629, 'highlights': ['By using the F4 key or the function 4 key on the keyboard, a dollar sign is added before the column and the row in Excel, allowing the user to lock both the column and the row for specific cell references.', 'The importance of absolute references is demonstrated through the example of tax rate calculations, where locking the reference using the dollar sign ensures consistent and accurate formula adjustments.', 'The chapter also explains the ability to selectively lock either the column or the row by pressing the F4 key, providing flexibility in reference locking within Excel formulas.']}, {'end': 1019.587, 'start': 587.941, 'title': 'Excel formulas and functions', 'summary': 'Covers referencing cells on different sheets, assigning names to cells, the order of operations, and using functions to simplify calculations, with examples including division and summing up values.', 'duration': 431.646, 'highlights': ['The chapter covers referencing cells on different sheets and assigning names to cells, making it easier to understand and access specific data. The speaker demonstrates referencing a tax rate on a different sheet and assigning a name to a cell, making it easier to access specific data in formulas.', 'The chapter explains the order of operations in Excel formulas, addressing the issue of incorrect calculation due to the order of operations and demonstrating the use of parentheses to control the order of evaluation. The speaker addresses the issue of incorrect calculation due to the order of operations and demonstrates the use of parentheses to control the order of evaluation in Excel formulas.', "The chapter discusses the use of functions to simplify calculations, with an example of using the 'sum' function to add up values, illustrating the efficiency of using functions over manual addition. The speaker demonstrates the use of the 'sum' function to add up values, highlighting the efficiency of using functions over manual addition in Excel."]}], 'duration': 543.796, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Y8xhrUa3KH4/pics/Y8xhrUa3KH4475791.jpg', 'highlights': ['By using the F4 key or the function 4 key on the keyboard, a dollar sign is added before the column and the row in Excel, allowing the user to lock both the column and the row for specific cell references.', 'The importance of absolute references is demonstrated through the example of tax rate calculations, where locking the reference using the dollar sign ensures consistent and accurate formula adjustments.', 'The chapter also explains the ability to selectively lock either the column or the row by pressing the F4 key, providing flexibility in reference locking within Excel formulas.', 'The chapter covers referencing cells on different sheets and assigning names to cells, making it easier to understand and access specific data.', 'The chapter explains the order of operations in Excel formulas, addressing the issue of incorrect calculation due to the order of operations and demonstrating the use of parentheses to control the order of evaluation.', "The chapter discusses the use of functions to simplify calculations, with an example of using the 'sum' function to add up values, illustrating the efficiency of using functions over manual addition."]}, {'end': 1982.238, 'segs': [{'end': 1047.162, 'src': 'embed', 'start': 1020.107, 'weight': 0, 'content': [{'end': 1027.491, 'text': "These are referred to as the arguments or the parameters that I'm passing into the function, and right here at the bottom,", 'start': 1020.107, 'duration': 7.384}, {'end': 1028.972, 'text': 'you can see what they add up to.', 'start': 1027.491, 'duration': 1.481}, {'end': 1035.356, 'text': 'So when I sum all of these different numbers, that gives me 421 and this is a basic function.', 'start': 1029.092, 'duration': 6.264}, {'end': 1041.04, 'text': 'Next, I can click on OK and here you see that I just summed up all of these different cells.', 'start': 1035.915, 'duration': 5.125}, {'end': 1047.162, 'text': 'One thing to call out, when I double click into the cell, you can see the notation of a function.', 'start': 1042.06, 'duration': 5.102}], 'summary': 'Summed up 421 from various parameters in a basic function.', 'duration': 27.055, 'max_score': 1020.107, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Y8xhrUa3KH4/pics/Y8xhrUa3KH41020107.jpg'}, {'end': 1232.475, 'src': 'embed', 'start': 1201.983, 'weight': 2, 'content': [{'end': 1207.105, 'text': "So this is the argument or the parameter that I'm feeding in, and I could press enter.", 'start': 1201.983, 'duration': 5.122}, {'end': 1214.648, 'text': 'and here I could see that chocolate chip had the most number of cookies sold at 97, and here we could validate that by looking at this list.', 'start': 1207.105, 'duration': 7.543}, {'end': 1217.449, 'text': "I'll delete the 421.", 'start': 1215.308, 'duration': 2.141}, {'end': 1224.292, 'text': "Right over here, I also want to know the least number of cookies sold, and if we just look at this list, we see that it's fortune with 36.", 'start': 1217.449, 'duration': 6.843}, {'end': 1232.475, 'text': "I'll click into this cell, and once again I could go to formulas, auto sum, and here I could select min, but instead let's just type in the function.", 'start': 1224.292, 'duration': 8.183}], 'summary': 'Chocolate chip had 97 cookies sold, while fortune had 36, showcasing the highest and lowest sales.', 'duration': 30.492, 'max_score': 1201.983, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Y8xhrUa3KH4/pics/Y8xhrUa3KH41201983.jpg'}, {'end': 1502.276, 'src': 'embed', 'start': 1476.939, 'weight': 5, 'content': [{'end': 1483.647, 'text': 'I can just press enter and that too will calculate, so that saves you a little bit of time and here I can see that on average we sold 60.14 cookies.', 'start': 1476.939, 'duration': 6.708}, {'end': 1492.431, 'text': 'Along with your average, you can also calculate the median or basically which number is in the middle of this list.', 'start': 1486.388, 'duration': 6.043}, {'end': 1495.012, 'text': "If we just look at it, we see that it's 52.", 'start': 1492.491, 'duration': 2.521}, {'end': 1502.276, 'text': "Here I could type in median, open the parentheses, let me highlight this list and press enter, and there we see that it's 52.", 'start': 1495.012, 'duration': 7.264}], 'summary': 'The average cookies sold was 60.14, with a median of 52.', 'duration': 25.337, 'max_score': 1476.939, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Y8xhrUa3KH4/pics/Y8xhrUa3KH41476939.jpg'}, {'end': 1547.011, 'src': 'embed', 'start': 1518.387, 'weight': 1, 'content': [{'end': 1524.579, 'text': "Once again, I'll select this list, hit enter, and here we see that 52 shows up the most often in this list.", 'start': 1518.387, 'duration': 6.192}, {'end': 1532.604, 'text': "Moving on now to the conditional functions worksheet, we're now getting a little bit more advanced.", 'start': 1525.6, 'duration': 7.004}, {'end': 1536.125, 'text': 'Here we have a function called SUMIF.', 'start': 1532.764, 'duration': 3.361}, {'end': 1541.368, 'text': 'Here we can sum up values if it meets a certain criteria.', 'start': 1536.306, 'duration': 5.062}, {'end': 1547.011, 'text': "Let's say for instance that I want to know how many cookies we sold in the United States.", 'start': 1541.468, 'duration': 5.543}], 'summary': '52 is the most frequent number. introducing sumif function for advanced calculations.', 'duration': 28.624, 'max_score': 1518.387, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Y8xhrUa3KH4/pics/Y8xhrUa3KH41518387.jpg'}, {'end': 1678.594, 'src': 'embed', 'start': 1653.393, 'weight': 3, 'content': [{'end': 1662.099, 'text': "Here within the function for SUMIF, let me open up the function helper again and for the criteria here I'm going to type in United States.", 'start': 1653.393, 'duration': 8.706}, {'end': 1667.944, 'text': 'And, once again, I will click on OK, and we see that the function works the exact same way.', 'start': 1663.179, 'duration': 4.765}, {'end': 1673.91, 'text': "But let's say that I want to sum up all the values for countries that end in an S.", 'start': 1668.585, 'duration': 5.325}, {'end': 1678.594, 'text': "Now, it's an arbitrary situation, but I want to show you how you can search based on a wildcard.", 'start': 1673.91, 'duration': 4.684}], 'summary': 'Demonstrating the use of the sumif function with specific criteria and the use of wildcards for searching.', 'duration': 25.201, 'max_score': 1653.393, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Y8xhrUa3KH4/pics/Y8xhrUa3KH41653393.jpg'}], 'start': 1020.107, 'title': 'Using excel functions and conditional functions in excel', 'summary': 'Demonstrates using excel functions like sum, max, and min to calculate totals and find the highest and lowest values, achieving a sum of 421 with 97 as the highest and 36 as the lowest. it also explains the usage of sumif function to sum specific values based on criteria, achieving a sum of 802 based on the mentioned criteria.', 'chapters': [{'end': 1266.45, 'start': 1020.107, 'title': 'Using excel functions', 'summary': 'Demonstrates using excel functions like sum, max, and min to calculate totals and find the highest and lowest values in a dataset, with the sum of different numbers being 421, the highest number of cookies sold being 97, and the lowest being 36.', 'duration': 246.343, 'highlights': ["The sum of different numbers results in 421. The example demonstrates using the 'sum' function in Excel to calculate the total of different numbers, resulting in the sum of 421.", "The highest number of cookies sold is 97. The 'max' function in Excel is used to determine the highest number of cookies sold, which is found to be 97.", "The lowest number of cookies sold is 36. By using the 'min' function in Excel, it is revealed that the lowest number of cookies sold is 36."]}, {'end': 1517.566, 'start': 1267.23, 'title': 'Using functions in excel', 'summary': 'Explains how to use various functions in excel, such as finding the second largest and smallest values, counting the number of cells with values, handling text-based values in count function, and calculating average, median, and mode.', 'duration': 250.336, 'highlights': ['The chapter demonstrates using functions in Excel to find the second largest and smallest values, with an example of finding the second largest cookie sold, which is oatmeal raisin with 77 sales.', 'It explains using the count function to determine the number of cells with values, identifying that seven different cookies had some sales, and handling text-based values in the count function.', 'The chapter illustrates calculating the average, median, and mode in Excel, showing the average sales of 60.14 cookies, the median as 52, and the mode as 52, which occurs most frequently in the list.']}, {'end': 1696.73, 'start': 1518.387, 'title': 'Using conditional functions in excel', 'summary': 'Explains how to use the sumif function in excel to sum up specific values based on criteria, and also demonstrates the use of wildcards for more flexible conditions, achieving a sum of 802 based on the criteria mentioned.', 'duration': 178.343, 'highlights': ['The SUMIF function in Excel is used to sum up values based on a specific criteria, such as summing up the number of cookies sold in the United States, resulting in a sum of 362. The chapter explains the use of the SUMIF function in Excel to sum up values based on specific criteria, demonstrating the example of summing up the number of cookies sold in the United States, resulting in a sum of 362.', 'Other conditional functions like AVERAGEIF and COUNTIF work similarly to SUMIF, allowing for averaging or counting values based on specific criteria. The chapter also covers other conditional functions like AVERAGEIF and COUNTIF, which work similarly to SUMIF, allowing for averaging or counting values based on specific criteria.', "The demonstration of using a wildcard in the criteria for SUMIF to sum up values for countries ending with 'S', achieving a sum of 802. The chapter demonstrates the use of a wildcard in the criteria for the SUMIF function to sum up values for countries ending with 'S', achieving a sum of 802."]}, {'end': 1982.238, 'start': 1696.73, 'title': 'Summing and manipulating data with excel functions', 'summary': "Covers the usage of sumifs function to sum values based on multiple criteria, demonstrating the sum of chocolate chip cookies sold in the united states as 28, the combined sum of chocolate chip and oatmeal raisin cookies in the united states as 60, and introduces text manipulation functions like 'proper' and 'trim'.", 'duration': 285.508, 'highlights': ['The sum of chocolate chip cookies sold in the United States is 28. Demonstrates the specific sum of chocolate chip cookies sold in the United States, providing quantifiable data.', 'The combined sum of chocolate chip and oatmeal raisin cookies in the United States is 60. Shows the combined sum of chocolate chip and oatmeal raisin cookies in the United States, providing quantifiable data.', "Introduces text manipulation functions like 'proper' and 'trim'. Highlights the introduction of text manipulation functions, providing an overview of new concepts covered in the chapter."]}], 'duration': 962.131, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Y8xhrUa3KH4/pics/Y8xhrUa3KH41020107.jpg', 'highlights': ["The sum of different numbers results in 421. The example demonstrates using the 'sum' function in Excel to calculate the total of different numbers, resulting in the sum of 421.", "The highest number of cookies sold is 97. The 'max' function in Excel is used to determine the highest number of cookies sold, which is found to be 97.", "The lowest number of cookies sold is 36. By using the 'min' function in Excel, it is revealed that the lowest number of cookies sold is 36.", "The demonstration of using a wildcard in the criteria for SUMIF to sum up values for countries ending with 'S', achieving a sum of 802. The chapter demonstrates the use of a wildcard in the criteria for the SUMIF function to sum up values for countries ending with 'S', achieving a sum of 802.", 'The SUMIF function in Excel is used to sum up values based on a specific criteria, such as summing up the number of cookies sold in the United States, resulting in a sum of 362. The chapter explains the use of the SUMIF function in Excel to sum up values based on specific criteria, demonstrating the example of summing up the number of cookies sold in the United States, resulting in a sum of 362.', 'The chapter illustrates calculating the average, median, and mode in Excel, showing the average sales of 60.14 cookies, the median as 52, and the mode as 52, which occurs most frequently in the list.']}, {'end': 2729.925, 'segs': [{'end': 2170.13, 'src': 'embed', 'start': 2080.237, 'weight': 0, 'content': [{'end': 2086.819, 'text': "I'll highlight these three and then hit enter and here I get Kevin Cookie Company back with a space in between each word.", 'start': 2080.237, 'duration': 6.582}, {'end': 2093.502, 'text': 'So, out of these three options of concatenating or bringing together text, text join is by far the most powerful one.', 'start': 2087.36, 'duration': 6.142}, {'end': 2095.291, 'text': 'Moving on.', 'start': 2094.73, 'duration': 0.561}, {'end': 2102.58, 'text': 'we have some additional functions here right, left and mid and these allow us to take out text from the right-hand side,', 'start': 2095.291, 'duration': 7.289}, {'end': 2104.663, 'text': 'the left-hand side or the middle.', 'start': 2102.58, 'duration': 2.083}, {'end': 2110.37, 'text': "So, what do I mean by that? Well, let's say that I want to pick out the zip code from this address.", 'start': 2105.123, 'duration': 5.247}, {'end': 2112.252, 'text': 'I can use the write function.', 'start': 2110.891, 'duration': 1.361}, {'end': 2117.915, 'text': "I'll enter in write and then I'll select this address as my text, a comma,", 'start': 2112.953, 'duration': 4.962}, {'end': 2122.378, 'text': 'and then I need to define how many characters do I want to take out from the write?', 'start': 2117.915, 'duration': 4.463}, {'end': 2125.239, 'text': 'and here we can see the zip code is five characters.', 'start': 2122.378, 'duration': 2.861}, {'end': 2129.401, 'text': "I'll type in five and then hit enter and this pulls out the zip code.", 'start': 2125.499, 'duration': 3.902}, {'end': 2135.041, 'text': 'Next, we have another function called len and this is an abbreviation for length.', 'start': 2130.276, 'duration': 4.765}, {'end': 2139.866, 'text': 'So I could use len to figure out how many characters make up the Kevin Cookie Company.', 'start': 2135.621, 'duration': 4.245}, {'end': 2147.874, 'text': "I could type in equals len, open parentheses, and then I could put this text in, close parentheses, and there we see that it's 20 characters long.", 'start': 2140.386, 'duration': 7.488}, {'end': 2157.3, 'text': "Next, there's another function called find and then there's one called search, and they essentially do the same thing, but there's one key difference.", 'start': 2149.013, 'duration': 8.287}, {'end': 2161.763, 'text': 'Find is case sensitive and then search is not case sensitive.', 'start': 2157.98, 'duration': 3.783}, {'end': 2170.13, 'text': "Here. in this example, let's say I want to find out what is the position of the at character within this email address,", 'start': 2162.664, 'duration': 7.466}], 'summary': 'Text join is the most powerful, len function finds 20 characters in kevin cookie company, find & search locate characters in the email address.', 'duration': 89.893, 'max_score': 2080.237, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Y8xhrUa3KH4/pics/Y8xhrUa3KH42080237.jpg'}, {'end': 2327.354, 'src': 'embed', 'start': 2300.22, 'weight': 4, 'content': [{'end': 2304.903, 'text': 'So this shows you the power of when you start combining these different text functions together.', 'start': 2300.22, 'duration': 4.683}, {'end': 2311.967, 'text': "Moving on to the logical function sheet, here we're going to see how you can add some logic to your spreadsheet.", 'start': 2306.004, 'duration': 5.963}, {'end': 2315.83, 'text': 'And first off, we can simply check is something true or false.', 'start': 2312.528, 'duration': 3.302}, {'end': 2318.981, 'text': "So here I'll enter in the equal sign again.", 'start': 2316.618, 'duration': 2.363}, {'end': 2323.448, 'text': 'and is the Kevin Cookie Company equal to Miss Fields??', 'start': 2318.981, 'duration': 4.467}, {'end': 2327.354, 'text': 'Now, I hope all of you know the answer to this, but of course not.', 'start': 2324.249, 'duration': 3.105}], 'summary': 'Combining text functions and logical functions in spreadsheets for data manipulation and analysis.', 'duration': 27.134, 'max_score': 2300.22, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Y8xhrUa3KH4/pics/Y8xhrUa3KH42300220.jpg'}], 'start': 1983.218, 'title': 'Text and logical functions in spreadsheets', 'summary': 'Covers how to concatenate text using functions like concat, ampersand, and textjoin, with textjoin being the most powerful, and also demonstrates the use of logical functions such as comparing text and numbers, using and and or functions, and nesting if functions to make decisions in spreadsheets.', 'chapters': [{'end': 2147.874, 'start': 1983.218, 'title': 'Text functions in excel', 'summary': 'Covers how to concatenate text using functions like concat, ampersand, and textjoin, with textjoin being the most powerful. it also explains how to extract text using functions like right and len in excel.', 'duration': 164.656, 'highlights': ['The TEXTJOIN function is the most powerful way to join together text in Excel. Explains how to use the TEXTJOIN function to concatenate text, providing a more efficient and effective method than CONCAT and AMPERSAND.', 'The RIGHT function can be used to extract specific text, such as a zip code, from a larger text string. Demonstrates the usage of the RIGHT function to extract a zip code from an address, showcasing its practical application in text extraction.', 'The LEN function accurately calculates the character length of a given text in Excel. Illustrates the usage of the LEN function to determine the character length of a specific text, showcasing its utility in quantifying text length.']}, {'end': 2729.925, 'start': 2149.013, 'title': 'Using text functions and logical functions in spreadsheets', 'summary': 'Explains how to use text functions like find and search to manipulate text in spreadsheets, and also demonstrates the use of logical functions such as comparing text and numbers, using and and or functions, and nesting if functions to make decisions in spreadsheets.', 'duration': 580.912, 'highlights': ['The chapter explains how to use text functions like find and search to manipulate text in spreadsheets. It details the differences between the find and search functions, demonstrating their usage to find the position of a character within a text, and how combining text functions can provide dynamic results.', 'The use of logical functions such as comparing text and numbers, using AND and OR functions, and nesting IF functions to make decisions in spreadsheets is demonstrated. It covers comparisons of text and numbers, showing examples of equal, greater than, greater than or equal to, and less than functions, as well as using AND and OR functions to check multiple criteria, and nesting IF functions to make decisions based on different conditions.']}], 'duration': 746.707, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Y8xhrUa3KH4/pics/Y8xhrUa3KH41983218.jpg', 'highlights': ['The TEXTJOIN function is the most powerful way to join together text in Excel. Explains how to use the TEXTJOIN function to concatenate text, providing a more efficient and effective method than CONCAT and AMPERSAND.', 'The chapter explains how to use text functions like find and search to manipulate text in spreadsheets. It details the differences between the find and search functions, demonstrating their usage to find the position of a character within a text, and how combining text functions can provide dynamic results.', 'The RIGHT function can be used to extract specific text, such as a zip code, from a larger text string. Demonstrates the usage of the RIGHT function to extract a zip code from an address, showcasing its practical application in text extraction.', 'The LEN function accurately calculates the character length of a given text in Excel. Illustrates the usage of the LEN function to determine the character length of a specific text, showcasing its utility in quantifying text length.', 'The use of logical functions such as comparing text and numbers, using AND and OR functions, and nesting IF functions to make decisions in spreadsheets is demonstrated. It covers comparisons of text and numbers, showing examples of equal, greater than, greater than or equal to, and less than functions, as well as using AND and OR functions to check multiple criteria, and nesting IF functions to make decisions based on different conditions.']}, {'end': 3140.647, 'segs': [{'end': 2988.575, 'src': 'embed', 'start': 2951.596, 'weight': 0, 'content': [{'end': 2953.176, 'text': "VLOOKUP wouldn't work anymore.", 'start': 2951.596, 'duration': 1.58}, {'end': 2959.12, 'text': "When you use VLOOKUP, the column or the value that you're looking for has to be the leftmost column,", 'start': 2953.297, 'duration': 5.823}, {'end': 2961.561, 'text': 'and then the values that you return have to be to the right of it.', 'start': 2959.12, 'duration': 2.441}, {'end': 2966.344, 'text': "But with XLOOKUP, you can return any column even if it's over to the left.", 'start': 2962.081, 'duration': 4.263}, {'end': 2971.588, 'text': 'Next, I want to show you some date and time functions, and these are really simple.', 'start': 2967.426, 'duration': 4.162}, {'end': 2975.269, 'text': "They're actually functions that you don't even pass any arguments or parameters into.", 'start': 2971.608, 'duration': 3.661}, {'end': 2977.63, 'text': "So, let's say you want to get today's date back.", 'start': 2975.749, 'duration': 1.881}, {'end': 2983.953, 'text': "You simply type in equals today, open and close parentheses, and the reason why is you're not passing any values in.", 'start': 2977.67, 'duration': 6.283}, {'end': 2986.274, 'text': "When you hit enter, you get today's date back.", 'start': 2984.453, 'duration': 1.821}, {'end': 2988.575, 'text': 'And down below, you could also type in now.', 'start': 2986.794, 'duration': 1.781}], 'summary': "Xlookup allows returning any column, while date and time functions like today and now don't require parameters.", 'duration': 36.979, 'max_score': 2951.596, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Y8xhrUa3KH4/pics/Y8xhrUa3KH42951596.jpg'}, {'end': 3046.658, 'src': 'embed', 'start': 3020.95, 'weight': 4, 'content': [{'end': 3029.914, 'text': "Well, you can press the shortcut key control together with the tilde character and that'll automatically show all of the different formulas and functions on your sheet.", 'start': 3020.95, 'duration': 8.964}, {'end': 3038.958, 'text': "Here you'll see that I have a few different formulas and here I reference a few different cells and I can press control tilde again and I can toggle back and forth between those views.", 'start': 3030.594, 'duration': 8.364}, {'end': 3041.879, 'text': "So, that's a nice way to just view all of your formulas.", 'start': 3039.018, 'duration': 2.861}, {'end': 3046.658, 'text': "Now let's say you just want to see all of your formulas and maybe you want to highlight them.", 'start': 3042.796, 'duration': 3.862}], 'summary': 'Press control + tilde to view all formulas and functions on a sheet.', 'duration': 25.708, 'max_score': 3020.95, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Y8xhrUa3KH4/pics/Y8xhrUa3KH43020950.jpg'}, {'end': 3107.949, 'src': 'embed', 'start': 3082.592, 'weight': 2, 'content': [{'end': 3088.696, 'text': "So let's say that you're troubleshooting a function and you want to find all the precedents or you want to find all of the dependents.", 'start': 3082.592, 'duration': 6.104}, {'end': 3090.157, 'text': 'You could very easily do that.', 'start': 3089.056, 'duration': 1.101}, {'end': 3097.022, 'text': 'Here with this cell selected, I could click on formulas up above and here I could trace all of the precedents.', 'start': 3090.918, 'duration': 6.104}, {'end': 3100.704, 'text': 'So this cell relies on these two other cells.', 'start': 3097.522, 'duration': 3.182}, {'end': 3103.726, 'text': 'but here, if I go back to formula here, I could trace the precedents again,', 'start': 3100.704, 'duration': 3.022}, {'end': 3107.949, 'text': 'and it looks like this cell relies on these two other cells to get this value.', 'start': 3103.726, 'duration': 4.223}], 'summary': 'Easily trace precedents and dependents in a function for troubleshooting.', 'duration': 25.357, 'max_score': 3082.592, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Y8xhrUa3KH4/pics/Y8xhrUa3KH43082592.jpg'}], 'start': 2730.065, 'title': 'Excel functions and formulas', 'summary': 'Covers the usage of vlookup and xlookup functions for looking up values in a table, demonstrating the advantages of xlookup over vlookup in returning multiple values. it also provides a comprehensive understanding of formulas and functions in excel, including date and time functions, and tracing of precedents and dependents.', 'chapters': [{'end': 2950.795, 'start': 2730.065, 'title': 'Excel lookup functions tutorial', 'summary': 'Discusses the usage of vlookup and xlookup functions in excel for looking up values in a table, with the example demonstrating the lookup of cookie prices, showcasing the advantages of xlookup over vlookup in returning multiple values.', 'duration': 220.73, 'highlights': ['The function VLOOKUP is demonstrated for looking up values in a table, showcasing the example of searching for the price of an oatmeal raisin cookie, which returns a value of 8, and sugar cookie, which returns a value of 5.', 'The XLOOKUP function is introduced, showcasing its advantages over VLOOKUP, with the example demonstrating the lookup of sugar cookie and chocolate chip prices, which returns values of 5 and 10 respectively, and highlighting its capability to return multiple values like price and cost for a given lookup.', 'The function XLOOKUP is shown to have key advantages over VLOOKUP, as it is capable of returning multiple values, such as price and cost, compared to VLOOKUP, which only returns a single value for a given lookup.']}, {'end': 3140.647, 'start': 2951.596, 'title': 'Excel formulas & functions mastery', 'summary': 'Covers the transition from vlookup to xlookup, simple usage of date and time functions, identification and highlighting of formulas, and tracing of precedents and dependents in excel, providing a comprehensive understanding of formulas and functions in excel.', 'duration': 189.051, 'highlights': ['You can return any column with XLOOKUP, allowing for more flexibility compared to VLOOKUP.', "The 'today' function and the 'now' function provide easy ways to retrieve the current date and time without any arguments or parameters.", 'Using the shortcut key control + tilde can reveal all formulas and functions in the sheet, facilitating quick identification and troubleshooting.', "The 'find formulas' feature in the 'editing' section within the 'home' tab allows for easy highlighting of all formulas in the sheet, aiding in visual identification and organization.", 'Tracing precedents and dependents provides a valuable tool for auditing and understanding the relationships between different cells and formulas in Excel.']}], 'duration': 410.582, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Y8xhrUa3KH4/pics/Y8xhrUa3KH42730065.jpg', 'highlights': ['XLOOKUP function returns multiple values, e.g., price and cost, compared to VLOOKUP.', 'XLOOKUP allows returning any column, providing more flexibility than VLOOKUP.', 'Tracing precedents and dependents aids in auditing and understanding cell relationships in Excel.', "Using 'today' and 'now' functions for retrieving current date and time without parameters.", 'Shortcut key control + tilde reveals all formulas and functions for quick identification.']}], 'highlights': ['The TEXTJOIN function is the most powerful way to join together text in Excel. Explains how to use the TEXTJOIN function to concatenate text, providing a more efficient and effective method than CONCAT and AMPERSAND.', 'The chapter illustrates calculating the average, median, and mode in Excel, showing the average sales of 60.14 cookies, the median as 52, and the mode as 52, which occurs most frequently in the list.', 'The chapter demonstrates how to easily calculate the sum, average, count, min, and max of selected cells using the status bar, providing a quick way to obtain basic metrics for analysis.', 'XLOOKUP function returns multiple values, e.g., price and cost, compared to VLOOKUP.', 'The chapter explains how to use text functions like find and search to manipulate text in spreadsheets. It details the differences between the find and search functions, demonstrating their usage to find the position of a character within a text, and how combining text functions can provide dynamic results.']}