title
Business Analytics with Excel | Data Science Tutorial | Simplilearn

description
🔥 Caltech Post Graduate Program In Data Science: https://www.simplilearn.com/post-graduate-program-data-science?utm_campaign=BusinessAnalysis-W3vrMSah3rc&utm_medium=DescriptionFirstFold&utm_source=youtube 🔥IIT Kanpur Professional Certificate Course In Data Science (India Only): https://www.simplilearn.com/iitk-professional-certificate-course-data-science?utm_campaign=BusinessAnalysis-W3vrMSah3rc&utm_medium=DescriptionFirstFold&utm_source=youtube 🔥 Data Science Bootcamp (US Only): https://www.simplilearn.com/post-graduate-program-data-science?utm_campaign=BusinessAnalysis-W3vrMSah3rc&utm_medium=DescriptionFirstFold&utm_source=youtube 🔥Data Scientist Masters Program (Discount Code - YTBE15): https://www.simplilearn.com/big-data-and-analytics/senior-data-scientist-masters-program-training?utm_campaign=BusinessAnalysis-W3vrMSah3rc&utm_medium=DescriptionFirstFold&utm_source=youtube Business Analytics with excel training has been designed to help initiate you to the world of analytics. For this we use the most commonly used analytics tool i.e. Microsoft Excel. The training will equip you with all the concepts and hard skills required to kick start your analytics career. The topics covered in this video are: Introduction 00:00:00 Formatting and Functions 00:05:42 Lookup Functions 00:22:57 Statistical Functions 00:34:02 Who Is A Business Analyst? A business analyst understands the business problems and identifies the solutions that will maximize the organization's value to its stakeholders. A business analyst is an individual who works closely with the technology team to improve the quality of the services being delivered. To become a business analyst you need to ensure the customer needs are correctly interpreted and translated into application. Another key factor in knowing how to become a business analyst is possessing skills like Excel, SQL, and business intelligence skills to deliver data-driven solutions. #Simplilearn #SimplilearnCourses #OnlineTraining #DataScience #Simplilearn #datasciencewithpython #DataScienceTraining #UpskillingSuccessStories #simplilearn ➡️ About Caltech Post Graduate Program In Data Science This Post Graduation in Data Science leverages the superiority of Caltech's academic eminence. The Data Science program covers critical Data Science topics like Python programming, R programming, Machine Learning, Deep Learning, and Data Visualization tools through an interactive learning model with live sessions by global practitioners and practical labs. ✅ Key Features - Simplilearn's JobAssist helps you get noticed by top hiring companies - Caltech PG program in Data Science completion certificate - Earn up to 14 CEUs from Caltech CTME - Masterclasses delivered by distinguished Caltech faculty and IBM experts - Caltech CTME Circle membership - Online convocation by Caltech CTME Program Director - IBM certificates for IBM courses - Access to hackathons and Ask Me Anything sessions from IBM - 25+ hands-on projects from the likes of Amazon, Walmart, Uber, and many more - Seamless access to integrated labs - Capstone projects in 3 domains - Simplilearn’s Career Assistance to help you get noticed by top hiring companies - 8X higher interaction in live online classes by industry experts ✅ Skills Covered - Exploratory Data Analysis - Descriptive Statistics - Inferential Statistics - Model Building and Fine Tuning - Supervised and Unsupervised Learning - Ensemble Learning - Deep Learning - Data Visualization 🔥 Enroll for FREE Business Analytics Course & Get your Completion Certificate: https://www.simplilearn.com/cbap-basics-skillup?utm_campaign=BusinessAnalysis&utm_medium=Description&utm_source=youtube Learn more at: https://www.simplilearn.com/pgp-data-analytics-certification-training-course?utm_campaign=BusinessAnalysis-W3vrMSah3rc&utm_medium=Description&utm_source=youtube 🔥🔥 Interested in Attending Live Classes? Call Us: IN - 18002127688 / US - +18445327688

