title
These are the ONLY 15 functions you need to know in Excel (to get most things done)

description
Learn the most important Excel functions and how to use them for business data analysis. Formulas & Techniques covered in this video: ==================================== SUMIFS COUNTIFS AVERAGEIFS XLOOKUP MAXIFS INDEX + MATCH FILTER MAX LARGE ~ How and when to use each formula Working with wildcards & patterns Using date ranges in your criteria Multiple criteria with formulas How to combine formulas to solve a problem Download the Sample Workbooks: ============================ ✅ Completed Workbook: https://chandoo.org/wp/wp-content/uploads/2022/03/top-formulas-for-data-analysis-answers.xlsx 🟩 Blank Workbook (to practice): https://chandoo.org/wp/wp-content/uploads/2022/03/top-formulas-for-data-analysis-blank.xlsx ⏱Topics in the video:👇 =================== 0:00 - Introduction to the data & 15 Formulas 1:20 - Q1 Total Downloads of SugarRush App (SUMIFS) 3:17 - Q2 Total downloads for C apps (wildcards) 5:04 - Q3 Uninstalls in December 2021 (Using Dates) 7:00 - Q4 Downloads in Oct, Nov, Dec 2021 (Date Ranges) 9:21 - Q5 How many times we had more than 8000 downloads per app month? (COUNTIFS) 10:52 - Q6 5 star rating % for Sugar Rush (Complex Problem) 12:31 - Q7 Average uninstalls for Subway Wafers (AVERAGEIFS) 13:10 - Q8 Which month has highest uninstall ratio? (MAX & XLOOKUP) 16:23 - Q9 Average monthly downloads (AVERAGEIFS) 17:06 - Q10 Most downloads in any month / app? 2nd and 3rd places also? (MAX, LARGE) 18:18 - Q11 Which month and app was that? (XLOOKUP, INDEX+MATCH) 20:02 - Q12 In October 2021, which app has most 1star ratings? (XLOOKUP vs. FILTER) 23:35 - Q13 Does it change by December 2021? (XLOOKUP vs. FILTER) 24:50 - Q14 What are the best download figures for each app? (MAXIFS) 26:00 - What is the least downloaded app in each month? (FILTER) 28:03 - How to learn more? 👋 ALTERNATIVE FORMULAS FOR OLDER EXCEL VERSIONS: ================================================== XLOOKUP alternative = Use INDEX MATCH. Instead of =XLOOKUP(F61,F53:F59,C53:C59) use = INDEX(C53:C59, MATCH(F61, F53:F59,0)) MAXIFS alternative = Use MAX(IF()) Array formula. Instead of =MAXIFS(data[Downloads],data[App],C99) Use =MAX(IF(data[App] = C99, data[Downloads])) and press CTRL+Shift+Enter to get the correct result. NEXT STEPS FOR YOU 👌 ==================== 💥💥💥 Do you want to learn how & when to use various Excel formulas, how they compare with other ways of analysing data in Excel? Consider going for Excel School program. In this online class, I teach how to use Excel and how to do your work better. Visit https://chandoo.org/wp/excel-school-program/ for full info & to enrol. 💥💥💥 Additional videos on the formulas: =========================== IF formula & Advanced IF formulas - https://youtu.be/-yFpzIRifK4 SUMIFS formula (10 examples) - https://youtu.be/YEt-aYbDTrs INDEX MATCH in detail - https://youtu.be/kly0uPIM4IU XLOOKUP - https://youtu.be/gpwqUc3y7ZA VLOOKUP - https://youtu.be/8rtvDQVQaA0 FILTER - https://youtu.be/JuTdj2j-9Kg ~ Don't forget to be AWESOME 😎 #Excel #DataAnalysis

