title

Google Sheets Formulas Tutorial

description

Learn how to use formulas and functions in Google Sheets.
👋 Additional resources:
- Access the spreadsheet I used if you'd like to follow along: https://docs.google.com/spreadsheets/d/1lRrxezy1dKcJLrZot5BxBBKffEP8Ys-c6qUMirM9OTA/edit?usp=sharing
- Read about all functions available in Google Sheets: https://support.google.com/docs/table/25273
⌚ Timestamps
0:00 Introduction
0:33 Add & formula basics
2:08 Subtract
2:48 Multiply
3:50 Divide
4:37 Bring it all together
5:44 Min
6:16 Max
6:38 Mean
7:23 Median
7:52 Mode
8:24 Sum
9:37 Sumif
11:09 Count
12:01 Counta
12:33 Countif
13:26 Concatenate
14:41 If
16:41 Vlookup
19:29 Convert
20:50 Today & Now
21:30 Len / character length
22:25 Wrap up
📃 Watch related playlists and videos
- Google: https://www.youtube.com/playlist?list=PLlKpQrBME6xLGL3Ty_1Wbd3nTZ_q_OKFQ
🚩 Connect with me on social:
- LinkedIn: https://www.linkedin.com/in/kevinstratvert/
- Twitter: https://twitter.com/kevstrat
- Facebook: https://www.facebook.com/Kevin-Stratvert-101912218227818
🔔 Subscribe to my YouTube channel
https://www.youtube.com/user/kevlers?sub_confirmation=1
🎬 Want to watch again? Navigate back to my YouTube channel quickly
http://www.kevinstratvert.com
🛍 Support me with your Amazon purchases: https://amzn.to/3kCP2yz
⚖ As full disclosure, I use affiliate links above. Purchasing through these links gives me a small commission to support videos on this channel -- the price to you is the same.

detail