detail
{'title': 'Business Analytics with Excel | Data Science Tutorial | Simplilearn', 'heatmap': [{'end': 2428.214, 'start': 2395.866, 'weight': 1}], 'summary': "Discusses the significance of business analytics in gaining insights, excel's extensive analytics capabilities, custom and conditional formatting techniques, conditional formatting, functions and formulas, data retrieval functions, and statistical functions usage, emphasizing excel's increasing preference as a business intelligence tool with millions of users.", 'chapters': [{'end': 72.517, 'segs': [{'end': 72.517, 'src': 'embed', 'start': 28.039, 'weight': 0, 'content': [{'end': 36.943, 'text': 'it is quite clear that businesses must understand the changing market and trends in social media to stay ahead of the competition and drive effective business decisions.', 'start': 28.039, 'duration': 8.904}, {'end': 40.554, 'text': 'What can help businesses maintain their competitive edge?', 'start': 37.932, 'duration': 2.622}, {'end': 43.256, 'text': 'The answer lies in business analytics.', 'start': 41.235, 'duration': 2.021}, {'end': 47.84, 'text': 'The term business analytics refers to the skills, technologies,', 'start': 44.097, 'duration': 3.743}, {'end': 55.366, 'text': 'applications and practices that facilitate continuous iterative exploration and investigation of past business performance,', 'start': 47.84, 'duration': 7.526}, {'end': 58.208, 'text': 'customer data and competitive landscape data.', 'start': 55.366, 'duration': 2.842}, {'end': 62.291, 'text': 'The goal is to gain insights and drive business planning.', 'start': 59.309, 'duration': 2.982}, {'end': 69.355, 'text': 'Therefore, business analytics empowers businesses to automate and optimize business processes,', 'start': 63.151, 'duration': 6.204}, {'end': 72.517, 'text': 'thereby enabling them to sustain the competitive advantage.', 'start': 69.355, 'duration': 3.162}], 'summary': 'Business analytics empowers businesses to sustain competitive advantage by automating and optimizing processes.', 'duration': 44.478, 'max_score': 28.039, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/W3vrMSah3rc/pics/W3vrMSah3rc28039.jpg'}], 'start': 9.83, 'title': 'Importance of business analytics', 'summary': 'Discusses the significance of business analytics in gaining insights, driving business planning, and sustaining competitive advantage through iterative exploration of past business performance, customer data, and competitive landscape data.', 'chapters': [{'end': 72.517, 'start': 9.83, 'title': "Importance of business analytics in today's business", 'summary': 'Discusses the significance of business analytics, emphasizing its role in gaining insights, driving business planning, and sustaining competitive advantage through the iterative exploration of past business performance, customer data, and competitive landscape data.', 'duration': 62.687, 'highlights': ['Business analytics empowers businesses to automate and optimize processes, sustaining competitive advantage.', 'The term business analytics refers to the skills, technologies, applications, and practices that facilitate continuous iterative exploration and investigation of past business performance, customer data, and competitive landscape data.', 'Businesses must understand changing market trends in social media to stay ahead of the competition and drive effective business decisions.']}], 'duration': 62.687, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/W3vrMSah3rc/pics/W3vrMSah3rc9830.jpg', 'highlights': ['Business analytics empowers businesses to automate and optimize processes, sustaining competitive advantage.', 'The term business analytics refers to the skills, technologies, applications, and practices that facilitate continuous iterative exploration and investigation of past business performance, customer data, and competitive landscape data.', 'Businesses must understand changing market trends in social media to stay ahead of the competition and drive effective business decisions.']}, {'end': 331.981, 'segs': [{'end': 122.432, 'src': 'embed', 'start': 98.95, 'weight': 0, 'content': [{'end': 105.697, 'text': 'Today, Excel comes with in-memory technology that makes it easily compatible with millions of bytes of data,', 'start': 98.95, 'duration': 6.747}, {'end': 111.602, 'text': 'enabling companies to perform the necessary analysis without being dependent on specialized solutions.', 'start': 105.697, 'duration': 5.905}, {'end': 121.091, 'text': 'Additionally, features such as Power Business Intelligence, including Power Pivot, Power Query, Power View, Power Maps and so on,', 'start': 112.543, 'duration': 8.548}, {'end': 122.432, 'text': 'easily integrate with Excel.', 'start': 121.091, 'duration': 1.341}], 'summary': "Excel's in-memory technology handles millions of bytes, enabling independent analysis. it integrates power bi features seamlessly.", 'duration': 23.482, 'max_score': 98.95, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/W3vrMSah3rc/pics/W3vrMSah3rc98950.jpg'}, {'end': 167.935, 'src': 'embed', 'start': 142.911, 'weight': 2, 'content': [{'end': 150.255, 'text': "it's very clear that Excel is the business intelligence tool of choice and the future of business intelligence workflows,", 'start': 142.911, 'duration': 7.344}, {'end': 155.478, 'text': 'and is being increasingly preferred by analysts, reporting professionals and BI people.', 'start': 150.255, 'duration': 5.223}, {'end': 163.152, 'text': 'Excel provides a grid-like interface of rows and columns, enabling users to organize and format data better.', 'start': 156.989, 'duration': 6.163}, {'end': 167.935, 'text': 'The data can be simple text, numbers, dates, or other supported formats.', 'start': 163.913, 'duration': 4.022}], 'summary': 'Excel is the preferred business intelligence tool, increasingly favored by analysts and reporting professionals, providing a grid-like interface for organizing and formatting various data types.', 'duration': 25.024, 'max_score': 142.911, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/W3vrMSah3rc/pics/W3vrMSah3rc142911.jpg'}, {'end': 263.58, 'src': 'embed', 'start': 238.753, 'weight': 3, 'content': [{'end': 245.938, 'text': "experiment and forecast results with Excel's histograms, pivot tables, statistics and mathematical formulas and charts.", 'start': 238.753, 'duration': 7.185}, {'end': 255.405, 'text': 'After attending this course, you should be able to understand business problems and datasets, perform calculations using statistical techniques,', 'start': 246.979, 'duration': 8.426}, {'end': 263.58, 'text': 'Conduct a variety of statistical analyses, including regressions, covariance, correlation and testing hypothesis.', 'start': 256.305, 'duration': 7.275}], 'summary': 'Learn excel for statistical analysis, including regressions and hypothesis testing.', 'duration': 24.827, 'max_score': 238.753, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/W3vrMSah3rc/pics/W3vrMSah3rc238753.jpg'}], 'start': 73.558, 'title': "Excel's analytics capabilities", 'summary': "Discusses excel's extensive analytics capabilities, its flat learning curve, good reporting functionalities, and in-memory technology. it emphasizes the intuitive analytics features, the integration of power business intelligence with excel, and excel's increasing preference as a business intelligence tool, with millions of users. additionally, it introduces the excel business analytics course covering multivariate statistics, statistical techniques, and dynamic reports, with support available through live virtual classroom training and online query submission.", 'chapters': [{'end': 142.911, 'start': 73.558, 'title': 'Excel for data analytics', 'summary': 'Highlights the extensive analytics capabilities of microsoft excel, including its flat learning curve, good reporting functionalities, and in-memory technology, enabling companies to perform data analysis without being dependent on specialized solutions. it also emphasizes the intuitive analytics features and the integration of power business intelligence with excel.', 'duration': 69.353, 'highlights': ["Excel's in-memory technology enables compatibility with millions of bytes of data, reducing dependency on specialized solutions.", "The intuitive analytics features such as Find, Connect, and Shape provide an easy way to model and analyze a company's data.", 'Power Business Intelligence, including Power Pivot, Power Query, Power View, and Power Maps, easily integrates with Excel, enhancing its analytics capabilities.', 'The sandbox-like Excel environment makes data exploration and deriving results quite easy, enabling powerful data analytics processes with just one click.']}, {'end': 192.283, 'start': 142.911, 'title': 'Excel as the future of business intelligence', 'summary': "Highlights the increasing preference for excel as the business intelligence tool of choice, emphasizing its user-friendly interface and pivotal role in organizations, alongside mentioning amazon's extensive user base of millions of customers.", 'duration': 49.372, 'highlights': ['Excel is the business intelligence tool of choice and the future of business intelligence workflows, increasingly preferred by analysts, reporting professionals, and BI people.', 'Excel provides a grid-like interface of rows and columns, enabling users to organize and format data better, supporting simple text, numbers, dates, and other formats.', "Excel's libraries span a large range of functions, from basic math to complex statistical formulae, making it the leading user-friendly analytical tool.", "Amazon has a user base of millions of customers, showcasing the company's extensive reach and market presence."]}, {'end': 331.981, 'start': 193.324, 'title': 'Excel business analytics course', 'summary': 'Introduces excel business analytics course which covers multivariate statistics, statistical techniques, and dynamic reports, with support available through live virtual classroom training and online query submission.', 'duration': 138.657, 'highlights': ['The course covers how to use Excel to perform multivariate statistics and explore, experiment, and forecast results with histograms, pivot tables, statistics, and mathematical formulas and charts.', 'Attendees should be able to perform calculations using statistical techniques, conduct various statistical analyses, present and interpret results, and design dynamic reports and dashboards for business consumption.', 'The course includes concept videos with exercises, practice and revision assessments, and detailed case studies outlining real-life scenarios.', 'Simply Learn provides complete support during the course and projects, offering live virtual classroom training and online query submission through their website.']}], 'duration': 258.423, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/W3vrMSah3rc/pics/W3vrMSah3rc73558.jpg', 'highlights': ["Excel's in-memory technology reduces dependency on specialized solutions.", 'Power Business Intelligence easily integrates with Excel, enhancing its analytics capabilities.', 'Excel is increasingly preferred by analysts, reporting professionals, and BI people as the future of business intelligence workflows.', 'The course covers how to use Excel to perform multivariate statistics and explore, experiment, and forecast results.']}, {'end': 721.985, 'segs': [{'end': 441.915, 'src': 'embed', 'start': 391.167, 'weight': 0, 'content': [{'end': 400.371, 'text': 'Conditional formatting, a type of formatting, helps us visually explore and analyze data, detect critical issues, and identify patterns and trends.', 'start': 391.167, 'duration': 9.204}, {'end': 408.999, 'text': 'Conditional formatting helps to add patterns and trends to the raw information, using different colors, icons, and formulae, etc.', 'start': 401.211, 'duration': 7.788}, {'end': 416.041, 'text': 'At times, we also need to perform mathematical operations and calculations within our data set.', 'start': 410.86, 'duration': 5.181}, {'end': 428.705, 'text': 'Excel provides a large number of logical, statistical and mathematical functions, such as VLOOKUP, HLOOKUP AND, IF NOT, RANK,', 'start': 417.062, 'duration': 11.643}, {'end': 435.853, 'text': 'QUARTILE and many more that can help the users to perform calculations ranging from basic to complex operations.', 'start': 428.705, 'duration': 7.148}, {'end': 441.915, 'text': 'These inbuilt functions help us to manage data and perform descriptive statistical analysis.', 'start': 436.913, 'duration': 5.002}], 'summary': 'Conditional formatting visually explores data, excel offers various functions for basic to complex calculations.', 'duration': 50.748, 'max_score': 391.167, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/W3vrMSah3rc/pics/W3vrMSah3rc391167.jpg'}, {'end': 506.001, 'src': 'embed', 'start': 463.035, 'weight': 2, 'content': [{'end': 468.157, 'text': 'The worksheets appear more polished and easy to read if proper formatting techniques are applied to it.', 'start': 463.035, 'duration': 5.122}, {'end': 476.821, 'text': 'We can format the cells in Microsoft Excel manually by selecting fonts, font color, font size, background colors, and borders.', 'start': 469.018, 'duration': 7.803}, {'end': 483.444, 'text': 'We can also use numerous predefined table styles or quick styles to quickly format a table automatically.', 'start': 477.181, 'duration': 6.263}, {'end': 485.685, 'text': 'This is the basic formatting in Excel.', 'start': 483.684, 'duration': 2.001}, {'end': 494.656, 'text': 'If these built-in functions do not meet our needs or do not display the data in the format that we require, we can create our own custom format.', 'start': 487.233, 'duration': 7.423}, {'end': 498.257, 'text': 'In this topic, we will focus on custom formatting in Excel.', 'start': 495.056, 'duration': 3.201}, {'end': 503.98, 'text': "Let's say we want to display 1, 532, 000 as 1.53M, or the first of January 2016 date, with a weekday as the first Friday, January 2016.", 'start': 498.398, 'duration': 5.582}, {'end': 506.001, 'text': 'Here, we can use custom formatting feature of Excel.', 'start': 503.98, 'duration': 2.021}], 'summary': 'Proper formatting in excel includes using predefined styles and creating custom formats for better presentation of data.', 'duration': 42.966, 'max_score': 463.035, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/W3vrMSah3rc/pics/W3vrMSah3rc463035.jpg'}, {'end': 572.747, 'src': 'embed', 'start': 540.858, 'weight': 5, 'content': [{'end': 542.96, 'text': 'For example, 7, 164 should be displayed as 7.16k.', 'start': 540.858, 'duration': 2.102}, {'end': 551.134, 'text': 'Select the entire column for Net Sales.', 'start': 548.792, 'duration': 2.342}, {'end': 563.101, 'text': 'Under the Home tab, click the drop-down present in the Numbers panel.', 'start': 559.859, 'duration': 3.242}, {'end': 566.243, 'text': 'Select the option More Number Formats.', 'start': 564.182, 'duration': 2.061}, {'end': 569.065, 'text': 'A Format Sales dialog box appears.', 'start': 566.964, 'duration': 2.101}, {'end': 572.747, 'text': 'From the Category list, select the type as Custom.', 'start': 569.805, 'duration': 2.942}], 'summary': 'Format net sales column as 7.16k using custom number format.', 'duration': 31.889, 'max_score': 540.858, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/W3vrMSah3rc/pics/W3vrMSah3rc540858.jpg'}, {'end': 721.985, 'src': 'embed', 'start': 680.689, 'weight': 6, 'content': [{'end': 683.251, 'text': 'The first 2DD is the date.', 'start': 680.689, 'duration': 2.562}, {'end': 689.116, 'text': 'The next four, DDDD, is for weekday, say Thursday.', 'start': 684.192, 'duration': 4.924}, {'end': 695.429, 'text': 'Then, MMM is for the month in short form, and YY is for year.', 'start': 690.177, 'duration': 5.252}, {'end': 703.734, 'text': 'Here we can see in the preview in the sample box, the value shown is 1, Sunday, Jan, 12.', 'start': 696.53, 'duration': 7.204}, {'end': 705.415, 'text': 'Finally, click on OK button.', 'start': 703.734, 'duration': 1.681}, {'end': 721.985, 'text': 'Thus, the custom formatting helps us in changing the appearance of the cell value according to our needs.', 'start': 716.261, 'duration': 5.724}], 'summary': 'Custom date formatting changes cell appearance, e.g. 1, sunday, jan, 12', 'duration': 41.296, 'max_score': 680.689, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/W3vrMSah3rc/pics/W3vrMSah3rc680689.jpg'}], 'start': 331.981, 'title': 'Excel formatting and custom functions', 'summary': 'Covers custom and conditional formatting techniques in excel, including logical, statistical, and mathematical functions, focusing on sales value differentiation and predefined table styles. it also explains how to apply custom formatting to display values and format dates, with detailed steps and examples.', 'chapters': [{'end': 485.685, 'start': 331.981, 'title': 'Excel formatting and functions', 'summary': 'Covers custom and conditional formatting techniques in excel, including the use of logical, statistical, and mathematical functions, to efficiently analyze and present data, with a focus on differentiation of sales values and the use of predefined table styles.', 'duration': 153.704, 'highlights': ['Excel offers a wide range of logical, statistical, and mathematical functions, such as VLOOKUP, HLOOKUP, IF NOT, RANK, QUARTILE, and more, for performing calculations and descriptive statistical analysis. Excel provides various inbuilt functions like VLOOKUP and IF NOT for performing calculations, enabling users to manage data efficiently.', 'Conditional formatting in Excel helps visually explore and analyze data, detect critical issues, and identify patterns and trends using different colors, icons, and formulae. Conditional formatting in Excel allows for visual exploration and analysis of data, aiding in pattern and trend identification through the use of colors and icons.', 'Proper formatting techniques in Excel, including font selection, color, size, background, and borders, can enhance the appearance and readability of worksheets. Applying proper formatting techniques in Excel, such as font selection and background colors, can improve the appearance and readability of worksheets.']}, {'end': 721.985, 'start': 487.233, 'title': 'Custom formatting in excel', 'summary': 'Explains how to apply custom formatting in excel to display values such as 1.53m for 1,532,000 and format dates like 1st sunday jan 2012, with detailed steps and examples.', 'duration': 234.752, 'highlights': ['Custom formatting in Excel allows displaying values in custom formats, such as 1.53M for 1,532,000, and formatting dates like 1st Sunday Jan 2012. ', 'Steps for applying custom formatting in Excel include selecting the column, accessing More Number Formats under the Home tab, and specifying the custom format in the dialog box. ', "An example of custom format for Net Sales involves entering 'Number, Column, number, number, zero, decimal, zero, zero, comma, open double quote, capital K, close double quote' to display values in thousands. e.g., 7,164 displayed as 7.16k", "For date formatting, the custom format 'DD-DDDD-MMM-YY' is used to display dates like 1st Sunday Jan 2012. "]}], 'duration': 390.004, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/W3vrMSah3rc/pics/W3vrMSah3rc331981.jpg', 'highlights': ['Excel offers logical, statistical, and mathematical functions like VLOOKUP, HLOOKUP, IF NOT, RANK, QUARTILE for calculations and analysis.', 'Conditional formatting in Excel visually explores data, detects issues, and identifies patterns using colors, icons, and formulae.', 'Proper formatting techniques in Excel enhance appearance and readability through font, color, size, and borders.', 'Custom formatting in Excel displays values like 1.53M for 1,532,000 and formats dates like 1st Sunday Jan 2012.', 'Steps for applying custom formatting in Excel include selecting the column and specifying the custom format in the dialog box.', "An example of custom format for Net Sales involves entering 'Number, Column, number, number, zero, decimal, zero, zero, comma, open double quote, capital K, close double quote' to display values in thousands.", "The custom format 'DD-DDDD-MMM-YY' is used to display dates like 1st Sunday Jan 2012."]}, {'end': 1182.646, 'segs': [{'end': 790.275, 'src': 'embed', 'start': 722.97, 'weight': 0, 'content': [{'end': 725.332, 'text': 'A worksheet may contain thousands of rows of data.', 'start': 722.97, 'duration': 2.362}, {'end': 730.135, 'text': 'By simply examining the raw information, it would be difficult to see patterns and trends.', 'start': 725.692, 'duration': 4.443}, {'end': 735.478, 'text': 'Conditional formatting helps us visualize data that make worksheets easier to understand.', 'start': 730.915, 'duration': 4.563}, {'end': 741.202, 'text': 'It quickly highlights important information in a spreadsheet by using colors, icons, and data bars.', 'start': 735.919, 'duration': 5.283}, {'end': 746.826, 'text': 'It changes the appearance of one or more cells when the cell value meets certain criteria.', 'start': 741.883, 'duration': 4.943}, {'end': 750.128, 'text': 'To do this, we need to create a conditional formatting rule.', 'start': 747.206, 'duration': 2.922}, {'end': 757.671, 'text': 'For example, A conditional formatting rule can be, if value is greater than 5, 000, color the cell yellow.', 'start': 750.549, 'duration': 7.122}, {'end': 764.696, 'text': 'By applying this rule, we will be able to quickly see which cells contain values that are greater than 5, 000.', 'start': 758.372, 'duration': 6.324}, {'end': 766.858, 'text': 'Let us understand this with the help of examples.', 'start': 764.696, 'duration': 2.162}, {'end': 772.682, 'text': 'Consider the given sales table, which depicts the amount of sales done by each salesman.', 'start': 768.179, 'duration': 4.503}, {'end': 776.224, 'text': 'Here, we want to highlight the duplicate order ID values.', 'start': 773.302, 'duration': 2.922}, {'end': 779.787, 'text': 'First, select the order ID column.', 'start': 777.505, 'duration': 2.282}, {'end': 785.252, 'text': 'Click Home tab.', 'start': 784.431, 'duration': 0.821}, {'end': 790.275, 'text': 'Under Styles panel, select the option Conditional Formatting.', 'start': 786.092, 'duration': 4.183}], 'summary': 'Conditional formatting visually highlights data, making it easier to understand. it uses colors, icons, and data bars to quickly signify important information, such as values greater than 5,000 or duplicate order ids in a sales table.', 'duration': 67.305, 'max_score': 722.97, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/W3vrMSah3rc/pics/W3vrMSah3rc722970.jpg'}, {'end': 978.759, 'src': 'embed', 'start': 908.661, 'weight': 3, 'content': [{'end': 918.465, 'text': 'To perform conditional formatting, select the desired cells, click on Home tab.', 'start': 908.661, 'duration': 9.804}, {'end': 923.027, 'text': 'Under Styles Panel, select the option Conditional Formatting.', 'start': 919.486, 'duration': 3.541}, {'end': 929.73, 'text': 'From the drop-down menu, hover the mouse over Icon Set Rules and select the first rule.', 'start': 924.348, 'duration': 5.382}, {'end': 945.032, 'text': 'Again, click on Conditional Formatting, select the option Manage Rules from the dropdown, a Conditional Formatting Rules Manager dialog box appears.', 'start': 935.43, 'duration': 9.602}, {'end': 949.713, 'text': 'Go to Icon Set Rule and select Three Icon Set Rule.', 'start': 946.372, 'duration': 3.341}, {'end': 954.934, 'text': 'Click on Edit Rule and an Edit Rule Formatting dialog box appears.', 'start': 951.033, 'duration': 3.901}, {'end': 963.956, 'text': 'By default, for Three Icon Set Rule, Excel uses the 67th percentile and the 33th percentile rule.', 'start': 956.394, 'duration': 7.562}, {'end': 970.177, 'text': 'This means that it shows a green arrow for values greater than 67%,', 'start': 964.636, 'duration': 5.541}, {'end': 978.759, 'text': 'yellow arrows for values between 67 and 33% and red arrow for values less than 33%.', 'start': 970.177, 'duration': 8.582}], 'summary': 'Perform conditional formatting in excel using 67th and 33th percentiles.', 'duration': 70.098, 'max_score': 908.661, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/W3vrMSah3rc/pics/W3vrMSah3rc908661.jpg'}, {'end': 1182.646, 'src': 'embed', 'start': 1156.908, 'weight': 5, 'content': [{'end': 1162.512, 'text': 'Here, on the basis of the sales amount, we want to determine the commission percentage of each salesman.', 'start': 1156.908, 'duration': 5.604}, {'end': 1164.573, 'text': 'Following is the criteria.', 'start': 1163.352, 'duration': 1.221}, {'end': 1169.378, 'text': 'If net sales greater than or equal to 10, 000, set commission as 5%.', 'start': 1165.396, 'duration': 3.982}, {'end': 1177.383, 'text': 'If net sales greater than or equal to 5, 000 and less than or equal to 10, 000, set commission as 2.5%.', 'start': 1169.378, 'duration': 8.005}, {'end': 1182.646, 'text': 'If net sales less than 5, 000, set commission as 1.5%.', 'start': 1177.383, 'duration': 5.263}], 'summary': 'Determine commission percentage based on sales: >=10,000: 5%, >=5,000 and <=10,000: 2.5%, <5,000: 1.5%', 'duration': 25.738, 'max_score': 1156.908, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/W3vrMSah3rc/pics/W3vrMSah3rc1156908.jpg'}], 'start': 722.97, 'title': 'Excel conditional formatting techniques', 'summary': 'Explains how to use conditional formatting in excel to visualize and understand data, including highlighting cells with values greater than 5,000, identifying duplicate values, using data bars, color scales, icon sets, and applying conditional formatting using formulas.', 'chapters': [{'end': 850.489, 'start': 722.97, 'title': 'Conditional formatting in excel', 'summary': 'Explains how conditional formatting in excel helps visualize and understand data by highlighting important information using colors, icons, and data bars. it provides examples and steps to create conditional formatting rules, such as highlighting cells with values greater than 5,000 and identifying duplicate values in a sales table.', 'duration': 127.519, 'highlights': ['Conditional formatting in Excel helps visualize and understand data by highlighting important information using colors, icons, and data bars. It makes worksheets easier to understand by quickly highlighting important information in a spreadsheet using colors, icons, and data bars.', 'Steps to create conditional formatting rules, such as highlighting cells with values greater than 5,000. By creating a conditional formatting rule, for example, if value is greater than 5,000, the cell can be colored yellow, helping to quickly identify cells with values greater than 5,000.', "Identifying duplicate values in a sales table using conditional formatting. By selecting the order ID column, choosing 'Duplicate Values' from the Conditional Formatting menu, and applying a formatting style, such as green fill with dark green text, Excel can highlight all the duplicate cells in the sales table."]}, {'end': 1182.646, 'start': 851.049, 'title': 'Excel conditional formatting techniques', 'summary': 'Explains using data bars, color scales, and icon sets for conditional formatting, customizing icon set rules based on specific criteria, applying conditional formatting using formulas, and determining commission percentage based on sales amount.', 'duration': 331.597, 'highlights': ['Customizing icon set rules based on specific criteria The chapter explains how to customize icon set rules in Excel, setting specific threshold values such as 8000 and 5000 for different colored arrows, with cells being highlighted accordingly.', 'Applying conditional formatting using formulas The transcript details the process of applying conditional formatting using formulas, where cells with profit greater than 4000 are highlighted with a green color, providing a practical example of the application.', 'Determining commission percentage based on sales amount The chapter outlines the criteria for determining commission percentage based on sales amount, where different sales thresholds result in varying commission percentages, providing a clear understanding of the logic behind the calculation.']}], 'duration': 459.676, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/W3vrMSah3rc/pics/W3vrMSah3rc722970.jpg', 'highlights': ['Conditional formatting in Excel helps visualize and understand data by highlighting important information using colors, icons, and data bars.', 'Steps to create conditional formatting rules, such as highlighting cells with values greater than 5,000.', 'Identifying duplicate values in a sales table using conditional formatting.', 'Customizing icon set rules based on specific criteria.', 'Applying conditional formatting using formulas.', 'Determining commission percentage based on sales amount.']}, {'end': 1718.731, 'segs': [{'end': 1240.167, 'src': 'embed', 'start': 1207.51, 'weight': 0, 'content': [{'end': 1210.152, 'text': 'The first parameter we need to pass is a logical test.', 'start': 1207.51, 'duration': 2.642}, {'end': 1219.978, 'text': 'Here, the logical test is if sales, cell G5, greater than or equal to 10, 000, a sign value as 5%.', 'start': 1210.832, 'duration': 9.146}, {'end': 1221.939, 'text': 'Then we will pass the second logical test.', 'start': 1219.978, 'duration': 1.961}, {'end': 1224.478, 'text': 'Here, we will use AND function.', 'start': 1222.537, 'duration': 1.941}, {'end': 1238.286, 'text': 'If open bracket AND open bracket sales at cell G5 greater than or equal to 5, 000, comma at cell G5, less than or equal to 10, 000, comma,', 'start': 1225.038, 'duration': 13.248}, {'end': 1240.167, 'text': 'assign value as 2.5%.', 'start': 1238.286, 'duration': 1.881}], 'summary': 'Set logical tests for sales value, assigning 5% for >=10,000 and 2.5% for 5,000-10,000.', 'duration': 32.657, 'max_score': 1207.51, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/W3vrMSah3rc/pics/W3vrMSah3rc1207510.jpg'}, {'end': 1296.914, 'src': 'embed', 'start': 1267.678, 'weight': 3, 'content': [{'end': 1274.679, 'text': 'The OR function returns true if any of the conditions are true and returns false if all of the conditions are false.', 'start': 1267.678, 'duration': 7.001}, {'end': 1281.74, 'text': 'In the given table, we are required to assign performance remarks for each salesman as per the scaling table.', 'start': 1275.979, 'duration': 5.761}, {'end': 1284.348, 'text': 'scaling table.', 'start': 1283.607, 'duration': 0.741}, {'end': 1296.914, 'text': 'we have the criteria that if net sales greater than 10, 000 or profit greater than 5, 000, set remark as good else set remark as needs improvement.', 'start': 1284.348, 'duration': 12.566}], 'summary': 'Using or function to assign performance remarks based on sales and profit criteria.', 'duration': 29.236, 'max_score': 1267.678, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/W3vrMSah3rc/pics/W3vrMSah3rc1267678.jpg'}, {'end': 1385.691, 'src': 'embed', 'start': 1329.676, 'weight': 2, 'content': [{'end': 1333.099, 'text': "Let's drag this formula across the cells with the help of the fill handle.", 'start': 1329.676, 'duration': 3.423}, {'end': 1337.623, 'text': 'This formula will return true if any of the condition is true.', 'start': 1334.44, 'duration': 3.183}, {'end': 1341.726, 'text': 'Thus, in the given example, the cell J9 returns good.', 'start': 1338.203, 'duration': 3.523}, {'end': 1344.477, 'text': 'even if profit is less than 5, 000.', 'start': 1342.156, 'duration': 2.321}, {'end': 1348.54, 'text': 'Our next function is true slash false.', 'start': 1344.478, 'duration': 4.062}, {'end': 1354.485, 'text': 'True slash false returns true and false respectively if we write them in any cell.', 'start': 1348.941, 'duration': 5.544}, {'end': 1359.408, 'text': 'The not function reverses the result of the function.', 'start': 1356.346, 'duration': 3.062}, {'end': 1363.411, 'text': 'If result is true, it returns false, else true.', 'start': 1360.269, 'duration': 3.142}, {'end': 1372.444, 'text': "Let's write formula as equal, not, open bracket, K5, close bracket, in cell L5.", 'start': 1364.552, 'duration': 7.892}, {'end': 1375.265, 'text': 'This returns the result as false.', 'start': 1373.444, 'duration': 1.821}, {'end': 1385.691, 'text': 'Let us now study about different lookup and reference functions used in Excel, such as vlookup, hlookup, match, index, and offset function.', 'start': 1377.587, 'duration': 8.104}], 'summary': 'Excel formulas return results based on conditions; not function reverses results; various lookup and reference functions are used in excel.', 'duration': 56.015, 'max_score': 1329.676, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/W3vrMSah3rc/pics/W3vrMSah3rc1329676.jpg'}, {'end': 1432.926, 'src': 'embed', 'start': 1408.89, 'weight': 4, 'content': [{'end': 1415.513, 'text': 'The table depicts sales information based on order ID, product sold, salesman, and net sales.', 'start': 1408.89, 'duration': 6.623}, {'end': 1422.196, 'text': 'Now we want to determine the net sales corresponding to order ID 1121.', 'start': 1416.274, 'duration': 5.922}, {'end': 1425.478, 'text': "Let's understand how we can accomplish this using the VLOOKUP function.", 'start': 1422.196, 'duration': 3.282}, {'end': 1430.144, 'text': 'Note that VLOOKUP function is only used for vertical tables.', 'start': 1426.721, 'duration': 3.423}, {'end': 1432.926, 'text': "Let's use cell I2 to add the formula.", 'start': 1430.764, 'duration': 2.162}], 'summary': 'Sales table analysis using vlookup for order id 1121.', 'duration': 24.036, 'max_score': 1408.89, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/W3vrMSah3rc/pics/W3vrMSah3rc1408890.jpg'}, {'end': 1558.089, 'src': 'embed', 'start': 1532.632, 'weight': 5, 'content': [{'end': 1537.313, 'text': 'That means the lookup value should always be present in the leftmost column of our table array.', 'start': 1532.632, 'duration': 4.681}, {'end': 1544.803, 'text': 'If our table is in horizontal format, and we want to determine a specific value, then we use the HLOOKUP function.', 'start': 1538.733, 'duration': 6.07}, {'end': 1547.024, 'text': 'Consider the given table.', 'start': 1545.943, 'duration': 1.081}, {'end': 1549.485, 'text': "Let's write the formula in cell I4.", 'start': 1547.704, 'duration': 1.781}, {'end': 1558.089, 'text': 'We will start with the equal sign, then type our function name, HLOOKUP, and then type open parentheses.', 'start': 1550.486, 'duration': 7.603}], 'summary': 'Using hlookup function to find specific value in horizontal table.', 'duration': 25.457, 'max_score': 1532.632, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/W3vrMSah3rc/pics/W3vrMSah3rc1532632.jpg'}, {'end': 1648.528, 'src': 'embed', 'start': 1618.301, 'weight': 6, 'content': [{'end': 1626.323, 'text': 'The match function searches for a specified value in an item in a single dimensional array and then returns the relative position of that item in the array.', 'start': 1618.301, 'duration': 8.022}, {'end': 1629.724, 'text': 'Let us understand its functioning with the help of an example.', 'start': 1627.223, 'duration': 2.501}, {'end': 1632.222, 'text': 'Consider the given sales table shown.', 'start': 1630.421, 'duration': 1.801}, {'end': 1636.843, 'text': 'Here, we want to determine the position of the corresponding date in the original table.', 'start': 1632.742, 'duration': 4.101}, {'end': 1640.125, 'text': "So let's write the formula in cell E5.", 'start': 1637.724, 'duration': 2.401}, {'end': 1648.528, 'text': 'Type equal sign, then type the function name, match, and then type an open parentheses.', 'start': 1641.585, 'duration': 6.943}], 'summary': 'The match function locates a value in an array and returns its position.', 'duration': 30.227, 'max_score': 1618.301, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/W3vrMSah3rc/pics/W3vrMSah3rc1618301.jpg'}], 'start': 1182.646, 'title': 'Excel functions and formulas', 'summary': 'Covers creating between formula with if and and functions in excel, assigning specific percentages based on logical tests for different sales ranges, and also includes detailed explanations and examples of or, if, true/false, not, vlookup, hlookup, match functions.', 'chapters': [{'end': 1266.157, 'start': 1182.646, 'title': 'Creating between formula with if and and functions', 'summary': 'Explains how to create a between formula using the if and and functions in excel, where values between two given ranges are assigned specific percentages based on logical tests, with an example demonstrating the assignment of percentages 5%, 2.5%, and 1.5% for different sales ranges.', 'duration': 83.511, 'highlights': ['The formula uses logical tests with IF and AND functions to assign percentages based on specific sales ranges. The chapter describes the use of IF and AND functions to create a formula that assigns percentages to sales ranges based on logical tests, demonstrating the assignment of 5%, 2.5%, and 1.5% for different sales ranges.', 'The example demonstrates the assignment of a 5% value for sales greater than or equal to 10,000, a 2.5% value for sales between 5,000 and 10,000, and a 1.5% value for other cases. The example provided in the transcript illustrates the assignment of 5% for sales greater than or equal to 10,000, 2.5% for sales between 5,000 and 10,000, and 1.5% for other cases.', 'The demonstration shows the application of the formula to multiple cells using the fill handle to obtain results for various sales ranges. The chapter demonstrates the application of the formula to multiple cells using the fill handle to obtain results for various sales ranges, showcasing the efficiency of the formula in handling multiple data points.']}, {'end': 1718.731, 'start': 1267.678, 'title': 'Excel functions and formulas', 'summary': 'Covers the or, if, true/false, not, vlookup, hlookup, match functions in excel, demonstrating the use of logical functions, lookup and reference functions, with examples and detailed explanations.', 'duration': 451.053, 'highlights': ['The OR function returns true if any of the conditions are true and returns false if all of the conditions are false. The OR function logic and its application in determining performance remarks based on net sales and profit.', 'The VLOOKUP function lets us search for specific information in the current worksheet. Explanation and example of using VLOOKUP function to search for net sales corresponding to a specific order ID.', 'The HLOOKUP function is used to determine a specific value in a horizontal table. Explanation and example of using HLOOKUP function to determine a specific value in a horizontal table.', 'The MATCH function searches for a specified value in an item in a single-dimensional array and returns the relative position of that item in the array. Explanation and example of using the MATCH function to determine the position of a corresponding date in the original table.', 'The NOT function reverses the result of the function. Explanation of the NOT function and its application in reversing the result of a function.']}], 'duration': 536.085, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/W3vrMSah3rc/pics/W3vrMSah3rc1182646.jpg', 'highlights': ['The chapter describes the use of IF and AND functions to create a formula that assigns percentages to sales ranges based on logical tests, demonstrating the assignment of 5%, 2.5%, and 1.5% for different sales ranges.', 'The example provided in the transcript illustrates the assignment of 5% for sales greater than or equal to 10,000, 2.5% for sales between 5,000 and 10,000, and 1.5% for other cases.', 'The chapter demonstrates the application of the formula to multiple cells using the fill handle to obtain results for various sales ranges, showcasing the efficiency of the formula in handling multiple data points.', 'The OR function returns true if any of the conditions are true and returns false if all of the conditions are false.', 'Explanation and example of using VLOOKUP function to search for net sales corresponding to a specific order ID.', 'Explanation and example of using HLOOKUP function to determine a specific value in a horizontal table.', 'Explanation and example of using the MATCH function to determine the position of a corresponding date in the original table.', 'Explanation of the NOT function and its application in reversing the result of a function.']}, {'end': 2020.986, 'segs': [{'end': 1816.3, 'src': 'embed', 'start': 1758.814, 'weight': 0, 'content': [{'end': 1764.696, 'text': 'For example, net sales is at seventh position in the range B17 to I17.', 'start': 1758.814, 'duration': 5.882}, {'end': 1767.556, 'text': "Let's write the function in cell E3.", 'start': 1765.596, 'duration': 1.96}, {'end': 1771.977, 'text': 'Write formula as equal sign, match, and open parentheses.', 'start': 1768.016, 'duration': 3.961}, {'end': 1774.258, 'text': 'In the first argument, select cell C3.', 'start': 1772.417, 'duration': 1.841}, {'end': 1779.773, 'text': 'For second argument, select cells B17 to I17.', 'start': 1775.592, 'duration': 4.181}, {'end': 1782.894, 'text': 'Past third argument is 0.', 'start': 1780.754, 'duration': 2.14}, {'end': 1784.455, 'text': 'Finally, close the parentheses.', 'start': 1782.894, 'duration': 1.561}, {'end': 1799.539, 'text': 'So our function looks like equal match open bracket C3 comma dollar sign B dollar sign 17 colon dollar sign I dollar sign 17 comma 0 close bracket.', 'start': 1785.555, 'duration': 13.984}, {'end': 1805.836, 'text': 'So for profit loss field, the value returned is 8.', 'start': 1800.259, 'duration': 5.577}, {'end': 1811.598, 'text': 'net sale, the value returned is 7.', 'start': 1805.836, 'duration': 5.762}, {'end': 1816.3, 'text': 'The INDEX function returns value from a specified position in a specific column in a list.', 'start': 1811.598, 'duration': 4.702}], 'summary': 'Using the match function, net sales is at 7th position, and for profit loss, the value returned is 8.', 'duration': 57.486, 'max_score': 1758.814, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/W3vrMSah3rc/pics/W3vrMSah3rc1758814.jpg'}, {'end': 1970.058, 'src': 'embed', 'start': 1905.593, 'weight': 2, 'content': [{'end': 1907.534, 'text': 'The third column is call num.', 'start': 1905.593, 'duration': 1.941}, {'end': 1911.955, 'text': 'This corresponds to the column number that has the value we want to return.', 'start': 1908.434, 'duration': 3.521}, {'end': 1918.416, 'text': 'Here we will enter E3, which denotes the position of the field name in cell C3 in table.', 'start': 1912.615, 'duration': 5.801}, {'end': 1927.136, 'text': 'Finally, close the parentheses.', 'start': 1925.555, 'duration': 1.581}, {'end': 1936.044, 'text': 'Thus, our formula returns the value as 6, 528 based on the position at cells E5.', 'start': 1929.278, 'duration': 6.766}, {'end': 1940.908, 'text': 'This is the net sales value on the date, the 2nd of January.', 'start': 1936.745, 'duration': 4.163}, {'end': 1945.512, 'text': 'Now drag the formula with the fill handle to the other cells as well.', 'start': 1942.149, 'duration': 3.363}, {'end': 1958.196, 'text': 'The offset function returns a reference to a range that is the offset number of the rows and columns from another range or cell.', 'start': 1951.394, 'duration': 6.802}, {'end': 1960.456, 'text': "Now let's understand how to use it.", 'start': 1958.976, 'duration': 1.48}, {'end': 1967.638, 'text': 'Select cell D5, write formula as equal sign, offset, and open parentheses.', 'start': 1961.176, 'duration': 6.462}, {'end': 1970.058, 'text': 'The first argument is the reference.', 'start': 1968.298, 'duration': 1.76}], 'summary': 'Using the offset function to return net sales value as 6,528 based on the position at cells e5.', 'duration': 64.465, 'max_score': 1905.593, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/W3vrMSah3rc/pics/W3vrMSah3rc1905593.jpg'}], 'start': 1719.184, 'title': 'Excel data retrieval functions', 'summary': "Discusses using match and index functions to determine the position of field names and return values from specific positions, with examples showing positions of 'profit loss' as 8 and 'net sales' as 7. it also explains using index and offset functions in excel for dynamic data retrieval, with an example of retrieving the net sales value for a specified date, resulting in 6,528.", 'chapters': [{'end': 1816.3, 'start': 1719.184, 'title': 'Using match and index functions', 'summary': "Discusses using the match function to determine the position of a field name in a range and using the index function to return a value from a specified position in a specific column in a list, with examples showing the positions of 'profit loss' and 'net sales' as 8 and 7 respectively.", 'duration': 97.116, 'highlights': ["The INDEX function returns value from a specified position in a specific column in a list, with examples showing the positions of 'profit loss' and 'net sales' as 8 and 7 respectively.", "Using the MATCH function to determine the position of a field name in a range, with examples demonstrating the position of 'net sales' as seventh in the range B17 to I17."]}, {'end': 2020.986, 'start': 1817.22, 'title': 'Using index and offset functions for dynamic data retrieval', 'summary': 'Explains how to use the index and offset functions in excel to dynamically fetch values from a table based on cell input, with an example of retrieving the net sales value for a specified date, which returns 6,528.', 'duration': 203.766, 'highlights': ['The index function is used to determine the value in the worksheet, fetching the net sales value of 6,528 based on the position at cells E5 for the 2nd of January. The index function is demonstrated to fetch the net sales value of 6,528 based on the position at cells E5 for the 2nd of January.', 'The offset function is explained, returning a reference to a range by applying an offset to the starting range, with an example of returning a value based on the specified date. The offset function is explained, returning a reference to a range by applying an offset to the starting range, with an example of returning a value based on the specified date.']}], 'duration': 301.802, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/W3vrMSah3rc/pics/W3vrMSah3rc1719184.jpg', 'highlights': ["Using the INDEX function to return values from specific positions, with examples showing positions of 'profit loss' as 8 and 'net sales' as 7.", "Demonstrating the position of 'net sales' as seventh in the range B17 to I17 using the MATCH function.", 'Fetching the net sales value of 6,528 based on the position at cells E5 for the 2nd of January using the INDEX function.', 'Explaining the OFFSET function, returning a reference to a range by applying an offset to the starting range, with an example of returning a value based on the specified date.']}, {'end': 2544.205, 'segs': [{'end': 2106.531, 'src': 'embed', 'start': 2077.449, 'weight': 0, 'content': [{'end': 2081.61, 'text': 'The SUMIFS function calculates the sum of sales based on certain criteria.', 'start': 2077.449, 'duration': 4.161}, {'end': 2085.733, 'text': "Let's understand how to use the function with the help of an example.", 'start': 2083.071, 'duration': 2.662}, {'end': 2087.734, 'text': 'Consider the given sales table.', 'start': 2086.313, 'duration': 1.421}, {'end': 2091.795, 'text': 'Say we want to determine the net sales done by salesman Justin.', 'start': 2088.234, 'duration': 3.561}, {'end': 2096.737, 'text': 'Here we will use the SUMIFS function to calculate net sales.', 'start': 2093.417, 'duration': 3.32}, {'end': 2099.9, 'text': "So let's write the formula in cell N4.", 'start': 2097.479, 'duration': 2.421}, {'end': 2101.561, 'text': 'Type equal sign.', 'start': 2100.5, 'duration': 1.061}, {'end': 2106.531, 'text': 'then type the function name, SUMIFS, and then type an open parentheses.', 'start': 2102.25, 'duration': 4.281}], 'summary': 'Sumifs calculates net sales based on specified criteria.', 'duration': 29.082, 'max_score': 2077.449, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/W3vrMSah3rc/pics/W3vrMSah3rc2077449.jpg'}, {'end': 2182.972, 'src': 'embed', 'start': 2155.098, 'weight': 1, 'content': [{'end': 2158.42, 'text': 'COUNTIFS function works in a similar manner like SUMIFS.', 'start': 2155.098, 'duration': 3.322}, {'end': 2162.542, 'text': 'It counts the number of cells in a given range when certain criteria is met.', 'start': 2159.06, 'duration': 3.482}, {'end': 2168.841, 'text': 'Say in the given sales table, we want to determine the total number of sales for net sales greater than 8, 000.', 'start': 2163.317, 'duration': 5.524}, {'end': 2172.924, 'text': 'To determine the count, we will use the COUNTIFS function.', 'start': 2168.841, 'duration': 4.083}, {'end': 2175.987, 'text': "So let's write the formula in cell N5.", 'start': 2173.545, 'duration': 2.442}, {'end': 2182.972, 'text': 'Type the equal sign, then type the function name, COUNTIFS, and then type an open parentheses.', 'start': 2176.547, 'duration': 6.425}], 'summary': 'Countifs counts cells meeting criteria, e.g. net sales > 8,000.', 'duration': 27.874, 'max_score': 2155.098, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/W3vrMSah3rc/pics/W3vrMSah3rc2155098.jpg'}, {'end': 2293.486, 'src': 'embed', 'start': 2236.464, 'weight': 2, 'content': [{'end': 2237.825, 'text': 'Consider the given sales table.', 'start': 2236.464, 'duration': 1.361}, {'end': 2242.889, 'text': "Let's see how to use the percentile function on net sales to find its 40th percentile.", 'start': 2238.385, 'duration': 4.504}, {'end': 2244.73, 'text': 'Select the cell N6.', 'start': 2243.629, 'duration': 1.101}, {'end': 2249.494, 'text': 'Write the formula as equal sign, percentile, open bracket.', 'start': 2245.791, 'duration': 3.703}, {'end': 2253.289, 'text': 'Select the column net sales H4 to H1328 as our first argument.', 'start': 2250.188, 'duration': 3.101}, {'end': 2255.129, 'text': 'Pass the second argument as 0.4 in order to find the 40th percentile.', 'start': 2253.309, 'duration': 1.82}, {'end': 2255.81, 'text': 'This returns the result 5220.', 'start': 2255.229, 'duration': 0.581}, {'end': 2259.39, 'text': 'This means that 40% of the net sales is less than or equal to 5220.', 'start': 2255.81, 'duration': 3.58}, {'end': 2260.411, 'text': 'The next function is quartile.', 'start': 2259.39, 'duration': 1.021}, {'end': 2280.884, 'text': 'Quartile function depicts the results for the first, second, third and fourth quartile.', 'start': 2276.803, 'duration': 4.081}, {'end': 2287.545, 'text': 'Say we want to find the total number of sales which are less than or equal to 25%.', 'start': 2281.804, 'duration': 5.741}, {'end': 2289.766, 'text': "Let's use the quartile function to calculate this.", 'start': 2287.545, 'duration': 2.221}, {'end': 2293.486, 'text': 'Select cell N7 to write the formula.', 'start': 2290.726, 'duration': 2.76}], 'summary': 'Using percentile function, 40th percentile of net sales is 5220. quartile function calculates total sales <= 25%.', 'duration': 57.022, 'max_score': 2236.464, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/W3vrMSah3rc/pics/W3vrMSah3rc2236464.jpg'}, {'end': 2438.752, 'src': 'heatmap', 'start': 2395.866, 'weight': 4, 'content': [{'end': 2403.979, 'text': 'To calculate median in cell N9, Write the formula as equal sign, the function name median, then open braces.', 'start': 2395.866, 'duration': 8.113}, {'end': 2406.901, 'text': 'Select net sales column as its argument.', 'start': 2404.76, 'duration': 2.141}, {'end': 2421.889, 'text': 'This calculates the mid value of net sales as 6, 192.', 'start': 2415.746, 'duration': 6.143}, {'end': 2428.214, 'text': 'Rank function is used to compare a number from the rest of the numbers in a list in any order, whether ascending or descending.', 'start': 2421.89, 'duration': 6.324}, {'end': 2433.408, 'text': 'This function returns the rank of the number relative to the other values in the list.', 'start': 2429.005, 'duration': 4.403}, {'end': 2438.752, 'text': "Let's use rank function in cell J4 to calculate rank on the basis of net sales.", 'start': 2434.109, 'duration': 4.643}], 'summary': 'Calculate median of net sales in cell n9 and use rank function in cell j4 to calculate rank based on net sales.', 'duration': 73.466, 'max_score': 2395.866, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/W3vrMSah3rc/pics/W3vrMSah3rc2395866.jpg'}], 'start': 2021.786, 'title': 'Excel statistical functions and usage in countifs', 'summary': 'Covers excel statistical functions like sumif, countifs, percentile, quartile, standard deviation, and median, with emphasis on the usage of sumifs and countifs as commonly used functions. it also discusses the application of countifs to determine sales exceeding 8,000, and explores percentile, quartile, standard deviation, median, and rank functions with examples and results.', 'chapters': [{'end': 2153.778, 'start': 2021.786, 'title': 'Excel statistical functions', 'summary': 'Covers various statistical functions like sumif, countifs, percentile, quartile, standard deviation, and median in excel, highlighting the usage of sumifs and countifs as the most frequently used functions.', 'duration': 131.992, 'highlights': ['The chapter covers various statistical functions like SUMIF, COUNTIFS, PERCENTILE, QUARTILE, STANDARD DEVIATION, and MEDIAN in Excel.', 'The SUMIFS function calculates the sum of sales based on certain criteria, with cells H4 to H1328 as the sum range and cells E4 to E1328 as the criteria range.', 'The COUNTIFS function allows performing count based on one or more criteria in Excel.']}, {'end': 2544.205, 'start': 2155.098, 'title': 'Using countifs and statistical functions in excel', 'summary': 'Discusses the usage of countifs function in excel to determine the total number of sales for net sales greater than 8,000, and also covers the application of percentile, quartile, standard deviation, median, and rank functions with specific examples and results.', 'duration': 389.107, 'highlights': ['The COUNTIFS function is used to determine the total number of sales for net sales greater than 8,000, yielding a count of 445 entries.', 'The percentile function is employed to find the 40th percentile of net sales, resulting in a value of 5220, indicating that 40% of the net sales is less than or equal to 5220.', 'The quartile function is utilized to calculate that 25% of the sales is less than or equal to 3822, returning the value 3822.', 'The STDEV function is utilized to calculate the standard deviation for the number of customers, yielding a result of 1.4.', 'The median function is applied to calculate the mid value of net sales as 6,192.', 'The rank function is used to determine the rank of the net sales, with the specific value of 7,164 being ranked as 553.']}], 'duration': 522.419, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/W3vrMSah3rc/pics/W3vrMSah3rc2021786.jpg', 'highlights': ['The SUMIFS function calculates the sum of sales based on certain criteria.', 'The COUNTIFS function is used to determine the total number of sales for net sales greater than 8,000, yielding a count of 445 entries.', 'The percentile function is employed to find the 40th percentile of net sales, resulting in a value of 5220.', 'The quartile function is utilized to calculate that 25% of the sales is less than or equal to 3822, returning the value 3822.', 'The STDEV function is utilized to calculate the standard deviation for the number of customers, yielding a result of 1.4.', 'The median function is applied to calculate the mid value of net sales as 6,192.']}], 'highlights': ['Business analytics empowers businesses to automate and optimize processes, sustaining competitive advantage.', 'The term business analytics refers to the skills, technologies, applications, and practices that facilitate continuous iterative exploration and investigation of past business performance, customer data, and competitive landscape data.', 'Businesses must understand changing market trends in social media to stay ahead of the competition and drive effective business decisions.', "Excel's in-memory technology reduces dependency on specialized solutions.", 'Power Business Intelligence easily integrates with Excel, enhancing its analytics capabilities.', 'Excel is increasingly preferred by analysts, reporting professionals, and BI people as the future of business intelligence workflows.', 'The course covers how to use Excel to perform multivariate statistics and explore, experiment, and forecast results.', 'Excel offers logical, statistical, and mathematical functions like VLOOKUP, HLOOKUP, IF NOT, RANK, QUARTILE for calculations and analysis.', 'Conditional formatting in Excel visually explores data, detects issues, and identifies patterns using colors, icons, and formulae.', 'Proper formatting techniques in Excel enhance appearance and readability through font, color, size, and borders.', 'Custom formatting in Excel displays values like 1.53M for 1,532,000 and formats dates like 1st Sunday Jan 2012.', 'Steps for applying custom formatting in Excel include selecting the column and specifying the custom format in the dialog box.', "An example of custom format for Net Sales involves entering 'Number, Column, number, number, zero, decimal, zero, zero, comma, open double quote, capital K, close double quote' to display values in thousands.", "The custom format 'DD-DDDD-MMM-YY' is used to display dates like 1st Sunday Jan 2012.", 'Conditional formatting in Excel helps visualize and understand data by highlighting important information using colors, icons, and data bars.', 'Steps to create conditional formatting rules, such as highlighting cells with values greater than 5,000.', 'Identifying duplicate values in a sales table using conditional formatting.', 'Customizing icon set rules based on specific criteria.', 'Applying conditional formatting using formulas.', 'Determining commission percentage based on sales amount.', 'The chapter describes the use of IF and AND functions to create a formula that assigns percentages to sales ranges based on logical tests, demonstrating the assignment of 5%, 2.5%, and 1.5% for different sales ranges.', 'The example provided in the transcript illustrates the assignment of 5% for sales greater than or equal to 10,000, 2.5% for sales between 5,000 and 10,000, and 1.5% for other cases.', 'The chapter demonstrates the application of the formula to multiple cells using the fill handle to obtain results for various sales ranges, showcasing the efficiency of the formula in handling multiple data points.', 'The OR function returns true if any of the conditions are true and returns false if all of the conditions are false.', 'Explanation and example of using VLOOKUP function to search for net sales corresponding to a specific order ID.', 'Explanation and example of using HLOOKUP function to determine a specific value in a horizontal table.', 'Explanation and example of using the MATCH function to determine the position of a corresponding date in the original table.', 'Explanation of the NOT function and its application in reversing the result of a function.', "Using the INDEX function to return values from specific positions, with examples showing positions of 'profit loss' as 8 and 'net sales' as 7.", "Demonstrating the position of 'net sales' as seventh in the range B17 to I17 using the MATCH function.", 'Fetching the net sales value of 6,528 based on the position at cells E5 for the 2nd of January using the INDEX function.', 'Explaining the OFFSET function, returning a reference to a range by applying an offset to the starting range, with an example of returning a value based on the specified date.', 'The SUMIFS function calculates the sum of sales based on certain criteria.', 'The COUNTIFS function is used to determine the total number of sales for net sales greater than 8,000, yielding a count of 445 entries.', 'The percentile function is employed to find the 40th percentile of net sales, resulting in a value of 5220.', 'The quartile function is utilized to calculate that 25% of the sales is less than or equal to 3822, returning the value 3822.', 'The STDEV function is utilized to calculate the standard deviation for the number of customers, yielding a result of 1.4.', 'The median function is applied to calculate the mid value of net sales as 6,192.']}