detail
{'title': 'These are the ONLY 15 functions you need to know in Excel (to get most things done)', 'heatmap': [{'end': 407.733, 'start': 386.82, 'weight': 0.801}, {'end': 549.68, 'start': 525.461, 'weight': 0.786}, {'end': 648.517, 'start': 601.461, 'weight': 0.836}, {'end': 745.688, 'start': 701.398, 'weight': 0.936}, {'end': 956.856, 'start': 936.886, 'weight': 0.712}, {'end': 1136.68, 'start': 1110.586, 'weight': 0.739}, {'end': 1191.008, 'start': 1140.581, 'weight': 0.922}, {'end': 1274.27, 'start': 1255.154, 'weight': 0.839}, {'end': 1415.635, 'start': 1378.507, 'weight': 0.884}, {'end': 1752.855, 'start': 1751.69, 'weight': 0.951}], 'summary': 'Covers 15 business questions related to app download and installation data, demonstrating the use of excel functions like sumifs, countifs, and average.ifs for data analysis, app analytics, and data filtering, including specific examples and techniques.', 'chapters': [{'end': 296.932, 'segs': [{'end': 32.134, 'src': 'embed', 'start': 2.455, 'weight': 0, 'content': [{'end': 7.318, 'text': 'Today we are going to learn how to use Excel to answer business questions.', 'start': 2.455, 'duration': 4.863}, {'end': 16.123, 'text': 'I have got 15 data analysis questions here and I will show you how to use Excel formulas to answer these questions.', 'start': 8.079, 'duration': 8.044}, {'end': 25.45, 'text': 'Along way we are also going to learn some of the most powerful and useful Excel functions and how to apply them for answering the questions.', 'start': 16.644, 'duration': 8.806}, {'end': 32.134, 'text': 'I have provided a sample file for you to download so feel free to grab that and follow along.', 'start': 26.251, 'duration': 5.883}], 'summary': 'Learn to answer 15 business questions with excel formulas and functions.', 'duration': 29.679, 'max_score': 2.455, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/B5hayFelHDU/pics/B5hayFelHDU2455.jpg'}, {'end': 96.787, 'src': 'embed', 'start': 51.362, 'weight': 2, 'content': [{'end': 58.565, 'text': 'The data is available for the seven apps for each month starting June 2021.', 'start': 51.362, 'duration': 7.203}, {'end': 60.906, 'text': 'up until December 2021.', 'start': 58.565, 'duration': 2.341}, {'end': 62.487, 'text': 'For each app month.', 'start': 60.906, 'duration': 1.581}, {'end': 66.249, 'text': 'we know how many downloads were there, how many uninstalls were there,', 'start': 62.487, 'duration': 3.762}, {'end': 70.911, 'text': 'how many 5-star and 1-star ratings were available and how many total ratings are there.', 'start': 66.249, 'duration': 4.662}, {'end': 73.893, 'text': 'These are all the questions that we shall answer.', 'start': 71.752, 'duration': 2.141}, {'end': 79.516, 'text': "Let's go one question at a time and along way you will learn various formulas.", 'start': 74.553, 'duration': 4.963}, {'end': 83.398, 'text': 'Our question one is total downloads for Sugar Rush app.', 'start': 80.276, 'duration': 3.122}, {'end': 93.385, 'text': 'as you can see here, sugar rush is available every month, so we just need to add up all these values to get the total.', 'start': 84.318, 'duration': 9.067}, {'end': 96.787, 'text': 'this is where the sum ifs formula is perfect.', 'start': 93.385, 'duration': 3.402}], 'summary': 'Data available for 7 apps monthly from june to dec 2021, including downloads, uninstalls, 5-star, 1-star ratings, and total ratings.', 'duration': 45.425, 'max_score': 51.362, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/B5hayFelHDU/pics/B5hayFelHDU51362.jpg'}, {'end': 227.446, 'src': 'embed', 'start': 197.788, 'weight': 4, 'content': [{'end': 202.951, 'text': 'Our second business question is how many total downloads are there for C apps?', 'start': 197.788, 'duration': 5.163}, {'end': 209.113, 'text': 'This is an internal name for all the apps whose name starts with the letter C.', 'start': 203.611, 'duration': 5.502}, {'end': 214.455, 'text': "To do this, I'm going to define an input cell starting with and then type C there.", 'start': 209.113, 'duration': 5.342}, {'end': 217.217, 'text': 'And then we can use the SUMIFS formula like this.', 'start': 215.176, 'duration': 2.041}, {'end': 227.446, 'text': 'The sum range needs to be download column, so you can select this and then the criteria range is app name.', 'start': 219.52, 'duration': 7.926}], 'summary': 'Total downloads for apps starting with c using sumifs formula.', 'duration': 29.658, 'max_score': 197.788, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/B5hayFelHDU/pics/B5hayFelHDU197788.jpg'}], 'start': 2.455, 'title': 'Utilizing excel for business questions', 'summary': "Focuses on answering 15 business questions related to awesome chocolates' app download and installation data from june 2021 to december 2021 using excel. it also covers the usage of the sumifs formula to calculate the total downloads for specific apps, with examples and techniques demonstrated for sugar rush and c apps.", 'chapters': [{'end': 73.893, 'start': 2.455, 'title': 'Excel for business questions', 'summary': "Focuses on using excel to answer 15 business questions related to awesome chocolates' app download and installation data from june 2021 to december 2021.", 'duration': 71.438, 'highlights': ['Showcasing how to use Excel formulas to answer 15 data analysis questions related to app download and installation data.', 'Introducing powerful and useful Excel functions for data analysis in the context of answering business questions.', 'Providing a data set with app download and installation information for seven apps for each month from June 2021 to December 2021, including details on downloads, uninstalls, ratings, and total ratings.']}, {'end': 296.932, 'start': 74.553, 'title': 'Using sumifs formula for app downloads', 'summary': 'Covers the usage of the sumifs formula to calculate the total downloads for specific apps, with examples and techniques demonstrated for sugar rush and c apps, showcasing the flexibility and efficiency of the formula in handling various business questions.', 'duration': 222.379, 'highlights': ["The chapter focuses on demonstrating the application of the SUMIFS formula to calculate the total downloads for specific apps, illustrating the process with examples for Sugar Rush and C apps, highlighting the formula's adaptability to handle different business questions.", "The use of the SUMIFS formula is exemplified by calculating the total downloads for the Sugar Rush app, showcasing the process of specifying the numbers to be added up in the criteria, linking the input cell for flexibility, and demonstrating the formula's adaptability to different app names, reinforcing its efficiency and versatility in data analysis.", "An example of using the SUMIFS formula to calculate the total downloads for C apps is provided, demonstrating the process of defining an input cell for the specific app name criteria, utilizing the 'star' special character in the formula to represent any number of letters following the input letter, and showcasing the method of linking the input cell to enhance flexibility in analyzing different app categories."]}], 'duration': 294.477, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/B5hayFelHDU/pics/B5hayFelHDU2455.jpg', 'highlights': ['Introducing powerful and useful Excel functions for data analysis in the context of answering business questions.', 'Showcasing how to use Excel formulas to answer 15 data analysis questions related to app download and installation data.', 'Providing a data set with app download and installation information for seven apps for each month from June 2021 to December 2021, including details on downloads, uninstalls, ratings, and total ratings.', "The chapter focuses on demonstrating the application of the SUMIFS formula to calculate the total downloads for specific apps, illustrating the process with examples for Sugar Rush and C apps, highlighting the formula's adaptability to handle different business questions.", "An example of using the SUMIFS formula to calculate the total downloads for C apps is provided, demonstrating the process of defining an input cell for the specific app name criteria, utilizing the 'star' special character in the formula to represent any number of letters following the input letter, and showcasing the method of linking the input cell to enhance flexibility in analyzing different app categories.", "The use of the SUMIFS formula is exemplified by calculating the total downloads for the Sugar Rush app, showcasing the process of specifying the numbers to be added up in the criteria, linking the input cell for flexibility, and demonstrating the formula's adaptability to different app names, reinforcing its efficiency and versatility in data analysis."]}, {'end': 648.517, 'segs': [{'end': 371.997, 'src': 'embed', 'start': 343.504, 'weight': 2, 'content': [{'end': 348.387, 'text': "So we'll say month and then type December 2021.", 'start': 343.504, 'duration': 4.883}, {'end': 352.869, 'text': 'And here I want to see how many uninstall happen.', 'start': 348.387, 'duration': 4.482}, {'end': 355.765, 'text': 'To do this, we will just say SUMIFS.', 'start': 353.803, 'duration': 1.962}, {'end': 360.248, 'text': 'Instead of selecting the column, you can also type these columns yourself.', 'start': 356.585, 'duration': 3.663}, {'end': 363.01, 'text': 'So you will simply say data table.', 'start': 360.328, 'duration': 2.682}, {'end': 371.997, 'text': 'open the square bracket and then, using the arrow keys, select the uninstall and then press the tab key to add that to the formula.', 'start': 363.01, 'duration': 8.987}], 'summary': 'In december 2021, we will use sumifs to count uninstalls from a data table.', 'duration': 28.493, 'max_score': 343.504, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/B5hayFelHDU/pics/B5hayFelHDU343504.jpg'}, {'end': 416.346, 'src': 'heatmap', 'start': 386.82, 'weight': 0.801, 'content': [{'end': 389.862, 'text': 'So we can directly link it to the cell like that.', 'start': 386.82, 'duration': 3.042}, {'end': 395.687, 'text': 'Or alternatively, you can also generate a date that is equal to December 2021.', 'start': 390.683, 'duration': 5.004}, {'end': 399.85, 'text': 'To do this, you can use the date formula 2021 12th month first date.', 'start': 395.687, 'duration': 4.163}, {'end': 407.733, 'text': 'And you will get the answer here as 783.', 'start': 403.567, 'duration': 4.166}, {'end': 409.496, 'text': "Let's quickly double check this total.", 'start': 407.733, 'duration': 1.763}, {'end': 416.346, 'text': 'So if I go all the way down here to my December and then select all of these, my sum here is 783.', 'start': 410.036, 'duration': 6.31}], 'summary': 'Using a date formula to generate december 2021 results in a sum of 783, confirmed by double checking.', 'duration': 29.526, 'max_score': 386.82, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/B5hayFelHDU/pics/B5hayFelHDU386820.jpg'}, {'end': 460.812, 'src': 'embed', 'start': 430.869, 'weight': 0, 'content': [{'end': 438.033, 'text': 'This time we would like to see how many downloads happened in the months of October, November or December 2021.', 'start': 430.869, 'duration': 7.164}, {'end': 439.654, 'text': 'So that entire three month window.', 'start': 438.033, 'duration': 1.621}, {'end': 442.476, 'text': 'Again, we can use the SUMIFS formula for this.', 'start': 440.234, 'duration': 2.242}, {'end': 446.4, 'text': "For this purpose, I'm going to define two input variables.", 'start': 442.877, 'duration': 3.523}, {'end': 451.444, 'text': 'One is my starting point of time and other is my ending point of time.', 'start': 447.02, 'duration': 4.424}, {'end': 460.812, 'text': 'So once you have defined these two values, we would now like to see all the download total wherever the date is between these two boundaries.', 'start': 452.085, 'duration': 8.727}], 'summary': 'Analyze downloads for october-december 2021 using sumifs formula.', 'duration': 29.943, 'max_score': 430.869, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/B5hayFelHDU/pics/B5hayFelHDU430869.jpg'}, {'end': 549.68, 'src': 'heatmap', 'start': 525.461, 'weight': 0.786, 'content': [{'end': 531.743, 'text': "So let's go and add the second condition, which is the date also needs to be before December.", 'start': 525.461, 'duration': 6.282}, {'end': 534.245, 'text': '31st of December 2021.', 'start': 532.444, 'duration': 1.801}, {'end': 543.874, 'text': 'so the second condition again, we will add data month, and this is less than or equal to ampersand this date.', 'start': 534.245, 'duration': 9.629}, {'end': 548.098, 'text': "so we have specified both boundaries and we'll get the result.", 'start': 543.874, 'duration': 4.224}, {'end': 549.68, 'text': 'of course, there is no change here.', 'start': 548.098, 'duration': 1.582}], 'summary': 'Add second condition: date before 31st december 2021.', 'duration': 24.219, 'max_score': 525.461, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/B5hayFelHDU/pics/B5hayFelHDU525461.jpg'}, {'end': 590.122, 'src': 'embed', 'start': 562.92, 'weight': 1, 'content': [{'end': 570.689, 'text': 'And we will get total downloads in the first two months since we launched our awesome apps.', 'start': 562.92, 'duration': 7.769}, {'end': 575.118, 'text': "Let's understand how to use the count ifs formula with this question.", 'start': 571.297, 'duration': 3.821}, {'end': 580.619, 'text': 'How many times we had more than 8, 000 downloads per app month?', 'start': 575.658, 'duration': 4.961}, {'end': 583.32, 'text': 'So that means on the download column.', 'start': 581.279, 'duration': 2.041}, {'end': 590.122, 'text': 'I just want to count all the occurrences when we had more than 8, 000 downloads within a month.', 'start': 583.32, 'duration': 6.802}], 'summary': 'Count occurrences of more than 8,000 downloads per app month.', 'duration': 27.202, 'max_score': 562.92, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/B5hayFelHDU/pics/B5hayFelHDU562920.jpg'}, {'end': 648.517, 'src': 'heatmap', 'start': 601.461, 'weight': 0.836, 'content': [{'end': 603.622, 'text': 'And then simply say what is the criteria.', 'start': 601.461, 'duration': 2.161}, {'end': 605.962, 'text': 'The criteria needs to be more than 8000.', 'start': 603.942, 'duration': 2.02}, {'end': 607.883, 'text': "So we'll say greater than 8000.", 'start': 605.962, 'duration': 1.921}, {'end': 610.744, 'text': 'And then close bracket.', 'start': 607.883, 'duration': 2.861}, {'end': 616.825, 'text': 'So you can see that there have been five times when we had more than 8000 downloads per month.', 'start': 611.624, 'duration': 5.201}, {'end': 619.306, 'text': "Let's quickly double check this.", 'start': 617.986, 'duration': 1.32}, {'end': 622.147, 'text': "So I'm going to go to the download column here.", 'start': 619.926, 'duration': 2.221}, {'end': 628.519, 'text': 'Number filters greater than 8000.', 'start': 622.367, 'duration': 6.152}, {'end': 630.1, 'text': 'and these are the five occurrences.', 'start': 628.519, 'duration': 1.581}, {'end': 636.306, 'text': 'You might feel a bit confused by when you would use the greater than or less than within the double quotes.', 'start': 630.421, 'duration': 5.885}, {'end': 644.733, 'text': "Every time you're doing a comparison of greater than less than before or after the operator, that is,", 'start': 637.187, 'duration': 7.546}, {'end': 648.517, 'text': 'the greater than or less than needs to be within the double quotes.', 'start': 644.733, 'duration': 3.784}], 'summary': 'There were five months with over 8000 downloads.', 'duration': 47.056, 'max_score': 601.461, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/B5hayFelHDU/pics/B5hayFelHDU601461.jpg'}], 'start': 296.932, 'title': 'Excel functions for data analysis', 'summary': 'Covers the use of excel functions such as sumifs, countifs, and average.ifs to analyze data, including finding the number of uninstalls in december 2021, total downloads from october to december 2021, and occurrences of more than 8,000 downloads per month.', 'chapters': [{'end': 648.517, 'start': 296.932, 'title': 'Excel functions for data analysis', 'summary': 'Covers the use of excel functions such as sumifs, countifs, and average.ifs to analyze data, including finding the number of uninstalls in december 2021, total downloads from october to december 2021, and occurrences of more than 8,000 downloads per month.', 'duration': 351.585, 'highlights': ['Using SUMIFS to find uninstalls in December 2021 The speaker demonstrates using the SUMIFS function to find the number of uninstalls in December 2021, resulting in a total of 783 uninstalls.', 'Using SUMIFS to find total downloads from October to December 2021 The explanation of using SUMIFS to find the total downloads from October to December 2021, showcasing the importance and usefulness of this technique in various business situations.', 'Using COUNTIFS to count occurrences of more than 8,000 downloads per month The demonstration of using COUNTIFS to count occurrences of more than 8,000 downloads per month, revealing a total of five occurrences.']}], 'duration': 351.585, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/B5hayFelHDU/pics/B5hayFelHDU296932.jpg', 'highlights': ['Using SUMIFS to find total downloads from October to December 2021, showcasing the importance and usefulness of this technique in various business situations.', 'Using COUNTIFS to count occurrences of more than 8,000 downloads per month, revealing a total of five occurrences.', 'Using SUMIFS to find uninstalls in December 2021, resulting in a total of 783 uninstalls.']}, {'end': 924.186, 'segs': [{'end': 687.432, 'src': 'embed', 'start': 649.137, 'weight': 1, 'content': [{'end': 655.283, 'text': "So that is the key rule to remember when you're working with the SUMIFS, COUNTIFS or AVERAGEIF functions.", 'start': 649.137, 'duration': 6.146}, {'end': 660.383, 'text': 'Now that we have some understanding of both SUMIFS and COUNTIFS functions,', 'start': 656.098, 'duration': 4.285}, {'end': 666.029, 'text': "let's go and answer a question that is not directly answerable by a single formula alone.", 'start': 660.383, 'duration': 5.646}, {'end': 667.931, 'text': 'In this question.', 'start': 667.13, 'duration': 0.801}, {'end': 672.716, 'text': 'I want to know what is the five star rating percentage for Sugar Rush app?', 'start': 667.931, 'duration': 4.785}, {'end': 687.432, 'text': 'So here the normal way of answering this question is I want to see how many five star ratings Sugar Rush had and then how many total ratings it had,', 'start': 673.977, 'duration': 13.455}], 'summary': 'Understand sumifs, countifs, averageif. find 5-star % for sugar rush app', 'duration': 38.295, 'max_score': 649.137, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/B5hayFelHDU/pics/B5hayFelHDU649137.jpg'}, {'end': 794.892, 'src': 'heatmap', 'start': 701.398, 'weight': 0, 'content': [{'end': 714.281, 'text': 'so this number is a sum ifs formula, sum ifs of five star ratings where app is sugar rush.', 'start': 701.398, 'duration': 12.883}, {'end': 719.182, 'text': 'so it has been rated five star 587 times.', 'start': 714.281, 'duration': 4.901}, {'end': 723.563, 'text': 'the next is sum ifs total ratings.', 'start': 719.182, 'duration': 4.381}, {'end': 731.344, 'text': 'app is sugar rush And it had received total of 5, 353 ratings.', 'start': 723.563, 'duration': 7.781}, {'end': 737.986, 'text': 'So five star percentage is equal to this divided by that.', 'start': 732.144, 'duration': 5.842}, {'end': 740.246, 'text': "We'll get this as a fraction.", 'start': 738.846, 'duration': 1.4}, {'end': 745.688, 'text': 'If you apply percentage formatting, you can see that this has happened 11% of the time.', 'start': 740.806, 'duration': 4.882}, {'end': 747.408, 'text': 'Pretty impressive.', 'start': 746.528, 'duration': 0.88}, {'end': 749.729, 'text': "Let's see how this is for temple bun.", 'start': 748.148, 'duration': 1.581}, {'end': 754.975, 'text': '12%. so far we have only done sums and counts.', 'start': 749.729, 'duration': 5.246}, {'end': 758.599, 'text': "let's see how to do this kind of analysis with average.", 'start': 754.975, 'duration': 3.624}, {'end': 764.585, 'text': 'here i have a question of what is the average uninstalls for subway wafers.', 'start': 758.599, 'duration': 5.986}, {'end': 773.414, 'text': 'so our app is subway wafers and average uninstalls can be calculated using the average ifs function.', 'start': 764.585, 'duration': 8.829}, {'end': 777.318, 'text': 'and here average range is on the uninstall column.', 'start': 774.295, 'duration': 3.023}, {'end': 784.584, 'text': "so we'll select uninstall and then criteria is my app column and this is the criteria.", 'start': 777.318, 'duration': 7.266}, {'end': 789.868, 'text': 'so on average subway wafers gets about 100 uninstalls every month.', 'start': 784.584, 'duration': 5.284}, {'end': 792.17, 'text': "now let's take this up to the next level.", 'start': 789.868, 'duration': 2.302}, {'end': 794.892, 'text': 'which month has the highest uninstall ratio?', 'start': 792.17, 'duration': 2.722}], 'summary': 'An analysis of app ratings and uninstalls shows sugar rush has a 11% five-star rating and subway wafers gets about 100 uninstalls per month.', 'duration': 93.494, 'max_score': 701.398, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/B5hayFelHDU/pics/B5hayFelHDU701398.jpg'}, {'end': 934.345, 'src': 'embed', 'start': 899.203, 'weight': 2, 'content': [{'end': 901.825, 'text': 'this is because the data is randomly made up.', 'start': 899.203, 'duration': 2.622}, {'end': 905.869, 'text': 'so now, out of all of this, we just want to calculate the maximum.', 'start': 901.825, 'duration': 4.044}, {'end': 908.11, 'text': 'so maximum of these values is.', 'start': 905.869, 'duration': 2.241}, {'end': 915.337, 'text': 'The question is not about this value, but it is about which month has the highest uninstall ratio.', 'start': 909.051, 'duration': 6.286}, {'end': 924.186, 'text': 'So this is my highest uninstall ratio and as you could see it happened in the month of July but we need really the answer as July.', 'start': 915.918, 'duration': 8.268}, {'end': 934.345, 'text': 'and to do that we need to use the lookup category of functions in excel, and here you can either use xlookup or index match.', 'start': 924.72, 'duration': 9.625}], 'summary': 'July has the highest uninstall ratio, calculated using excel lookup functions.', 'duration': 35.142, 'max_score': 899.203, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/B5hayFelHDU/pics/B5hayFelHDU899203.jpg'}], 'start': 649.137, 'title': 'App analytics', 'summary': 'Covers the application of sumifs and countifs functions to calculate the five-star rating percentage for the sugar rush app, which received 587 five-star ratings out of 5,353 total ratings, resulting in a 10.98% five-star rating. it also includes the analysis of app uninstall rates, average calculation, and identification of the month with the highest uninstall ratio, with an example showing an app experiencing an average of 100 uninstalls per month and the highest uninstall ratio occurring in july 2021.', 'chapters': [{'end': 740.246, 'start': 649.137, 'title': 'Understanding sumifs and countifs functions', 'summary': 'Covers the application of sumifs and countifs functions in calculating the five-star rating percentage for the sugar rush app, where it received 587 five-star ratings out of 5,353 total ratings, thus having a 10.98% five-star rating.', 'duration': 91.109, 'highlights': ['The application of SUMIFS and COUNTIFS functions is explained, focusing on calculating the five-star rating percentage for the Sugar Rush app, which received 587 five-star ratings out of 5,353 total ratings.', 'The specific quantifiable data of 587 five-star ratings out of 5,353 total ratings is highlighted as the basis for calculating the five-star rating percentage.']}, {'end': 924.186, 'start': 740.806, 'title': 'App uninstall analysis', 'summary': 'Covers the application of percentage formatting, average calculation for uninstall rates, and identifying the month with the highest uninstall ratio, with an example showing an app experiencing an average of 100 uninstalls per month and the highest uninstall ratio occurring in july 2021.', 'duration': 183.38, 'highlights': ["The app 'subway wafers' experiences an average of 100 uninstalls every month. This demonstrates the calculation of the average uninstalls for the app, providing a quantifiable data point.", 'The month with the highest uninstall ratio is identified as July, based on the provided data. This showcases the process of calculating uninstall ratios for different months and determining the month with the highest uninstall ratio, providing a key insight.', 'The process of using percentage formatting reveals that the event occurred 11% of the time. This highlights the application of percentage formatting to quantify the frequency of a specific event, providing a tangible data point.']}], 'duration': 275.049, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/B5hayFelHDU/pics/B5hayFelHDU649137.jpg', 'highlights': ['The specific quantifiable data of 587 five-star ratings out of 5,353 total ratings is highlighted as the basis for calculating the five-star rating percentage.', 'The application of SUMIFS and COUNTIFS functions is explained, focusing on calculating the five-star rating percentage for the Sugar Rush app, which received 587 five-star ratings out of 5,353 total ratings.', 'The month with the highest uninstall ratio is identified as July, based on the provided data. This showcases the process of calculating uninstall ratios for different months and determining the month with the highest uninstall ratio, providing a key insight.', 'The process of using percentage formatting reveals that the event occurred 11% of the time. This highlights the application of percentage formatting to quantify the frequency of a specific event, providing a tangible data point.', "The app 'subway wafers' experiences an average of 100 uninstalls every month. This demonstrates the calculation of the average uninstalls for the app, providing a quantifiable data point."]}, {'end': 1319.645, 'segs': [{'end': 956.856, 'src': 'heatmap', 'start': 924.72, 'weight': 0, 'content': [{'end': 934.345, 'text': 'and to do that we need to use the lookup category of functions in excel, and here you can either use xlookup or index match.', 'start': 924.72, 'duration': 9.625}, {'end': 936.886, 'text': 'i prefer using xlookup because it is easy.', 'start': 934.345, 'duration': 2.541}, {'end': 945.411, 'text': 'so this is the value that i want to look up in this column and then get the corresponding month name.', 'start': 936.886, 'duration': 8.525}, {'end': 949.033, 'text': 'so essentially, what excel will do is it will take this number.', 'start': 945.411, 'duration': 3.622}, {'end': 951.434, 'text': 'it will look up in this particular column.', 'start': 949.033, 'duration': 2.401}, {'end': 956.856, 'text': 'Wherever it finds the value, it will go across the screen, get the corresponding month name.', 'start': 952.254, 'duration': 4.602}], 'summary': 'Using xlookup to find month name in excel.', 'duration': 26.714, 'max_score': 924.72, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/B5hayFelHDU/pics/B5hayFelHDU924720.jpg'}, {'end': 1025.709, 'src': 'embed', 'start': 997.074, 'weight': 1, 'content': [{'end': 999.836, 'text': 'we can use the average ifs function for this card.', 'start': 997.074, 'duration': 2.762}, {'end': 1006.08, 'text': 'this question so average ifs of my downloads where my month is here.', 'start': 999.836, 'duration': 6.244}, {'end': 1010.762, 'text': 'So in the month of June we had 4971 on average.', 'start': 1006.5, 'duration': 4.262}, {'end': 1015.164, 'text': 'And then as you fill down, you can see that the values have kind of fluctuated quite a bit.', 'start': 1011.302, 'duration': 3.862}, {'end': 1025.709, 'text': 'And again, you can change this to a whole number or a number with some two decimal points to quickly see just the amount required amount of precision.', 'start': 1015.944, 'duration': 9.765}], 'summary': 'Using average ifs function, the average downloads in june was 4971, with fluctuating values.', 'duration': 28.635, 'max_score': 997.074, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/B5hayFelHDU/pics/B5hayFelHDU997074.jpg'}, {'end': 1065.411, 'src': 'embed', 'start': 1035.474, 'weight': 4, 'content': [{'end': 1046.146, 'text': 'So here we have a question about what is the most downloads in any month for any app and which downloads is taking up the second and third place.', 'start': 1035.474, 'duration': 10.672}, {'end': 1050.953, 'text': 'We can answer the most downloads question using the max formula.', 'start': 1046.807, 'duration': 4.146}, {'end': 1056, 'text': 'So maximum of the downloads column is 10228.', 'start': 1051.294, 'duration': 4.706}, {'end': 1062.63, 'text': 'The second highest value and the third highest value are not possible with the max formula.', 'start': 1056, 'duration': 6.63}, {'end': 1065.411, 'text': 'For this you need to use the large formula.', 'start': 1063.11, 'duration': 2.301}], 'summary': 'The highest downloads in any month for an app is 10228, and the second and third highest values are found using the large formula.', 'duration': 29.937, 'max_score': 1035.474, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/B5hayFelHDU/pics/B5hayFelHDU1035473.jpg'}, {'end': 1136.68, 'src': 'heatmap', 'start': 1110.586, 'weight': 0.739, 'content': [{'end': 1115.808, 'text': 'So XLOOKUP, we want to look up when that many downloads happened.', 'start': 1110.586, 'duration': 5.222}, {'end': 1122.236, 'text': 'In the download column and then get the corresponding app name.', 'start': 1117.308, 'duration': 4.928}, {'end': 1127.364, 'text': 'Essentially we are looking for chocolate crossing because that is the one that had 10, 028 downloads.', 'start': 1123.358, 'duration': 4.006}, {'end': 1136.68, 'text': "we'll get that answer here, and if we just drag this down, i'll get my other ones as well.", 'start': 1130.117, 'duration': 6.563}], 'summary': 'Using xlookup, found 10,028 downloads for chocolate crossing app.', 'duration': 26.094, 'max_score': 1110.586, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/B5hayFelHDU/pics/B5hayFelHDU1110586.jpg'}, {'end': 1191.008, 'src': 'heatmap', 'start': 1140.581, 'weight': 0.922, 'content': [{'end': 1150.225, 'text': 'this is simply because xlookup is only available in excel 365, but many of you might not have access to that and using a older version of excel,', 'start': 1140.581, 'duration': 9.644}, {'end': 1153.546, 'text': 'so index match would work in pretty much any version of excel.', 'start': 1150.225, 'duration': 3.321}, {'end': 1161.27, 'text': "The way this works is you'll start by saying index and then select the month column, because that's the answer that we want here.", 'start': 1153.866, 'duration': 7.404}, {'end': 1164.072, 'text': 'We need to know the month and then say match.', 'start': 1161.35, 'duration': 2.722}, {'end': 1170.335, 'text': 'Match this number in the downloads column and then the last parameter is 0.', 'start': 1164.812, 'duration': 5.523}, {'end': 1172.076, 'text': 'This is because we want an exact match.', 'start': 1170.335, 'duration': 1.741}, {'end': 1174.638, 'text': "So that's the pattern that you will use.", 'start': 1172.636, 'duration': 2.002}, {'end': 1180.701, 'text': 'Start by saying which column data you want and then specify the match.', 'start': 1175.478, 'duration': 5.223}, {'end': 1182.242, 'text': "We don't get the month.", 'start': 1181.301, 'duration': 0.941}, {'end': 1191.008, 'text': 'we get the number value of that month, but once the values are there, you can use the date formatting to see the underlying month.', 'start': 1182.242, 'duration': 8.766}], 'summary': 'Use index match instead of xlookup for compatibility, specifying month data and match for exact value.', 'duration': 50.427, 'max_score': 1140.581, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/B5hayFelHDU/pics/B5hayFelHDU1140581.jpg'}, {'end': 1218.517, 'src': 'embed', 'start': 1191.829, 'weight': 2, 'content': [{'end': 1196.45, 'text': 'We had most downloads in the month of August 2021.', 'start': 1191.829, 'duration': 4.621}, {'end': 1197.971, 'text': 'And the app is Chocolate Crossing.', 'start': 1196.45, 'duration': 1.521}, {'end': 1199.891, 'text': 'Second highest in November.', 'start': 1198.511, 'duration': 1.38}, {'end': 1201.072, 'text': 'Third highest in July.', 'start': 1199.951, 'duration': 1.121}, {'end': 1208.054, 'text': 'Our next question is in the month of October 2021, which app has most one star ratings?', 'start': 1201.712, 'duration': 6.342}, {'end': 1213.936, 'text': 'So if I just want to get the highest one star ratings ever, we could use the max formula.', 'start': 1208.494, 'duration': 5.442}, {'end': 1215.936, 'text': 'So max of one star ratings.', 'start': 1213.976, 'duration': 1.96}, {'end': 1218.517, 'text': "I'll get the answer as 60.", 'start': 1216.937, 'duration': 1.58}], 'summary': 'In august 2021, chocolate crossing had the most downloads, reaching 60 one-star ratings in october 2021.', 'duration': 26.688, 'max_score': 1191.829, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/B5hayFelHDU/pics/B5hayFelHDU1191829.jpg'}, {'end': 1278.073, 'src': 'heatmap', 'start': 1255.154, 'weight': 0.839, 'content': [{'end': 1264.1, 'text': "So maxifs one star ratings column because that's where I want my maximum and then the month column needs to be October.", 'start': 1255.154, 'duration': 8.946}, {'end': 1273.289, 'text': 'so here i will get the answer as 47, which is the highest number out of all these values.', 'start': 1264.64, 'duration': 8.649}, {'end': 1274.27, 'text': "but that's not the question.", 'start': 1273.289, 'duration': 0.981}, {'end': 1278.073, 'text': 'the question is which app has most one star ratings?', 'start': 1274.27, 'duration': 3.803}], 'summary': 'The app with the most one-star ratings in october is the desired information.', 'duration': 22.919, 'max_score': 1255.154, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/B5hayFelHDU/pics/B5hayFelHDU1255154.jpg'}], 'start': 924.72, 'title': 'Excel functions and formulas for data analysis', 'summary': 'Demonstrates the usage of xlookup, index match, sumifs, and other ifs functions to retrieve and analyze data, including finding the average monthly downloads, most downloads in a month, and app with the most one star ratings in a specific month.', 'chapters': [{'end': 1015.164, 'start': 924.72, 'title': 'Using excel functions for data analysis', 'summary': 'Demonstrates the use of xlookup and index match functions to retrieve values from a column, highlights the importance of sumifs and other ifs functions for data analysis, and showcases the application of average ifs function to calculate average monthly downloads.', 'duration': 90.444, 'highlights': ['Using xlookup or index match functions to retrieve values from a column and get the corresponding month name, which allows for efficient data analysis and visualization.', 'The importance of SUMIFS and other IFS functions in data analysis situations, emphasizing the need for calculating numerous numbers and working with SUMIFS formula in different ways.', 'Application of average ifs function to calculate average monthly downloads, demonstrating the fluctuation of values over different months.']}, {'end': 1319.645, 'start': 1015.944, 'title': 'Excel formulas for data analysis', 'summary': 'Covers the usage of sumifs, countifs, lookup, max, large, xlookup, indexmatch, and maxifs formulas to analyze data, such as finding the most downloads in a month, the second and third highest downloads, and the app with the most one star ratings in a specific month.', 'duration': 303.701, 'highlights': ['The maximum number of downloads in any month for any app is 10228, found using the max formula. The max formula is used to determine the maximum number of downloads in any month, resulting in 10228.', 'The app with the second highest number of downloads is Chocolate Crossing with 9195 downloads, determined using the large formula. The large formula is utilized to identify the app with the second highest number of downloads, which is Chocolate Crossing with 9195 downloads.', 'The app with the third highest number of downloads is Chocolate Crossing with 8586 downloads, obtained using the large formula. The large formula is employed to ascertain the app with the third highest number of downloads, which is Chocolate Crossing with 8586 downloads.', 'The month with the most downloads is August 2021, and the app is Chocolate Crossing, determined using XLOOKUP and INDEXMATCH formulas. By using XLOOKUP and INDEXMATCH formulas, it is found that the month with the most downloads is August 2021, with the app being Chocolate Crossing.', 'In October 2021, the app with the most one star ratings is Sugar Rush, with 47 ratings, identified using the maxifs and XLOOKUP formulas. The maxifs and XLOOKUP formulas are employed to ascertain that in October 2021, the app with the most one star ratings is Sugar Rush, with 47 ratings.']}], 'duration': 394.925, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/B5hayFelHDU/pics/B5hayFelHDU924720.jpg', 'highlights': ['Using xlookup or index match functions to retrieve values from a column and get the corresponding month name, allowing for efficient data analysis and visualization.', 'Application of average ifs function to calculate average monthly downloads, demonstrating the fluctuation of values over different months.', 'The month with the most downloads is August 2021, and the app is Chocolate Crossing, determined using XLOOKUP and INDEXMATCH formulas.', 'In October 2021, the app with the most one star ratings is Sugar Rush, with 47 ratings, identified using the maxifs and XLOOKUP formulas.', 'The maximum number of downloads in any month for any app is 10228, found using the max formula.']}, {'end': 1753.779, 'segs': [{'end': 1377.687, 'src': 'embed', 'start': 1346.834, 'weight': 0, 'content': [{'end': 1350.695, 'text': 'So this is where ideally I would use a filter function.', 'start': 1346.834, 'duration': 3.861}, {'end': 1352.396, 'text': "We'll start by saying filter.", 'start': 1350.935, 'duration': 1.461}, {'end': 1354.977, 'text': 'The value that we want is the app name.', 'start': 1352.956, 'duration': 2.021}, {'end': 1359.319, 'text': "So I'm going to just select the app column by adding the criteria.", 'start': 1354.997, 'duration': 4.322}, {'end': 1361.88, 'text': 'The criteria needs to have two parameters to it.', 'start': 1359.619, 'duration': 2.261}, {'end': 1368.443, 'text': 'So the first parameter is Month is equal to October and then the second parameter.', 'start': 1361.98, 'duration': 6.463}, {'end': 1372.444, 'text': 'to add the second parameter, we need to use the star operator here to multiply.', 'start': 1368.443, 'duration': 4.001}, {'end': 1377.687, 'text': 'Do not confuse this star with what sum if star or count if star will do.', 'start': 1372.965, 'duration': 4.722}], 'summary': 'Using filter function to select app names by setting criteria for the month of october.', 'duration': 30.853, 'max_score': 1346.834, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/B5hayFelHDU/pics/B5hayFelHDU1346834.jpg'}, {'end': 1468.186, 'src': 'heatmap', 'start': 1378.507, 'weight': 1, 'content': [{'end': 1388.331, 'text': 'So data month D9 star and then the second criteria is ratings also one star ratings need to be 47.', 'start': 1378.507, 'duration': 9.824}, {'end': 1398.2, 'text': 'So, essentially, we are asking the question which app had 47 one star ratings in the month of October 2021?', 'start': 1388.331, 'duration': 9.869}, {'end': 1402.583, 'text': 'So when you specify it like this, you will get the answer as Sugar Rush.', 'start': 1398.2, 'duration': 4.383}, {'end': 1406.427, 'text': "One of the good things with filter is it's going to return all matching results.", 'start': 1403.044, 'duration': 3.383}, {'end': 1413.933, 'text': 'As you can see, if there are two apps with 47 one star ratings in October, both of them will be shown on the screen.', 'start': 1406.967, 'duration': 6.966}, {'end': 1415.635, 'text': "Let's go to our next question.", 'start': 1414.514, 'duration': 1.121}, {'end': 1419.64, 'text': 'Our next question is kind of related.', 'start': 1417.498, 'duration': 2.142}, {'end': 1429.308, 'text': 'This question is, does it change? Does the app name change by December 2021? So you might be again tempted to just change the input cell here.', 'start': 1420.22, 'duration': 9.088}, {'end': 1432.931, 'text': "2021 And then you'll get the answer.", 'start': 1429.328, 'duration': 3.603}, {'end': 1438.375, 'text': 'Within December 2021, we had actually even higher one star ratings.', 'start': 1433.051, 'duration': 5.324}, {'end': 1439.136, 'text': 'So 55.', 'start': 1438.395, 'duration': 0.741}, {'end': 1441.337, 'text': 'But the app is Temple Bun.', 'start': 1439.136, 'duration': 2.201}, {'end': 1444.28, 'text': 'So from Sugar Rush, we moved on to Temple Bun.', 'start': 1441.878, 'duration': 2.402}, {'end': 1446.942, 'text': 'So a different app, but we had even higher ratings.', 'start': 1444.3, 'duration': 2.642}, {'end': 1457.284, 'text': 'things. if you want to keep both values, that is, October value and December value, on the screen, then what you can do is you can set it up for October 2021.', 'start': 1447.302, 'duration': 9.982}, {'end': 1465.125, 'text': "you will get the answers you can ctrl c, ctrl v, so you'll get another set of values here.", 'start': 1457.284, 'duration': 7.841}, {'end': 1468.186, 'text': 'because of the nature of your references, everything is relative.', 'start': 1465.125, 'duration': 3.061}], 'summary': 'In october 2021, sugar rush had 47 one-star ratings, but in december 2021, temple bun had 55 one-star ratings.', 'duration': 69.986, 'max_score': 1378.507, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/B5hayFelHDU/pics/B5hayFelHDU1378507.jpg'}, {'end': 1550.959, 'src': 'embed', 'start': 1524.986, 'weight': 3, 'content': [{'end': 1533.253, 'text': "so for crushed by candies, the most downloads ever is 8586, and when you fill this down, you'll get the similar answers here.", 'start': 1524.986, 'duration': 8.267}, {'end': 1544.374, 'text': 'If you want to figure out which month it is, then you can calculate that using the filter or the lookup formula approaches that we discussed earlier.', 'start': 1534.085, 'duration': 10.289}, {'end': 1550.959, 'text': 'Feel free to take that up as a homework challenge and try to figure the answer out yourself.', 'start': 1544.934, 'duration': 6.025}], 'summary': 'Crushed by candies has 8586 downloads; calculate month using filter or lookup.', 'duration': 25.973, 'max_score': 1524.986, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/B5hayFelHDU/pics/B5hayFelHDU1524986.jpg'}, {'end': 1733.373, 'src': 'embed', 'start': 1707.618, 'weight': 5, 'content': [{'end': 1717.802, 'text': 'Number one download the practice workbook and practice by asking your own questions and try to answer them using various formulas that I have shown you,', 'start': 1707.618, 'duration': 10.184}, {'end': 1721.464, 'text': "or the other formulas that I didn't mention but available in Excel.", 'start': 1717.802, 'duration': 3.662}, {'end': 1729.009, 'text': 'and number two is if you want to take your business and data analysis skills to next level,', 'start': 1722.404, 'duration': 6.605}, {'end': 1733.373, 'text': 'i highly recommend checking out my own excel school program.', 'start': 1729.009, 'duration': 4.364}], 'summary': 'Practice using various excel formulas and consider joining the excel school program to advance your data analysis skills.', 'duration': 25.755, 'max_score': 1707.618, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/B5hayFelHDU/pics/B5hayFelHDU1707618.jpg'}, {'end': 1752.855, 'src': 'heatmap', 'start': 1751.69, 'weight': 0.951, 'content': [{'end': 1752.855, 'text': 'Catch you again somewhere else.', 'start': 1751.69, 'duration': 1.165}], 'summary': 'No substantive content to summarize.', 'duration': 1.165, 'max_score': 1751.69, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/B5hayFelHDU/pics/B5hayFelHDU1751690.jpg'}], 'start': 1320.286, 'title': 'Data filtering and excel analysis', 'summary': 'Covers using filter function for data lookup to retrieve app names based on specific criteria and maintaining multiple sets of data. it also explores the most and least downloaded apps in june 2021, with crushed by candies having the most downloads at 8586 and temple bun being the least downloaded app at 1837, utilizing excel formulas for analysis.', 'chapters': [{'end': 1496.124, 'start': 1320.286, 'title': 'Using filter function for data lookup', 'summary': 'Discusses using the filter function to correctly retrieve app names based on specific criteria, such as finding the app with specific ratings in a particular month, and how to maintain multiple sets of data while using relative references.', 'duration': 175.838, 'highlights': ['Using the filter function to retrieve app names based on specific criteria, such as finding the app with specific ratings in a particular month, allows for the retrieval of all matching results, providing greater flexibility than XLOOKUP or index match.', 'Demonstrating how to maintain multiple sets of data while using relative references, allowing for the retrieval of answers for different time periods without distorting the original formulas.', 'Explaining how the filter function returns all matching results, making it possible to display multiple app names with the same rating in a specific month, providing a comprehensive view of the data.']}, {'end': 1753.779, 'start': 1496.124, 'title': 'Excel data analysis', 'summary': 'Explores the best and least downloaded apps, with crushed by candies having the most downloads at 8586, and temple bun being the least downloaded app in the month of june 2021 at 1837, utilizing excel formulas for data analysis.', 'duration': 257.655, 'highlights': ['Crushed by Candies had the most downloads ever at 8586. This is the highest number of downloads for any app mentioned in the transcript, showcasing the popularity of this particular app.', 'Temple Bun was the least downloaded app in the month of June 2021 with 1837 downloads. This provides a specific and quantifiable example of the least downloaded app, demonstrating the practical application of data analysis.', 'Utilizing Excel formulas such as maxifs and minifs for data analysis. This highlights the specific Excel functions used for analyzing the maximum and minimum values within the dataset, demonstrating the practical application of these functions.']}], 'duration': 433.493, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/B5hayFelHDU/pics/B5hayFelHDU1320286.jpg', 'highlights': ['Using the filter function to retrieve app names based on specific criteria provides greater flexibility than XLOOKUP or index match.', 'Demonstrating how to maintain multiple sets of data while using relative references allows for the retrieval of answers for different time periods without distorting the original formulas.', 'Explaining how the filter function returns all matching results makes it possible to display multiple app names with the same rating in a specific month, providing a comprehensive view of the data.', 'Crushed by Candies had the most downloads ever at 8586, showcasing the popularity of this particular app.', 'Temple Bun was the least downloaded app in the month of June 2021 with 1837 downloads, demonstrating the practical application of data analysis.', 'Utilizing Excel formulas such as maxifs and minifs for data analysis demonstrates the practical application of these functions.']}], 'highlights': ['Using the filter function to retrieve app names based on specific criteria provides greater flexibility than XLOOKUP or index match.', 'The month with the most downloads is August 2021, and the app is Chocolate Crossing, determined using XLOOKUP and INDEXMATCH formulas.', 'The specific quantifiable data of 587 five-star ratings out of 5,353 total ratings is highlighted as the basis for calculating the five-star rating percentage.', 'The month with the highest uninstall ratio is identified as July, based on the provided data. This showcases the process of calculating uninstall ratios for different months and determining the month with the highest uninstall ratio, providing a key insight.', 'The application of SUMIFS and COUNTIFS functions is explained, focusing on calculating the five-star rating percentage for the Sugar Rush app, which received 587 five-star ratings out of 5,353 total ratings.', 'Using SUMIFS to find total downloads from October to December 2021, showcasing the importance and usefulness of this technique in various business situations.', "The chapter focuses on demonstrating the application of the SUMIFS formula to calculate the total downloads for specific apps, illustrating the process with examples for Sugar Rush and C apps, highlighting the formula's adaptability to handle different business questions.", 'The maximum number of downloads in any month for any app is 10228, found using the max formula.', 'The process of using percentage formatting reveals that the event occurred 11% of the time. This highlights the application of percentage formatting to quantify the frequency of a specific event, providing a tangible data point.', "The app 'subway wafers' experiences an average of 100 uninstalls every month. This demonstrates the calculation of the average uninstalls for the app, providing a quantifiable data point."]}