{'title': 'Google Sheets Formulas Tutorial', 'heatmap': [{'end': 379.693, 'start': 361.523, 'weight': 0.713}, {'end': 674.541, 'start': 646.383, 'weight': 0.989}, {'end': 735.196, 'start': 714.489, 'weight': 0.757}, {'end': 818.26, 'start': 787.159, 'weight': 0.846}, {'end': 887.417, 'start': 868.67, 'weight': 0.745}, {'end': 1012.473, 'start': 945.52, 'weight': 0.778}, {'end': 1169.092, 'start': 1079.774, 'weight': 0.727}, {'end': 1254.395, 'start': 1236.488, 'weight': 0.707}, {'end': 1293.559, 'start': 1264.48, 'weight': 0.719}], 'summary': 'This tutorial covers the top 20 formulas and functions in google sheets, practical examples of using formulas for calculations, tracking cookie sales, using count, counta, and countif formulas, concatenating cells, if function, vlookup, and utilizing excel and google sheets functions for various tasks.', 'chapters': [{'end': 118.424, 'segs': [{'end': 39.075, 'src': 'embed', 'start': 0.049, 'weight': 0, 'content': [{'end': 1.51, 'text': 'Hi everyone, Kevin here.', 'start': 0.049, 'duration': 1.461}, {'end': 8.175, 'text': 'Today, I want to show you the top 20 formulas and functions in Google Sheets.', 'start': 1.65, 'duration': 6.525}, {'end': 10.197, 'text': "We're going to start off pretty basic.", 'start': 8.496, 'duration': 1.701}, {'end': 15.321, 'text': "If you've never used Google Sheets before, this will give you a good foundation to build upon.", 'start': 10.237, 'duration': 5.084}, {'end': 19.824, 'text': "As we go through formula by formula, we're going to get a little more complex.", 'start': 15.761, 'duration': 4.063}, {'end': 26.87, 'text': "By the end of this video, if you run through all of these, you'll be pretty proficient in using formulas and functions in Google Sheets.", 'start': 20.185, 'duration': 6.685}, {'end': 30.532, 'text': 'Feel free to use the timestamps down below to jump around.', 'start': 27.35, 'duration': 3.182}, {'end': 33.593, 'text': "Otherwise, let's jump on the PC and get started.", 'start': 30.872, 'duration': 2.721}, {'end': 39.075, 'text': "Here I am in Google Sheets and formula number one that we're looking at is addition.", 'start': 33.853, 'duration': 5.222}], 'summary': 'In this tutorial, kevin demonstrates the top 20 formulas and functions in google sheets, starting with basic concepts and progressing to more complex ones, aimed at making the viewers proficient in using these tools.', 'duration': 39.026, 'max_score': 0.049, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/9TVmdFtmbbo/pics/9TVmdFtmbbo49.jpg'}, {'end': 99.872, 'src': 'embed', 'start': 75.976, 'weight': 2, 'content': [{'end': 85.403, 'text': 'the power from a spreadsheet comes from being able to refer to different cells within the spreadsheet versus using it like a calculator and entering in the numbers you want to add up.', 'start': 75.976, 'duration': 9.427}, {'end': 90.986, 'text': 'For instance, we sold 200 chocolate chip cookies and 150 snickerdoodle cookies.', 'start': 85.863, 'duration': 5.123}, {'end': 94.769, 'text': 'What if I want to sum these up, to know how many we sold in total?', 'start': 91.427, 'duration': 3.342}, {'end': 99.872, 'text': 'Well, instead of typing in the individual values, I could refer to the individual cells.', 'start': 95.129, 'duration': 4.743}], 'summary': 'Using spreadsheet, 200 chocolate chip cookies and 150 snickerdoodle cookies are referred to cells to calculate total sales.', 'duration': 23.896, 'max_score': 75.976, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/9TVmdFtmbbo/pics/9TVmdFtmbbo75976.jpg'}], 'start': 0.049, 'title': 'Top 20 formulas and functions in google sheets', 'summary': 'Covers the top 20 formulas and functions in google sheets, providing a good foundation for proficient use, allowing users to sum up values and refer to different cells within the spreadsheet.', 'chapters': [{'end': 118.424, 'start': 0.049, 'title': 'Top 20 formulas and functions in google sheets', 'summary': 'Covers the top 20 formulas and functions in google sheets, starting with basic addition and progressing to referencing cells, providing a good foundation for proficient use, allowing users to sum up values and refer to different cells within the spreadsheet.', 'duration': 118.375, 'highlights': ['The chapter covers the top 20 formulas and functions in Google Sheets. The video discusses the top 20 formulas and functions in Google Sheets, providing a comprehensive overview.', 'Starting with basic addition and progressing to referencing cells, providing a good foundation for proficient use. The tutorial begins with basic addition and progresses to referencing cells, offering a foundation for proficient use.', 'Allowing users to sum up values and refer to different cells within the spreadsheet. The tutorial explains how to sum up values and refer to different cells within the spreadsheet, enhancing functionality.']}], 'duration': 118.375, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/9TVmdFtmbbo/pics/9TVmdFtmbbo49.jpg', 'highlights': ['The chapter covers the top 20 formulas and functions in Google Sheets, providing a comprehensive overview.', 'Starting with basic addition and progressing to referencing cells, offering a foundation for proficient use.', 'The tutorial explains how to sum up values and refer to different cells within the spreadsheet, enhancing functionality.']}, {'end': 504.69, 'segs': [{'end': 141.938, 'src': 'embed', 'start': 118.864, 'weight': 0, 'content': [{'end': 126.449, 'text': "Now just like before, I'll press the enter key and I could see that we sold 350 chocolate chip cookies and snickerdoodle cookies.", 'start': 118.864, 'duration': 7.585}, {'end': 128.31, 'text': "That's a pretty good sales total.", 'start': 126.829, 'duration': 1.481}, {'end': 131.852, 'text': "Next, let's take a look at number two, subtraction.", 'start': 128.69, 'duration': 3.162}, {'end': 136.134, 'text': 'How many more chocolate chip cookies did we sell than snickerdoodle cookies?', 'start': 132.312, 'duration': 3.822}, {'end': 141.938, 'text': 'Well, this is a subtraction question and we want to take 200 and subtract 150..', 'start': 136.195, 'duration': 5.743}], 'summary': 'Sold 350 cookies, 200 chocolate chip cookies sold, 150 snickerdoodle cookies sold, a good sales total', 'duration': 23.074, 'max_score': 118.864, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/9TVmdFtmbbo/pics/9TVmdFtmbbo118864.jpg'}, {'end': 199.993, 'src': 'embed', 'start': 161.288, 'weight': 1, 'content': [{'end': 165.53, 'text': 'So how many more chocolate chip cookies did we sell than snickerdoodles? It turns out it was 50 more.', 'start': 161.288, 'duration': 4.242}, {'end': 167.971, 'text': 'I guess chocolate chip cookies are more popular.', 'start': 165.89, 'duration': 2.081}, {'end': 172.495, 'text': 'This brings us now to formula number three, multiplication.', 'start': 168.411, 'duration': 4.084}, {'end': 181.542, 'text': 'At the Kevin Cookie Company, we currently have a promotion going on where for every chocolate chip cookie we sell, we donate $2 to charity.', 'start': 172.515, 'duration': 9.027}, {'end': 184.384, 'text': "Yes, we're doing some civic good at the Kevin Cookie Company.", 'start': 181.602, 'duration': 2.782}, {'end': 191.49, 'text': "So I want to take our 200 cookies and multiply it by $2 to see how much money we're going to donate to charity.", 'start': 184.805, 'duration': 6.685}, {'end': 193.352, 'text': "That's a multiplication question.", 'start': 191.81, 'duration': 1.542}, {'end': 199.993, 'text': "Now once again, let's enter the equal sign and I'm going to refer up to the 200 chocolate chip cookies sold.", 'start': 193.772, 'duration': 6.221}], 'summary': '50 more chocolate chip cookies sold than snickerdoodles, $2 donation for each cookie sold.', 'duration': 38.705, 'max_score': 161.288, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/9TVmdFtmbbo/pics/9TVmdFtmbbo161288.jpg'}, {'end': 294.452, 'src': 'embed', 'start': 265.849, 'weight': 5, 'content': [{'end': 271.494, 'text': "So we're going to take our 200 cookies that sold, divide by about 100 per employee and then hit enter.", 'start': 265.849, 'duration': 5.645}, {'end': 275.858, 'text': 'So it turns out that we need two employees to be able to make these 200 cookies.', 'start': 272.054, 'duration': 3.804}, {'end': 282.823, 'text': "Now that we've looked at addition, subtraction, multiplication, and division, let's bring it all together.", 'start': 277.339, 'duration': 5.484}, {'end': 287.286, 'text': 'I want to know how many employees we need to make all of these cookies.', 'start': 283.324, 'duration': 3.962}, {'end': 294.452, 'text': "First I'm going to add up the total number of cookies sold and then I want to divide it by 100 cookies per employee,", 'start': 287.767, 'duration': 6.685}], 'summary': '200 cookies sold, 2 employees needed for production.', 'duration': 28.603, 'max_score': 265.849, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/9TVmdFtmbbo/pics/9TVmdFtmbbo265849.jpg'}, {'end': 387.575, 'src': 'heatmap', 'start': 361.523, 'weight': 0.713, 'content': [{'end': 365.786, 'text': "I'm going to open the parentheses and now I could select all of these values.", 'start': 361.523, 'duration': 4.263}, {'end': 373.01, 'text': 'I could close the parentheses and when I hit enter, Google Sheets will automatically identify which one sold the least.', 'start': 366.186, 'duration': 6.824}, {'end': 376.252, 'text': "Here I could see the min was 50 and that's our peanut butter cookie.", 'start': 373.05, 'duration': 3.202}, {'end': 379.693, 'text': 'Just like with min, I can also enter max.', 'start': 376.852, 'duration': 2.841}, {'end': 387.575, 'text': "So now instead of entering min, I'll type in max, open the parentheses, highlight all of these values and close the parentheses and hit enter.", 'start': 379.713, 'duration': 7.862}], 'summary': 'Using google sheets, identified the least sold item (peanut butter cookie) as 50 units and the most sold item using functions like min and max.', 'duration': 26.052, 'max_score': 361.523, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/9TVmdFtmbbo/pics/9TVmdFtmbbo361523.jpg'}, {'end': 454.505, 'src': 'embed', 'start': 428.359, 'weight': 4, 'content': [{'end': 434.068, 'text': "I'm going to open the parentheses and highlight all seven of these values or this range of values,", 'start': 428.359, 'duration': 5.709}, {'end': 436.672, 'text': "and then I'm going to close the parentheses and hit enter.", 'start': 434.068, 'duration': 2.604}, {'end': 441.119, 'text': 'So here I could see that on average for one of our cookie types, we sell about 103.57 cookies.', 'start': 437.152, 'duration': 3.967}, {'end': 448.163, 'text': "Next, let's look at the median and the median is the middle most number.", 'start': 443.442, 'duration': 4.721}, {'end': 454.505, 'text': "So here looking at all the numbers above, my guess is it's probably 100 but let's let Google figure this out for us.", 'start': 448.523, 'duration': 5.982}], 'summary': 'The average number of cookies sold for one type is 103.57, and the median is around 100.', 'duration': 26.146, 'max_score': 428.359, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/9TVmdFtmbbo/pics/9TVmdFtmbbo428359.jpg'}], 'start': 118.864, 'title': 'Google sheets formulas and usage', 'summary': 'Covers the usage of google sheets formulas to perform calculations, including selling 350 chocolate chip and snickerdoodle cookies, calculating the difference between the two types of cookies sold, determining the amount to be donated to charity, and using various formulas for practical examples and calculations such as addition, subtraction, multiplication, division, min, max, mean, median, and mode.', 'chapters': [{'end': 221.997, 'start': 118.864, 'title': 'Google sheets formulas tutorial', 'summary': 'Covers the usage of google sheets formulas to perform calculations, including selling 350 chocolate chip and snickerdoodle cookies, calculating the difference between the two types of cookies sold, and determining the amount to be donated to charity by multiplying the number of chocolate chip cookies sold by $2.', 'duration': 103.133, 'highlights': ['Selling 350 chocolate chip and snickerdoodle cookies The sales total for chocolate chip and snickerdoodle cookies amounted to 350, indicating a successful sales outcome.', 'Calculating the difference between chocolate chip and snickerdoodle cookies sold The chapter demonstrates a subtraction question to determine that 200 chocolate chip cookies were sold, which is 50 more than the 150 snickerdoodle cookies sold, indicating the popularity of chocolate chip cookies.', 'Determining the amount to be donated to charity by multiplying the number of chocolate chip cookies sold by $2 The tutorial illustrates a multiplication question where the company pledges to donate $2 for every chocolate chip cookie sold, resulting in a donation calculation of $400 for the sale of 200 cookies.']}, {'end': 504.69, 'start': 222.697, 'title': 'Using formulas in google sheets', 'summary': 'Covers using formulas in google sheets, including addition, subtraction, multiplication, division, min, max, mean, median, and mode, with practical examples and calculations, such as needing 2 employees to make 200 cookies, selling 200 of the most popular cookie, and averaging about 103.57 cookies per type.', 'duration': 281.993, 'highlights': ['Needing 2 employees to make 200 cookies. It turns out that we need two employees to be able to make these 200 cookies.', 'Selling 200 of the most popular cookie. We sold the most of the chocolate chip cookie where we sold 200 cookies.', 'Averaging about 103.57 cookies per type. Here I could see that on average for one of our cookie types, we sell about 103.57 cookies.']}], 'duration': 385.826, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/9TVmdFtmbbo/pics/9TVmdFtmbbo118864.jpg', 'highlights': ['Selling 350 chocolate chip and snickerdoodle cookies The sales total for chocolate chip and snickerdoodle cookies amounted to 350, indicating a successful sales outcome.', 'Determining the amount to be donated to charity by multiplying the number of chocolate chip cookies sold by $2 The tutorial illustrates a multiplication question where the company pledges to donate $2 for every chocolate chip cookie sold, resulting in a donation calculation of $400 for the sale of 200 cookies.', 'Calculating the difference between chocolate chip and snickerdoodle cookies sold The chapter demonstrates a subtraction question to determine that 200 chocolate chip cookies were sold, which is 50 more than the 150 snickerdoodle cookies sold, indicating the popularity of chocolate chip cookies.', 'Selling 200 of the most popular cookie. We sold the most of the chocolate chip cookie where we sold 200 cookies.', 'Averaging about 103.57 cookies per type. Here I could see that on average for one of our cookie types, we sell about 103.57 cookies.', 'Needing 2 employees to make 200 cookies. It turns out that we need two employees to be able to make these 200 cookies.']}, {'end': 687.05, 'segs': [{'end': 548.104, 'src': 'embed', 'start': 520.777, 'weight': 0, 'content': [{'end': 527.56, 'text': "i want to add up how many cookies we sold, but i don't want to click through and have to say well, this cell plus, this cell plus this cell,", 'start': 520.777, 'duration': 6.783}, {'end': 528.821, 'text': "because that's going to take a while.", 'start': 527.56, 'duration': 1.261}, {'end': 533.987, 'text': 'Luckily, we can use the sum formula function to calculate this for us.', 'start': 529.301, 'duration': 4.686}, {'end': 540.595, 'text': "First off, just like we've been doing this whole time, let's enter the equal sign and now let's type in sum.", 'start': 534.367, 'duration': 6.228}, {'end': 548.104, 'text': "Let's open the parentheses and I could simply highlight all of the values that I want to sum or all the values that I want to add up.", 'start': 541.175, 'duration': 6.929}], 'summary': 'Use the sum formula to calculate total cookies sold efficiently.', 'duration': 27.327, 'max_score': 520.777, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/9TVmdFtmbbo/pics/9TVmdFtmbbo520777.jpg'}, {'end': 599.081, 'src': 'embed', 'start': 561.114, 'weight': 1, 'content': [{'end': 569.28, 'text': 'As another added bonus, you can also highlight all of the values down below and in the bottom right-hand corner, you can see a quick sum down here.', 'start': 561.114, 'duration': 8.166}, {'end': 576.126, 'text': "Along with sum, you can see the average, the min, the max, the count, and other helpful values if you don't want to type in a formula.", 'start': 569.641, 'duration': 6.485}, {'end': 577.447, 'text': 'So just an added bonus.', 'start': 576.186, 'duration': 1.261}, {'end': 581.83, 'text': 'Formula number 11, what if I just want to sum up specific values?', 'start': 577.927, 'duration': 3.903}, {'end': 586.354, 'text': 'For instance, what if I just want to know how many chocolate chip cookies did we sell?', 'start': 582.23, 'duration': 4.124}, {'end': 592.338, 'text': "Now I could look at the list and say well, here's a chocolate chip cookie, here's a chocolate chip cookie and I could add them up.", 'start': 586.714, 'duration': 5.624}, {'end': 593.419, 'text': "it's 415..", 'start': 592.338, 'duration': 1.081}, {'end': 599.081, 'text': "But imagine you had a massive list and you can't just eyeball it, that's where SUMIF comes in.", 'start': 593.419, 'duration': 5.662}], 'summary': 'You can use sumif to quickly sum specific values, like 415 chocolate chip cookies sold.', 'duration': 37.967, 'max_score': 561.114, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/9TVmdFtmbbo/pics/9TVmdFtmbbo561114.jpg'}, {'end': 690.412, 'src': 'heatmap', 'start': 646.383, 'weight': 4, 'content': [{'end': 651.266, 'text': 'Basically, what that means is when it finds chocolate chip, what value should it add up?', 'start': 646.383, 'duration': 4.883}, {'end': 654.588, 'text': 'and I wanted to use this column with the number sold.', 'start': 651.266, 'duration': 3.322}, {'end': 659.832, 'text': "Now I'm all done with my formula so I can close the parentheses and hit enter.", 'start': 655.329, 'duration': 4.503}, {'end': 664.855, 'text': 'This tells me now that we sold 415 chocolate chip cookies.', 'start': 660.732, 'duration': 4.123}, {'end': 668.898, 'text': "That's our most popular cookie, so it's understandable that it sold so much.", 'start': 665.175, 'duration': 3.723}, {'end': 670.759, 'text': 'Formula number 12.', 'start': 669.358, 'duration': 1.401}, {'end': 674.541, 'text': 'I want to count how many cookies had any sales at all.', 'start': 670.759, 'duration': 3.782}, {'end': 679.805, 'text': 'Now, when I look at this table here, I see that five different types of cookies had some sales.', 'start': 674.942, 'duration': 4.863}, {'end': 681.986, 'text': 'Two of the cookies had no sales.', 'start': 680.225, 'duration': 1.761}, {'end': 687.05, 'text': "I guess graham cracker cookies and whoopie pie cookies aren't that popular at our NYC location.", 'start': 682.026, 'duration': 5.024}, {'end': 690.412, 'text': 'I can use the count formula to help me with this.', 'start': 687.63, 'duration': 2.782}], 'summary': '415 chocolate chip cookies sold, 5 types had sales, 2 had none.', 'duration': 44.029, 'max_score': 646.383, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/9TVmdFtmbbo/pics/9TVmdFtmbbo646383.jpg'}], 'start': 504.69, 'title': 'Cookie sales tracking', 'summary': 'Covers formulas for summing cookies, including the sum function resulting in 1,528 cookies, the excel sumif function for specific data points, and an excel formula tracking the sales of chocolate chip cookies, with 415 sold and insights into sales of other cookie types.', 'chapters': [{'end': 560.574, 'start': 504.69, 'title': 'Formula 10: summing cookies sold', 'summary': 'Introduces formula number 10, the sum function, which simplifies the process of adding up the number of cookies sold across new york city and london stores, resulting in a total of 1,528 cookies.', 'duration': 55.884, 'highlights': ['The sum formula function is introduced to calculate the total number of cookies sold across New York City and London stores, resulting in 1,528 cookies.', 'The speaker emphasizes the time-saving aspect of using the sum formula function to avoid manually adding up each cell, highlighting its utility in simplifying tasks.']}, {'end': 619.689, 'start': 561.114, 'title': 'Excel sumif function', 'summary': 'Explains the added bonus of using the sumif function in excel to quickly sum, average, find the min, max, count, and other helpful values for specific data points, exemplified by summing up the sales of chocolate chip cookies.', 'duration': 58.575, 'highlights': ['The chapter emphasizes the added bonus of using the SUMIF function in Excel to quickly sum, average, find the min, max, count, and other helpful values for specific data points.', 'It provides an example of using the SUMIF function to sum up the sales of a specific item, such as chocolate chip cookies, in a large dataset to avoid manual counting.', 'The speaker explains the process of using the SUMIF function by entering the equal sign, typing SUMIF, and specifying the range to be evaluated against.']}, {'end': 687.05, 'start': 620.07, 'title': 'Excel formula for tracking chocolate chip cookie sales', 'summary': 'Demonstrates the usage of an excel formula to track the sales of chocolate chip cookies, revealing that 415 chocolate chip cookies were sold, making it the most popular, while also highlighting that 5 out of the 7 cookie types had some sales.', 'duration': 66.98, 'highlights': ['The formula tracked and revealed that 415 chocolate chip cookies were sold, indicating its popularity.', '5 out of the 7 cookie types had some sales, while graham cracker and whoopie pie cookies had no sales at the NYC location.']}], 'duration': 182.36, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/9TVmdFtmbbo/pics/9TVmdFtmbbo504690.jpg', 'highlights': ['The sum formula function results in 1,528 cookies sold.', 'The time-saving aspect of using the sum formula function is emphasized.', 'The SUMIF function in Excel is highlighted for quickly summing specific data points.', 'Using the SUMIF function to sum up the sales of chocolate chip cookies is exemplified.', 'The formula tracked and revealed that 415 chocolate chip cookies were sold.', 'Insights into the sales of other cookie types are provided.']}, {'end': 907.128, 'segs': [{'end': 771.01, 'src': 'heatmap', 'start': 714.489, 'weight': 0, 'content': [{'end': 721.174, 'text': "When I hit enter, it's only going to count the numeric values so it tells me that five cookies had some sales associated with them.", 'start': 714.489, 'duration': 6.685}, {'end': 727.131, 'text': "Now let's say I just want to know how many different types of cookies were for sale, whether or not they had sales.", 'start': 721.567, 'duration': 5.564}, {'end': 730.333, 'text': "That's where we could use the COUNTA formula.", 'start': 727.571, 'duration': 2.762}, {'end': 735.196, 'text': 'Here when we look at this, well, we had seven different types of cookies for sale.', 'start': 730.893, 'duration': 4.303}, {'end': 737.078, 'text': "So let's count this up.", 'start': 735.557, 'duration': 1.521}, {'end': 740.14, 'text': "So once again, let's enter the equal sign, type in COUNTA.", 'start': 737.158, 'duration': 2.982}, {'end': 744.125, 'text': 'Open the parentheses and we could simply highlight all of these cells.', 'start': 740.72, 'duration': 3.405}, {'end': 747.689, 'text': "Once again, it's going to calculate any cell that has a value in it.", 'start': 744.585, 'duration': 3.104}, {'end': 752.816, 'text': 'When I hit enter, we were selling seven different cookies whether or not they had any sales.', 'start': 748.03, 'duration': 4.786}, {'end': 759.582, 'text': "Lastly, let's use COUNTIF to count just the cookies that had at least 130 sales.", 'start': 753.617, 'duration': 5.965}, {'end': 763.705, 'text': 'So basically what COUNTIF does is you could set some type of criteria.', 'start': 759.983, 'duration': 3.722}, {'end': 766.507, 'text': 'In this case, it had sales over 130.', 'start': 763.805, 'duration': 2.702}, {'end': 771.01, 'text': "So here, once again, let's insert the equal sign and now we'll type in COUNTIF.", 'start': 766.507, 'duration': 4.503}], 'summary': 'Using counta, countif to count cookie types and sales data.', 'duration': 61.305, 'max_score': 714.489, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/9TVmdFtmbbo/pics/9TVmdFtmbbo714489.jpg'}, {'end': 833.644, 'src': 'heatmap', 'start': 787.159, 'weight': 3, 'content': [{'end': 790.382, 'text': 'And once again, I want to know cookies that sold over 130.', 'start': 787.159, 'duration': 3.223}, {'end': 798.57, 'text': "I'm going to insert quotes and I want to type in greater than 130 and then I'll close the quotes and close the parentheses and hit enter.", 'start': 790.382, 'duration': 8.188}, {'end': 806.298, 'text': "So only two of our cookies sold over 130 and that's the chocolate chip and the snickerdoodle, our two most popular cookies.", 'start': 798.931, 'duration': 7.367}, {'end': 810.599, 'text': 'Formula number 15, and this is a long word, concatenate.', 'start': 806.678, 'duration': 3.921}, {'end': 818.26, 'text': 'So what does concatenate mean? Well, you can take two values in different cells and then you can bring them together into one cell.', 'start': 810.799, 'duration': 7.461}, {'end': 822.601, 'text': "And you're not just limited to two cells, you could bring together any number of items.", 'start': 818.561, 'duration': 4.04}, {'end': 827.542, 'text': 'For instance, right now, Cookie is in cell A3 and Monster is in cell B3.', 'start': 822.961, 'duration': 4.581}, {'end': 829.163, 'text': "I'd like to bring them together.", 'start': 827.883, 'duration': 1.28}, {'end': 833.644, 'text': "So once again, let's enter the equal sign and then type in concatenate.", 'start': 829.263, 'duration': 4.381}], 'summary': 'Two cookies sold over 130; chocolate chip and snickerdoodle are the most popular.', 'duration': 46.485, 'max_score': 787.159, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/9TVmdFtmbbo/pics/9TVmdFtmbbo787159.jpg'}, {'end': 895.04, 'src': 'heatmap', 'start': 868.67, 'weight': 4, 'content': [{'end': 875.233, 'text': "The way to read this is we're combining cell A3, then we're combining a space and then we're combining cell B3.", 'start': 868.67, 'duration': 6.563}, {'end': 880.915, 'text': "Now that I'm all done, let's hit enter and yes, look at that, Cookie Monster has a space between him.", 'start': 875.873, 'duration': 5.042}, {'end': 887.417, 'text': 'Formula number 16 is the IF function and this allows us to run logic against our sheet.', 'start': 881.255, 'duration': 6.162}, {'end': 889.338, 'text': 'Now at the Kevin Cookie Company.', 'start': 887.838, 'duration': 1.5}, {'end': 895.04, 'text': 'we recently got our customer favorability ratings back and, as you can see and as expected,', 'start': 889.338, 'duration': 5.702}], 'summary': 'Combining cells a3 and b3 to create space. if function for logic. favorability ratings received at kevin cookie company.', 'duration': 26.37, 'max_score': 868.67, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/9TVmdFtmbbo/pics/9TVmdFtmbbo868670.jpg'}], 'start': 687.63, 'title': 'Using count, counta, and countif formulas and concatenating cells in excel', 'summary': 'Explains the usage of count, counta, and countif formulas to calculate numeric values, different types of items for sale, and items that meet specific criteria, with examples showing five cookies had sales, seven different types of cookies were for sale, and only two cookies sold over 130. it also explains the concatenate function for combining values from different cells and introduces the if function for logic operations, using a specific example of customer favorability ratings at the kevin cookie company.', 'chapters': [{'end': 810.599, 'start': 687.63, 'title': 'Using count, counta, and countif formulas', 'summary': 'Explains the usage of count, counta, and countif formulas in excel to calculate the numeric values, different types of items for sale, and items that meet specific criteria, with examples showing that five cookies had sales, seven different types of cookies were for sale, and only two cookies sold over 130.', 'duration': 122.969, 'highlights': ['The count function calculates only the numeric values, showing that five cookies had some sales associated with them. The count function is used to calculate the numeric values of cookies that had sales, indicating that five cookies had sales associated with them.', 'The countA function calculates any cell that has a value in it, indicating that seven different cookies were for sale. The countA function is used to calculate any cell that has a value in it, showing that seven different types of cookies were for sale.', 'The countIF function is used to count just the cookies that had at least 130 sales, showing that only two cookies sold over 130. The countIF function calculates the cookies that met the specific criterion of sales over 130, indicating that only two cookies, chocolate chip and snickerdoodle, sold over 130.']}, {'end': 907.128, 'start': 810.799, 'title': 'Concatenating cells in excel', 'summary': 'Explains the concatenate function in excel, demonstrating how to combine values from different cells, including adding spaces, and introduces the if function for logic operations, using a specific example of customer favorability ratings at the kevin cookie company.', 'duration': 96.329, 'highlights': ['The CONCATENATE function in Excel allows users to combine values from different cells, demonstrating the ability to bring together any number of items.', 'Demonstrating the addition of a space between concatenated values in Excel using the CONCATENATE function, improving the presentation of the combined data.', 'Introduction to the IF function in Excel for running logic against the sheet, exemplified by analyzing customer favorability ratings at the Kevin Cookie Company and identifying trouble spots.']}], 'duration': 219.498, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/9TVmdFtmbbo/pics/9TVmdFtmbbo687630.jpg', 'highlights': ['The countIF function calculates the cookies that met the specific criterion of sales over 130, indicating that only two cookies, chocolate chip and snickerdoodle, sold over 130.', 'The countA function is used to calculate any cell that has a value in it, showing that seven different types of cookies were for sale.', 'The count function is used to calculate the numeric values of cookies that had sales, indicating that five cookies had sales associated with them.', 'The CONCATENATE function in Excel allows users to combine values from different cells, demonstrating the ability to bring together any number of items.', 'Introduction to the IF function in Excel for running logic against the sheet, exemplified by analyzing customer favorability ratings at the Kevin Cookie Company and identifying trouble spots.']}, {'end': 1157.038, 'segs': [{'end': 931.093, 'src': 'embed', 'start': 907.549, 'weight': 0, 'content': [{'end': 915.54, 'text': 'Now I want to use an if function to evaluate if we need to investigate any of these cookies to see if there are any quality issues.', 'start': 907.549, 'duration': 7.991}, {'end': 920.527, 'text': "So once again, like we've been doing throughout, enter the equal sign and let's type in if.", 'start': 915.961, 'duration': 4.566}, {'end': 923.409, 'text': "Next, let's open our parentheses.", 'start': 921.468, 'duration': 1.941}, {'end': 927.351, 'text': 'Once again, you see helpful information about how you can use the formula.', 'start': 923.549, 'duration': 3.802}, {'end': 931.093, 'text': 'Now it starts off by saying enter a logical expression.', 'start': 927.831, 'duration': 3.262}], 'summary': 'Using an if function to evaluate cookies for quality issues.', 'duration': 23.544, 'max_score': 907.549, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/9TVmdFtmbbo/pics/9TVmdFtmbbo907549.jpg'}, {'end': 1012.473, 'src': 'heatmap', 'start': 945.52, 'weight': 0.778, 'content': [{'end': 958.51, 'text': "5 Next, I'll insert a comma and if it's true, if it is greater, I'll simply say all good because we don't need to take any action.", 'start': 945.52, 'duration': 12.99}, {'end': 962.994, 'text': "Next I'll enter a comma, and if it's not true, what do we want to do??", 'start': 959.111, 'duration': 3.883}, {'end': 964.556, 'text': 'Well, we need to investigate.', 'start': 963.054, 'duration': 1.502}, {'end': 972.003, 'text': "so I'll insert another quote and here I'll say investigate, and let's throw an exclamation mark in there, just to give it some urgency.", 'start': 964.556, 'duration': 7.447}, {'end': 976.926, 'text': "Then I'll close the quotes and close the parentheses and there's my if formula.", 'start': 972.643, 'duration': 4.283}, {'end': 981.589, 'text': 'When I hit enter now, I see that our chocolate cookies in the United States are all good.', 'start': 977.326, 'duration': 4.263}, {'end': 988.773, 'text': 'Now if I want to pull this formula and apply it everywhere else, I could simply click on the corner here and drag it all the way down.', 'start': 981.609, 'duration': 7.164}, {'end': 992.456, 'text': 'This will apply the formula to each individual row here.', 'start': 989.234, 'duration': 3.222}, {'end': 997, 'text': 'Now, I see that for the fortune cookies in the United States, we need to investigate that.', 'start': 992.936, 'duration': 4.064}, {'end': 998.441, 'text': "That's a very low rating.", 'start': 997.06, 'duration': 1.381}, {'end': 1001.403, 'text': "And here in China, maybe we'll just discontinue the cookie.", 'start': 998.821, 'duration': 2.582}, {'end': 1006.788, 'text': 'Formula number 17 is VLOOKUP, and that stands for vertical lookup.', 'start': 1001.543, 'duration': 5.245}, {'end': 1012.473, 'text': 'I have a table here with countries and their associated sales, revenue, cost and profit,', 'start': 1007.288, 'duration': 5.185}], 'summary': 'Using if formula to determine actions based on conditions, applying vlookup for sales data.', 'duration': 66.953, 'max_score': 945.52, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/9TVmdFtmbbo/pics/9TVmdFtmbbo945520.jpg'}, {'end': 1071.408, 'src': 'embed', 'start': 1040.711, 'weight': 1, 'content': [{'end': 1042.612, 'text': 'This is where VLOOKUP can help us.', 'start': 1040.711, 'duration': 1.901}, {'end': 1048.736, 'text': "Like we've been doing this entire time, let's insert an equal sign, and for the formula, let's type in VLOOKUP.", 'start': 1043.032, 'duration': 5.704}, {'end': 1054.439, 'text': "Next let's open the parentheses and first it's going to say what are you searching for?", 'start': 1049.336, 'duration': 5.103}, {'end': 1059.581, 'text': "Well, we're searching for China, because if it's China, well, there's a specific manager in China.", 'start': 1054.459, 'duration': 5.122}, {'end': 1062.963, 'text': 'so this is our search key, or the lookup value.', 'start': 1059.581, 'duration': 3.382}, {'end': 1064.764, 'text': "So I'm going to select that cell.", 'start': 1062.983, 'duration': 1.781}, {'end': 1066.625, 'text': "Next, let's enter a comma.", 'start': 1064.924, 'duration': 1.701}, {'end': 1071.408, 'text': "Next, it's going to ask me what is the range that I'm looking for this value in.", 'start': 1067.205, 'duration': 4.203}], 'summary': "Using vlookup to search for 'china' in a specific range.", 'duration': 30.697, 'max_score': 1040.711, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/9TVmdFtmbbo/pics/9TVmdFtmbbo1040711.jpg'}], 'start': 907.549, 'title': 'Using if function and vlookup in excel', 'summary': "Explains how to use the 'if' function to evaluate cookies for investigation, with a 50% threshold, and the use of vlookup to retrieve specific data, saving time in data analysis.", 'chapters': [{'end': 976.926, 'start': 907.549, 'title': 'Using if function for quality investigation', 'summary': "Explains how to use the 'if' function in excel to evaluate if cookies need investigation based on a favorability rating test, with a threshold of 50%, and provides a clear formula for the 'if' function.", 'duration': 69.377, 'highlights': ["The 'if' function is used to evaluate if cookies need investigation based on a favorability rating test, with a threshold of 50%, providing a clear formula for the 'if' function.", "The logical expression for the 'if' function is set to test if 94% is greater than 50% to determine if investigation is needed.", "The 'if' function provides two outcomes: 'all good' if the condition is met, and 'investigate!' if the condition is not met."]}, {'end': 1157.038, 'start': 977.326, 'title': 'Vlookup in excel', 'summary': 'Explains the use of vlookup in excel to efficiently retrieve specific data, such as the store manager for each country, with an example of how to apply it to a large table, saving time and effort in data analysis.', 'duration': 179.712, 'highlights': ['The chapter explains the use of VLOOKUP in Excel to efficiently retrieve specific data, such as the store manager for each country. VLOOKUP function is used to search for a value in a table and retrieve a corresponding value from another column, as demonstrated with the example of retrieving the store manager for each country.', 'An example demonstrates how to apply VLOOKUP to a large table, saving time and effort in data analysis. The example showcases how VLOOKUP can be used to quickly and accurately retrieve specific data from a large table, thereby streamlining the data analysis process.', 'Explanation of the different components of the VLOOKUP function, such as the search key, range, index, and sorted parameters. Detailed explanation of the various components of the VLOOKUP function, including the search key, range, index, and sorted parameters, providing a comprehensive understanding of its usage.']}], 'duration': 249.489, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/9TVmdFtmbbo/pics/9TVmdFtmbbo907549.jpg', 'highlights': ["The 'if' function is used to evaluate if cookies need investigation based on a favorability rating test, with a threshold of 50%, providing a clear formula for the 'if' function.", 'The chapter explains the use of VLOOKUP in Excel to efficiently retrieve specific data, such as the store manager for each country. VLOOKUP function is used to search for a value in a table and retrieve a corresponding value from another column, as demonstrated with the example of retrieving the store manager for each country.']}, {'end': 1390.843, 'segs': [{'end': 1228.059, 'src': 'embed', 'start': 1201.613, 'weight': 0, 'content': [{'end': 1205.457, 'text': "So, for the value, I'm going to select 375.", 'start': 1201.613, 'duration': 3.844}, {'end': 1209.281, 'text': "Next, I'll insert a comma and it wants to know what is the starting unit.", 'start': 1205.457, 'duration': 3.824}, {'end': 1214.365, 'text': 'Now, over here on the right-hand side, it shows all the different units that you can convert between.', 'start': 1209.721, 'duration': 4.644}, {'end': 1217.629, 'text': 'So, you could convert in weight, in distance, in time.', 'start': 1214.766, 'duration': 2.863}, {'end': 1222.714, 'text': 'In this specific example, this is temperature, and here I see what I can convert between.', 'start': 1218.209, 'duration': 4.505}, {'end': 1228.059, 'text': 'So here I can convert between Fahrenheit, which is the F, and Celsius, which is the C.', 'start': 1223.094, 'duration': 4.965}], 'summary': 'Select 375 as starting value and convert between fahrenheit and celsius.', 'duration': 26.446, 'max_score': 1201.613, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/9TVmdFtmbbo/pics/9TVmdFtmbbo1201613.jpg'}, {'end': 1264.04, 'src': 'heatmap', 'start': 1236.488, 'weight': 0.707, 'content': [{'end': 1242.51, 'text': 'a C because I want to convert into Celsius and then close the quotes and then close the parentheses and then hit enter.', 'start': 1236.488, 'duration': 6.022}, {'end': 1250.313, 'text': "Turns out that the ovens in Europe have to be running at 191 Celsius and that'll ensure that our cookies bake at the proper temperature.", 'start': 1242.911, 'duration': 7.402}, {'end': 1254.395, 'text': "Number 19, we're going to look at the today function.", 'start': 1250.834, 'duration': 3.561}, {'end': 1259.838, 'text': "If you've seen any of my YouTube videos, you realize that I always start off by saying today.", 'start': 1254.775, 'duration': 5.063}, {'end': 1264.04, 'text': 'So what does today actually mean? Well, I think Google Sheets might know.', 'start': 1259.858, 'duration': 4.182}], 'summary': 'Converting to celsius, ovens in europe at 191°c, using the today function in google sheets.', 'duration': 27.552, 'max_score': 1236.488, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/9TVmdFtmbbo/pics/9TVmdFtmbbo1236488.jpg'}, {'end': 1313.255, 'src': 'heatmap', 'start': 1264.48, 'weight': 1, 'content': [{'end': 1269.823, 'text': "Let's insert an equal sign, type in today, and then open and close a parentheses and hit enter.", 'start': 1264.48, 'duration': 5.343}, {'end': 1271.984, 'text': 'And this tells us what today is.', 'start': 1270.123, 'duration': 1.861}, {'end': 1273.985, 'text': 'Today is October 28th, 2020.', 'start': 1272.064, 'duration': 1.921}, {'end': 1281.349, 'text': "Now as an added bonus, what if you also want to know the time? Well, once again, let's enter an equal sign, type in now.", 'start': 1273.985, 'duration': 7.364}, {'end': 1289.796, 'text': 'Open and close your parentheses and hit enter and here we have not only the date of today, but we also have the time.', 'start': 1281.909, 'duration': 7.887}, {'end': 1293.559, 'text': 'Number 20 and this is the last one of today.', 'start': 1290.276, 'duration': 3.283}, {'end': 1300.705, 'text': "At the Kevin Cookie Company, we're looking at running a social media advertising campaign, but we're limited to 20 characters.", 'start': 1293.579, 'duration': 7.126}, {'end': 1309.292, 'text': 'We came up with all of these different taglines, But how do we know how many characters are in each tagline? Well, we can use the len function.', 'start': 1301.085, 'duration': 8.207}, {'end': 1313.255, 'text': 'To use this, enter an equal sign and then type in len.', 'start': 1309.692, 'duration': 3.563}], 'summary': "Today's date is october 28th, 2020. time is also displayed. limit for taglines is 20 characters.", 'duration': 48.775, 'max_score': 1264.48, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/9TVmdFtmbbo/pics/9TVmdFtmbbo1264480.jpg'}], 'start': 1157.478, 'title': 'Using excel and google sheets functions', 'summary': "Focuses on using excel functions for dragging formulas and using the convert function, including an example of converting 375 degrees fahrenheit to celsius, and also introduces the today and now functions in google sheets, with an example resulting in the current date and time, october 28th, 2020, and demonstrates the usage of the len function in google sheets to find taglines within the 20 character limit for a social media advertising campaign, resulting in three compliant taglines out of many, with 'love at first bite' being the chosen one.", 'chapters': [{'end': 1242.51, 'start': 1157.478, 'title': 'Excel functions: dragging formulas and using the convert function', 'summary': 'Explains how to use absolute references in dragging formulas to automatically fill in data, and also demonstrates the use of the convert function to easily switch between different units of measurement, as exemplified by converting 375 degrees fahrenheit to celsius.', 'duration': 85.032, 'highlights': ['The convert function in Excel can be used to easily switch between different units of measurement. The convert function in Excel allows for easy conversion between different units of measurement, such as temperature, weight, distance, and time.', 'Demonstrates the use of absolute reference in dragging formulas to automatically fill in data based on a table. The speaker demonstrates the use of absolute reference in dragging formulas to automatically fill in data from a table, ensuring that it only looks at the specified table for the required information.']}, {'end': 1289.796, 'start': 1242.911, 'title': 'Google sheets: today and now functions', 'summary': 'Introduces the today and now functions in google sheets, providing examples of their usage and resulting in the current date and time, october 28th, 2020, to ensure proper cookie baking, ovens in europe need to run at 191 celsius.', 'duration': 46.885, 'highlights': ['The ovens in Europe need to be running at 191 Celsius to ensure proper cookie baking.', 'The today function in Google Sheets displays the current date, which is October 28th, 2020.', 'The now function in Google Sheets displays the current date and time.']}, {'end': 1390.843, 'start': 1290.276, 'title': 'Social media tagline character limit', 'summary': "Demonstrates using the len function in google sheets to find taglines within the 20 character limit for a social media advertising campaign, resulting in three compliant taglines out of many, with 'love at first bite' being the chosen one.", 'duration': 100.567, 'highlights': ['The len function is used to determine the character count of taglines, resulting in the identification of three taglines that fall under the 20 character limit.', "The tagline 'love at first bite' is chosen for the social media campaign as it falls within the character limit, showcasing the practical application of the len function.", 'The knowledge of formulas and functions in Google Sheets enables proficiency in using spreadsheet applications like Microsoft Excel or WPS Office, contributing to the mastery of spreadsheets.']}], 'duration': 233.365, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/9TVmdFtmbbo/pics/9TVmdFtmbbo1157478.jpg', 'highlights': ['The convert function in Excel allows for easy conversion between different units of measurement, such as temperature, weight, distance, and time.', 'The today function in Google Sheets displays the current date, which is October 28th, 2020.', 'The len function is used to determine the character count of taglines, resulting in the identification of three taglines that fall under the 20 character limit.']}], 'highlights': ['The tutorial covers the top 20 formulas and functions in Google Sheets, providing a comprehensive overview.', 'The sum formula function results in 1,528 cookies sold.', 'Selling 350 chocolate chip and snickerdoodle cookies The sales total for chocolate chip and snickerdoodle cookies amounted to 350, indicating a successful sales outcome.', 'The countIF function calculates the cookies that met the specific criterion of sales over 130, indicating that only two cookies, chocolate chip and snickerdoodle, sold over 130.', "The 'if' function is used to evaluate if cookies need investigation based on a favorability rating test, with a threshold of 50%, providing a clear formula for the 'if' function.", 'The chapter explains the use of VLOOKUP in Excel to efficiently retrieve specific data, such as the store manager for each country. VLOOKUP function is used to search for a value in a table and retrieve a corresponding value from another column, as demonstrated with the example of retrieving the store manager for each country.', 'The convert function in Excel allows for easy conversion between different units of measurement, such as temperature, weight, distance, and time.', 'The today function in Google Sheets displays the current date, which is October 28th, 2020.']}