title

Excel Formulas and Functions | 16 Most Important Formulas in Excel | Excel Training | Edureka

description

** Edureka Online Training: https://www.edureka.co/ **
This Edureka video on 'Excel Formulas and Functions' will help you learn what are Formulas and functions in Excel along with the 16 Most Important Formulas you need to know. Below are the topics covered in this Excel tutorial:
1.13 What is a Formula?
2.30 Writing Excel Formulas
3.47 Editing a Formula
4.33 Copy/ Paste a Formula
5.58 Hide Formulas in Excel
7.33 Operator Precedence Excel Formulas
8.32 What are Functions in Excel?
9.31 Most Important Excel Functions
#Edureka #ExcelEdureka #ExcelFormulasandFunctions #ExcelTutorial
Do subscribe to our channel and hit the bell icon to never miss an update from us in the future: https://goo.gl/6ohpTV
Instagram: https://www.instagram.com/edureka_learning/
Facebook: https://www.facebook.com/edurekaIN/
Twitter: https://twitter.com/edurekain
LinkedIn: https://www.linkedin.com/company/edureka
-----------------------------------------
For more information, Please write back to us at sales@edureka.in or call us at IND: 9606058406 / US: 18338555775 (toll free)

detail

{'title': 'Excel Formulas and Functions | 16 Most Important Formulas in Excel | Excel Training | Edureka', 'heatmap': [{'end': 1022.803, 'start': 978.898, 'weight': 0.952}, {'end': 1287.988, 'start': 1251.293, 'weight': 0.837}, {'end': 1684.063, 'start': 1664.787, 'weight': 0.86}, {'end': 1741.797, 'start': 1709.66, 'weight': 0.782}], 'summary': 'Covers essential excel formulas and functions, including mathematical operators, cell references, date functions, if and vlookup functions, tax calculations, and practical demonstrations. it provides insights into operator precedence, percentage calculation, and compound interest calculations, making it suitable for individuals seeking certification in trending technologies.', 'chapters': [{'end': 45.188, 'segs': [{'end': 45.188, 'src': 'embed', 'start': 11.721, 'weight': 0, 'content': [{'end': 18.107, 'text': 'Data comes to use only when you can actually work on it, and Excel is one tool that provides great amount of convenience,', 'start': 11.721, 'duration': 6.386}, {'end': 21.99, 'text': 'both when you have to formulate equations of your own or make use of the built-in ones.', 'start': 18.107, 'duration': 3.883}, {'end': 25.713, 'text': 'Hey everyone, welcome back to a whole new session from edureka.', 'start': 22.791, 'duration': 2.922}, {'end': 31.979, 'text': "My name is Vajiha and in this session, you'll be learning how you can actually work with these Excel formulas and functions.", 'start': 26.294, 'duration': 5.685}, {'end': 39.92, 'text': 'Okay, so before we begin just make sure you subscribe to our Channel and hit the bell icon to stay updated with all the latest Edureka videos.', 'start': 33.05, 'duration': 6.87}, {'end': 45.188, 'text': "Also, if you're looking for an online training certification in any of the trending Technologies,", 'start': 40.541, 'duration': 4.647}], 'summary': 'Excel provides convenience for formulating and using equations. learn to work with excel formulas and functions in this session.', 'duration': 33.467, 'max_score': 11.721, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/hRsuACY8Wdc/pics/hRsuACY8Wdc11721.jpg'}], 'start': 11.721, 'title': 'Working with excel formulas and functions', 'summary': 'Discusses the convenience of using excel for formulating and utilizing equations and introduces the topic of working with excel formulas and functions for online training certification in trending technologies.', 'chapters': [{'end': 45.188, 'start': 11.721, 'title': 'Working with excel formulas and functions', 'summary': 'Discusses the convenience of using excel for formulating and utilizing equations, emphasizing the importance of data utilization, and introduces the topic of working with excel formulas and functions for online training certification in trending technologies.', 'duration': 33.467, 'highlights': ['Excel provides convenience for formulating and utilizing equations, enhancing data utilization.', 'Introduction to working with Excel formulas and functions for online training certification in trending technologies.', "Importance of subscribing to Edureka's channel for staying updated with the latest videos."]}], 'duration': 33.467, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/hRsuACY8Wdc/pics/hRsuACY8Wdc11721.jpg', 'highlights': ['Introduction to working with Excel formulas and functions for online training certification in trending technologies.', 'Excel provides convenience for formulating and utilizing equations, enhancing data utilization.', "Importance of subscribing to Edureka's channel for staying updated with the latest videos."]}, {'end': 449.15, 'segs': [{'end': 75.733, 'src': 'embed', 'start': 45.188, 'weight': 3, 'content': [{'end': 47.371, 'text': 'check out the link given in the description box below.', 'start': 45.188, 'duration': 2.183}, {'end': 49.552, 'text': 'Coming back towards the session.', 'start': 48.251, 'duration': 1.301}, {'end': 57.679, 'text': 'We shall first begin by understanding what exactly are formulas and how you can actually write edit copy and hide formulas in Excel next.', 'start': 49.772, 'duration': 7.907}, {'end': 62.723, 'text': 'We shall understand the operator precedence in Excel the various functions that are available with their categories.', 'start': 57.839, 'duration': 4.884}, {'end': 67.326, 'text': "And finally, I'll be showing you guys how to work with the most important Excel formulas.", 'start': 63.183, 'duration': 4.143}, {'end': 70.389, 'text': "Okay, so without any further delays, let's get started.", 'start': 67.927, 'duration': 2.462}, {'end': 75.733, 'text': 'So moving on towards the first topic of the session, which is what exactly is a formula.', 'start': 71.369, 'duration': 4.364}], 'summary': 'Learn about excel formulas and functions, including writing, editing, copying, and hiding formulas, understanding operator precedence, and working with important excel formulas.', 'duration': 30.545, 'max_score': 45.188, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/hRsuACY8Wdc/pics/hRsuACY8Wdc45188.jpg'}, {'end': 103.712, 'src': 'embed', 'start': 80.445, 'weight': 0, 'content': [{'end': 88.187, 'text': 'in terms of symbols in Excel, formulas are expressions that can be entered into the cells of an Excel sheet and their outputs are displayed.', 'start': 80.445, 'duration': 7.742}, {'end': 97.93, 'text': 'as a result, Excel formulas can be of any type, such as mathematical operators, values or texts, cell references or the worksheet functions.', 'start': 88.187, 'duration': 9.743}, {'end': 103.712, 'text': 'mathematical operators include the operators such as plus, minus, star, the divided by symbol, Etc.', 'start': 97.93, 'duration': 5.782}], 'summary': 'Excel formulas are expressions that can be of any type, including mathematical operators, values, texts, and cell references.', 'duration': 23.267, 'max_score': 80.445, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/hRsuACY8Wdc/pics/hRsuACY8Wdc80445.jpg'}, {'end': 191.635, 'src': 'embed', 'start': 165.071, 'weight': 2, 'content': [{'end': 169.793, 'text': 'after that, you can either type a cell address or specify the values that you intend to calculate.', 'start': 165.071, 'duration': 4.722}, {'end': 173.115, 'text': 'So for example, let me just enter some values over here.', 'start': 170.374, 'duration': 2.741}, {'end': 180.338, 'text': "I'll say a 1 is 3 and a 2 is 2 and then I want to return the sum of a 1 and a 2 in a tree.", 'start': 173.335, 'duration': 7.003}, {'end': 181.606, 'text': 'Now to do that.', 'start': 180.965, 'duration': 0.641}, {'end': 187.651, 'text': 'All I have to do is type in an equal to sign and then I can simply type down the cell address.', 'start': 181.686, 'duration': 5.965}, {'end': 191.635, 'text': "That's a 1 and a 2 with a plus sign in between and then hit enter.", 'start': 187.892, 'duration': 3.743}], 'summary': 'Demonstrates entering values and calculating sum using cell addresses in excel.', 'duration': 26.564, 'max_score': 165.071, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/hRsuACY8Wdc/pics/hRsuACY8Wdc165071.jpg'}, {'end': 295.595, 'src': 'embed', 'start': 270.047, 'weight': 4, 'content': [{'end': 275.629, 'text': 'So now moving on towards something very interesting, which is how to copy paste a formula in Excel.', 'start': 270.047, 'duration': 5.582}, {'end': 279.19, 'text': 'Excel comes in really handy when you have to copy paste formulas.', 'start': 275.629, 'duration': 3.561}, {'end': 284.991, 'text': 'whenever you copy a formula, Excel automatically takes care of the cell references that are required at that position.', 'start': 279.19, 'duration': 5.801}, {'end': 288.952, 'text': 'Now this task is taken care of by the relative cell address system.', 'start': 285.751, 'duration': 3.201}, {'end': 295.595, 'text': 'Here the copied formula will have modified row and column addresses that will suit its new position.', 'start': 289.853, 'duration': 5.742}], 'summary': 'Excel makes it easy to copy and paste formulas, adjusting cell references automatically.', 'duration': 25.548, 'max_score': 270.047, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/hRsuACY8Wdc/pics/hRsuACY8Wdc270047.jpg'}], 'start': 45.188, 'title': 'Excel formulas and functions', 'summary': 'Discusses excel formulas basics, including definition, types, and usage, along with mathematical operators, values, cell references, worksheet functions, and working with formulas such as writing, editing, copying, pasting, and hiding, as well as understanding operator precedence. it also covers the concept of relative cell addresses and protecting the sheet.', 'chapters': [{'end': 145.839, 'start': 45.188, 'title': 'Understanding excel formulas', 'summary': 'Discusses the basics of excel formulas, including their definition, types, and usage, followed by a focus on mathematical operators, values or texts, cell references, and worksheet functions. it also covers working with formulas in excel such as writing, editing, copying, pasting, and hiding formulas, as well as understanding operator precedence.', 'duration': 100.651, 'highlights': ['Formulas in Excel are expressions that can be entered into the cells of an Excel sheet, and their outputs are displayed.', 'Excel formulas can consist of mathematical operators, values or texts, cell references, or worksheet functions.', 'When comparing two cells in Excel, specifying an equal sign between the cell references will return either true or false, depending on whether the values are the same or different.', 'Worksheet functions are the built-in functions present in Excel, which will be discussed later in the session.', 'The session also covers working with formulas in Excel, including writing, editing, copying, pasting, and hiding formulas, as well as understanding operator precedence.']}, {'end': 449.15, 'start': 146.68, 'title': 'Excel formulas and functions', 'summary': 'Covers how to write, edit, copy, and hide formulas in excel, including examples and step-by-step instructions, and explains the concept of relative cell addresses and protecting the sheet.', 'duration': 302.47, 'highlights': ["The chapter explains how to write Excel formulas, including entering cell addresses or values to calculate, with an example of calculating the sum of 'a1' and 'a2' as 3+2=5.", 'It also demonstrates editing a formula in Excel, changing a sum formula to a product formula, and using the formula bar to make desired changes.', "The transcript details how to copy paste a formula in Excel, emphasizing Excel's automatic adjustment of cell references using the relative cell address system, with a step-by-step example of copying a formula to multiple cells.", 'Furthermore, it provides a guide on hiding formulas in Excel, including selecting cells, opening the font window, checking the hidden box, and protecting the sheet with a password, with a demonstration of how the formulas become hidden after protecting the sheet.']}], 'duration': 403.962, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/hRsuACY8Wdc/pics/hRsuACY8Wdc45188.jpg', 'highlights': ['Formulas in Excel are expressions that can be entered into the cells of an Excel sheet, and their outputs are displayed.', 'Excel formulas can consist of mathematical operators, values or texts, cell references, or worksheet functions.', "The chapter explains how to write Excel formulas, including entering cell addresses or values to calculate, with an example of calculating the sum of 'a1' and 'a2' as 3+2=5.", 'The session also covers working with formulas in Excel, including writing, editing, copying, pasting, and hiding formulas, as well as understanding operator precedence.', "The transcript details how to copy paste a formula in Excel, emphasizing Excel's automatic adjustment of cell references using the relative cell address system, with a step-by-step example of copying a formula to multiple cells."]}, {'end': 746.125, 'segs': [{'end': 487.712, 'src': 'embed', 'start': 449.77, 'weight': 0, 'content': [{'end': 451.27, 'text': 'Okay I hope you guys are clear with this.', 'start': 449.77, 'duration': 1.5}, {'end': 456.692, 'text': "So now let's move on and see what is the operator precedence of Excel formulas.", 'start': 451.99, 'duration': 4.702}, {'end': 463.353, 'text': 'Excel formulas basically follow the body mass or the brackets, order, division, multiplication, addition and subtraction rules.', 'start': 456.692, 'duration': 6.661}, {'end': 471.519, 'text': 'If you have a formula that contains brackets, the expression within the brackets will be solved before any other part of the complete formula.', 'start': 464.173, 'duration': 7.346}, {'end': 474.261, 'text': "say, for example I'll select a new cell and of here.", 'start': 471.519, 'duration': 2.742}, {'end': 475.522, 'text': "I'll just type in some formula.", 'start': 474.321, 'duration': 1.201}, {'end': 487.712, 'text': "I'd say equal to a 1 star and then I open up the brackets and within this I'll just say C 1 plus C 2 and then I'll close the brackets.", 'start': 476.043, 'duration': 11.669}], 'summary': 'Excel formulas follow operator precedence: brackets, order, division, multiplication, addition, and subtraction.', 'duration': 37.942, 'max_score': 449.77, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/hRsuACY8Wdc/pics/hRsuACY8Wdc449770.jpg'}, {'end': 537.019, 'src': 'embed', 'start': 495.944, 'weight': 1, 'content': [{'end': 503.708, 'text': 'So what exactly Excel is doing over here is first it determines the result of C 1 plus C 2, which is 2 plus 9 and that is 11.', 'start': 495.944, 'duration': 7.764}, {'end': 509.511, 'text': 'then it multiplies that result to 3 and hence we get the final result as 33..', 'start': 503.708, 'duration': 5.803}, {'end': 510.911, 'text': 'So I hope you guys have understood this.', 'start': 509.511, 'duration': 1.4}, {'end': 516.034, 'text': "So now let's move on towards the next topic, which is what exactly are functions in Excel.", 'start': 511.912, 'duration': 4.122}, {'end': 522.472, 'text': 'In general, a function defines a formula that is executed in some given order.', 'start': 517.25, 'duration': 5.222}, {'end': 529.116, 'text': 'Excel provides a huge number of built-in functions that can be used in order to calculate the results of various formulas,', 'start': 522.472, 'duration': 6.644}, {'end': 531.877, 'text': 'in order to view all the functions that are available in Excel.', 'start': 529.116, 'duration': 2.761}, {'end': 537.019, 'text': 'All you have to do is click on the formula menu that is present in the ribbon bar and over here.', 'start': 532.237, 'duration': 4.782}], 'summary': 'Excel calculates 2+9=11, then multiplies by 3, resulting in 33. functions in excel define formulas executed in a given order.', 'duration': 41.075, 'max_score': 495.944, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/hRsuACY8Wdc/pics/hRsuACY8Wdc495944.jpg'}, {'end': 711.359, 'src': 'embed', 'start': 680.439, 'weight': 4, 'content': [{'end': 685.34, 'text': 'Now just like the date function the day function basically returns the day value of the month.', 'start': 680.439, 'duration': 4.901}, {'end': 692.641, 'text': 'So it can be anything between 1 to 31 this function basically takes one parameter, which is the serial number.', 'start': 685.94, 'duration': 6.701}, {'end': 697.157, 'text': 'The serial number is the date whose day you want to retrieve.', 'start': 693.556, 'duration': 3.601}, {'end': 703.638, 'text': 'it can be given in any manner, such as the result of some other function supplied as a date function or the cell reference itself.', 'start': 697.157, 'duration': 6.481}, {'end': 711.359, 'text': "So what I'm going to do here is select a new cell and then I'll type down the equal to sign and I'll use the day function.", 'start': 704.338, 'duration': 7.021}], 'summary': 'The day function returns the day value of the month, ranging from 1 to 31, based on the provided serial number.', 'duration': 30.92, 'max_score': 680.439, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/hRsuACY8Wdc/pics/hRsuACY8Wdc680439.jpg'}, {'end': 750.867, 'src': 'embed', 'start': 722.276, 'weight': 3, 'content': [{'end': 726.498, 'text': 'You can also make use of the date function within the day function and to do that,', 'start': 722.276, 'duration': 4.222}, {'end': 735.821, 'text': 'just specify then equal to sign and then use the day function and within this, type down the date function and specify the parameters to it.', 'start': 726.498, 'duration': 9.323}, {'end': 741.423, 'text': 'So first is the year and then the month and then the day.', 'start': 736.601, 'duration': 4.822}, {'end': 746.125, 'text': 'Okay, so I hope you guys are clear with how this works.', 'start': 743.884, 'duration': 2.241}, {'end': 750.867, 'text': "So now let's move on and see how you can actually calculate percentage in an Excel sheet.", 'start': 746.925, 'duration': 3.942}], 'summary': 'Use date function within day function to specify year, month, and day in excel.', 'duration': 28.591, 'max_score': 722.276, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/hRsuACY8Wdc/pics/hRsuACY8Wdc722276.jpg'}], 'start': 449.77, 'title': 'Excel functions and date functions in excel', 'summary': 'Provides insights on excel operator precedence, functions, date function with three parameters, and day function that returns the day value of the month.', 'chapters': [{'end': 576.723, 'start': 449.77, 'title': 'Excel operator precedence and functions', 'summary': 'Discusses the operator precedence of excel formulas, emphasizing the sequence of operations and the importance of functions in excel, highlighting the availability and usage of built-in functions.', 'duration': 126.953, 'highlights': ['Excel follows the operator precedence of brackets, order, division, multiplication, addition, and subtraction rules, solving expressions within brackets first, as demonstrated by the example 1* (C1 + C2) resulting in 33.', 'Excel provides a wide range of built-in functions, accessible through the formula menu, allowing users to select functions from various categories and enter parameters to calculate results.']}, {'end': 746.125, 'start': 577.822, 'title': 'Excel date and day functions', 'summary': 'Covers the date function in excel, which takes three parameters (year, month, day) and returns a number representing a given date in the msxl date time format. it also discusses the day function that returns the day value of the month, with examples of usage and results shown.', 'duration': 168.303, 'highlights': ['The date function in Excel takes three parameters (year, month, day) and returns a number representing a given date in the msxl date time format, with examples of usage and results shown.', 'The day function in Excel returns the day value of the month, taking one parameter (serial number), with examples of usage and results shown.']}], 'duration': 296.355, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/hRsuACY8Wdc/pics/hRsuACY8Wdc449770.jpg', 'highlights': ['Excel follows operator precedence: brackets, order, division, multiplication, addition, and subtraction rules.', 'Demonstrated example: 1* (C1 + C2) results in 33.', 'Excel provides a wide range of built-in functions accessible through the formula menu.', 'Date function in Excel takes three parameters (year, month, day) and returns a number representing a given date.', 'Day function in Excel returns the day value of the month, taking one parameter (serial number).']}, {'end': 1012.553, 'segs': [{'end': 805.212, 'src': 'embed', 'start': 746.925, 'weight': 2, 'content': [{'end': 750.867, 'text': "So now let's move on and see how you can actually calculate percentage in an Excel sheet.", 'start': 746.925, 'duration': 3.942}, {'end': 755.616, 'text': 'percentage as we all know is the ratio calculated as a fraction of hundred.', 'start': 751.733, 'duration': 3.883}, {'end': 767.925, 'text': 'Okay, so mathematically percentage is equal to part divided by whole multiplied by hundred.', 'start': 758.018, 'duration': 9.907}, {'end': 775.03, 'text': "Okay, so now let's move on and see how you can actually do this using Excel.", 'start': 771.187, 'duration': 3.843}, {'end': 777.111, 'text': "I'll just erase these drawings over here.", 'start': 775.65, 'duration': 1.461}, {'end': 780.494, 'text': "So I'll just clear the values that are present over here.", 'start': 777.131, 'duration': 3.363}, {'end': 788.997, 'text': "So, in order to calculate the percentage, I'll just specify some new values, say 4 and 6,", 'start': 782.071, 'duration': 6.926}, {'end': 792.5, 'text': "and I'll just say that 4 is the part value and 6 is the whole value.", 'start': 788.997, 'duration': 3.503}, {'end': 798.326, 'text': 'So what I have to do over here is select the cell where I want to display the result type an equal to sign.', 'start': 793.141, 'duration': 5.185}, {'end': 804.011, 'text': "And then I'll say a 1 divided by a 2.", 'start': 798.346, 'duration': 5.665}, {'end': 805.212, 'text': "Okay, then I'll just hit enter.", 'start': 804.011, 'duration': 1.201}], 'summary': 'Learn how to calculate percentage in excel using specific values.', 'duration': 58.287, 'max_score': 746.925, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/hRsuACY8Wdc/pics/hRsuACY8Wdc746925.jpg'}, {'end': 869.097, 'src': 'embed', 'start': 825.555, 'weight': 1, 'content': [{'end': 833.337, 'text': 'is a conditional statement that returns true when a specified condition is satisfied and it returns false when the condition is not met.', 'start': 825.555, 'duration': 7.782}, {'end': 836.498, 'text': 'Excel provides a built-in if function that serves this purpose.', 'start': 833.337, 'duration': 3.161}, {'end': 839.424, 'text': 'Now to show this function to you guys.', 'start': 838.064, 'duration': 1.36}, {'end': 842.025, 'text': "what I'm going to do is go to the if function that is present.", 'start': 839.424, 'duration': 2.601}, {'end': 846.326, 'text': 'over here, select the function, and over here you can see the syntax of it.', 'start': 842.025, 'duration': 4.301}, {'end': 849.607, 'text': 'So this if function basically takes three parameters.', 'start': 846.906, 'duration': 2.701}, {'end': 854.729, 'text': 'The first is the logical test, which is the condition that is to be checked.', 'start': 850.167, 'duration': 4.562}, {'end': 858.93, 'text': 'the second parameter will return some value in case the condition is met,', 'start': 854.729, 'duration': 4.201}, {'end': 862.891, 'text': 'and the third parameter will give a separate value in case the condition is not met.', 'start': 858.93, 'duration': 3.961}, {'end': 866.012, 'text': "So I'll just take two values over here.", 'start': 864.571, 'duration': 1.441}, {'end': 868.077, 'text': "I'll say 3 and over here.", 'start': 866.032, 'duration': 2.045}, {'end': 869.097, 'text': "I'll just say XYZ.", 'start': 868.097, 'duration': 1}], 'summary': "Excel's if function operates with 3 parameters, testing conditions and providing values accordingly.", 'duration': 43.542, 'max_score': 825.555, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/hRsuACY8Wdc/pics/hRsuACY8Wdc825555.jpg'}, {'end': 944.94, 'src': 'embed', 'start': 920.276, 'weight': 0, 'content': [{'end': 926.409, 'text': 'This is also one of the most important and widely used formulas in Excel Now to show you the syntax of this function.', 'start': 920.276, 'duration': 6.133}, {'end': 930.251, 'text': "What I'm going to do is open the insert function window and over here.", 'start': 926.789, 'duration': 3.462}, {'end': 932.053, 'text': "I'll just type in vlookup.", 'start': 930.271, 'duration': 1.782}, {'end': 935.415, 'text': 'It basically comes in the lookup and reference category.', 'start': 933.053, 'duration': 2.362}, {'end': 937.576, 'text': "I'll just click V and over here.", 'start': 935.435, 'duration': 2.141}, {'end': 939.077, 'text': 'You can see the vlookup function.', 'start': 937.616, 'duration': 1.461}, {'end': 941.878, 'text': 'So here you can see the syntax of this function.', 'start': 939.897, 'duration': 1.981}, {'end': 944.94, 'text': 'So this function can take up to four parameters.', 'start': 942.519, 'duration': 2.421}], 'summary': 'Vlookup is a widely used excel formula with up to four parameters.', 'duration': 24.664, 'max_score': 920.276, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/hRsuACY8Wdc/pics/hRsuACY8Wdc920276.jpg'}], 'start': 746.925, 'title': 'Excel functions', 'summary': 'Includes demonstrations and examples of calculating percentages, using the if function, and understanding the vlookup function in excel. it covers the percentage calculation formula, if function parameters, and syntax and usage of the vlookup function with practical examples.', 'chapters': [{'end': 825.555, 'start': 746.925, 'title': 'Calculating percentage in excel', 'summary': 'Demonstrates how to calculate percentage in excel, through the formula percentage = part/whole * 100, using an example of 4 as part and 6 as whole, resulting in 66.67%. it also briefly touches on the if statement in excel.', 'duration': 78.63, 'highlights': ['The chapter demonstrates the calculation of percentage in Excel using the formula percentage = part/whole * 100, with an example of 4 as part and 6 as whole, resulting in 66.67%.', 'The demonstration includes selecting the cell, typing the formula, and formatting the result to display as a percentage using the home tab in Excel.', 'The chapter briefly touches on the if statement in Excel, indicating it as the next important function.', 'The demonstration includes clearing values, specifying new values for part and whole, and using the equal sign to perform the calculation in Excel.']}, {'end': 895.343, 'start': 825.555, 'title': 'Excel if function demo', 'summary': 'Demonstrates the excel if function, which takes three parameters: the logical test, a value to return when the condition is met, and a separate value when the condition is not met, showcasing a practical example of using the if function to check if a condition is met and return different values accordingly.', 'duration': 69.788, 'highlights': ['The if function in Excel returns true when a specified condition is met and false when it is not, with the ability to define different values for when the condition is met and when it is not.', 'The if function takes three parameters: the logical test, a value to return when the condition is met, and a separate value when the condition is not met, providing a clear structure for conditional actions.', "Practical demonstration of using the if function in Excel to check if a condition is met and return different values accordingly, exemplified by a comparison of two cell values with 'yes' and 'no' as the return values."]}, {'end': 1012.553, 'start': 896.325, 'title': 'Excel vlookup function', 'summary': 'Introduces the vlookup function, which is used to look up and retrieve specific data from an excel sheet, and explains its syntax and parameters, including a demonstration of using the function to retrieve data from a table.', 'duration': 116.228, 'highlights': ['The VLOOKUP function is used to look up and fetch specific data from an Excel sheet, and it is one of the most important and widely used formulas in Excel.', 'The syntax of the VLOOKUP function involves up to four parameters, with the fourth parameter being optional, and it is demonstrated by using the insert function window in Excel.', 'The parameters of the VLOOKUP function include the lookup value, table array, column index, and an optional range lookup parameter, which returns true for approximate values and false for an exact match.', 'A demonstration of using the VLOOKUP function to retrieve the ID of an employee named Dave is provided, showcasing the practical application of the function.', 'The VLOOKUP function successfully retrieves the ID of the specified employee, demonstrating its effectiveness and practical utility in Excel.']}], 'duration': 265.628, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/hRsuACY8Wdc/pics/hRsuACY8Wdc746925.jpg', 'highlights': ['The VLOOKUP function is one of the most important and widely used formulas in Excel.', 'The if function in Excel returns true when a specified condition is met and false when it is not.', 'The demonstration includes selecting the cell, typing the formula, and formatting the result to display as a percentage using the home tab in Excel.', 'The if function takes three parameters: the logical test, a value to return when the condition is met, and a separate value when the condition is not met.', 'The chapter demonstrates the calculation of percentage in Excel using the formula percentage = part/whole * 100, with an example of 4 as part and 6 as whole, resulting in 66.67%.']}, {'end': 1269.192, 'segs': [{'end': 1113.832, 'src': 'embed', 'start': 1034.79, 'weight': 0, 'content': [{'end': 1038.134, 'text': "So the next thing that I'll have to do is find the taxable income.", 'start': 1034.79, 'duration': 3.344}, {'end': 1042.457, 'text': 'the taxable income will be the difference between the total income and the salary deductions.', 'start': 1038.134, 'duration': 4.323}, {'end': 1046.625, 'text': 'So that will be a 1 minus a 2.', 'start': 1043.163, 'duration': 3.462}, {'end': 1047.025, 'text': "I'm sorry.", 'start': 1046.625, 'duration': 0.4}, {'end': 1049.787, 'text': "It's b 1 minus b 2 now.", 'start': 1047.145, 'duration': 2.642}, {'end': 1050.387, 'text': "I'll hit enter.", 'start': 1049.847, 'duration': 0.54}, {'end': 1054.289, 'text': 'So the total taxable income is 275 dollars.', 'start': 1051.107, 'duration': 3.182}, {'end': 1058.411, 'text': 'So the percentage of tax on my income is 10%.', 'start': 1055.129, 'duration': 3.282}, {'end': 1071.298, 'text': 'So, in order to find the income tax, what I have to do is income tax will be equal to taxable income into 10 by 100,', 'start': 1058.411, 'duration': 12.887}, {'end': 1073.64, 'text': 'which is the percentage of tax applied to my income.', 'start': 1071.298, 'duration': 2.342}, {'end': 1081.786, 'text': 'So to do this in Excel, what I have to do is just select the cell.', 'start': 1078.383, 'duration': 3.403}, {'end': 1086.91, 'text': "And here I'll type the equal to sign taxable income is present in B3.", 'start': 1083.087, 'duration': 3.823}, {'end': 1092.414, 'text': "So I'll select the cell B3 and then multiply this with 10 divided by 100.", 'start': 1087.07, 'duration': 5.344}, {'end': 1095.176, 'text': "And now I'm going to hit enter.", 'start': 1092.414, 'duration': 2.762}, {'end': 1099.642, 'text': 'So as you can see over here, I have $27.5 as my tax.', 'start': 1095.979, 'duration': 3.663}, {'end': 1102.023, 'text': 'Okay, so I hope you guys have understood this.', 'start': 1100.322, 'duration': 1.701}, {'end': 1106.467, 'text': 'So now moving on towards the next function, that is, the sum function.', 'start': 1102.864, 'duration': 3.603}, {'end': 1108.068, 'text': 'the sum function, as we all know,', 'start': 1106.467, 'duration': 1.601}, {'end': 1113.832, 'text': 'calculates the result by adding the specified values in Excel in order to show you guys the formula of this function.', 'start': 1108.068, 'duration': 5.764}], 'summary': 'Taxable income is $275, with a 10% tax rate resulting in $27.5 tax.', 'duration': 79.042, 'max_score': 1034.79, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/hRsuACY8Wdc/pics/hRsuACY8Wdc1034790.jpg'}, {'end': 1228.08, 'src': 'embed', 'start': 1200.826, 'weight': 1, 'content': [{'end': 1206.428, 'text': 'in case I want to calculate the compound interest for an investment of 500 at the rate of 10% for five years,', 'start': 1200.826, 'duration': 5.602}, {'end': 1208.729, 'text': 'assuming that the periodic payment value is zero.', 'start': 1206.428, 'duration': 2.301}, {'end': 1214.746, 'text': "All I have to do is make use of the FV function Okay, I'll just increase the size of this column over here.", 'start': 1209.169, 'duration': 5.577}, {'end': 1223.976, 'text': "So now what I'm going to do is type in equal to sign then make use of the FV function and the first value will be the rate.", 'start': 1216.428, 'duration': 7.548}, {'end': 1228.08, 'text': "So like I've already told you all before in order to calculate the rate.", 'start': 1224.937, 'duration': 3.143}], 'summary': 'Using the fv function to calculate compound interest for $500 investment at 10% rate over 5 years.', 'duration': 27.254, 'max_score': 1200.826, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/hRsuACY8Wdc/pics/hRsuACY8Wdc1200826.jpg'}], 'start': 1013.436, 'title': 'Excel tax calculations', 'summary': 'Covers calculating income tax in excel with a total taxable income of $275 and a 10% tax rate, along with the sum function and fv formula for compound interest calculations based on a 10% rate for 5 years.', 'chapters': [{'end': 1092.414, 'start': 1013.436, 'title': 'Calculating income tax in excel', 'summary': 'Explains the process of calculating income tax in excel using a specified table, with a total taxable income of $275 and a 10% tax rate applied to it.', 'duration': 78.978, 'highlights': ['The total taxable income is 275 dollars, calculated as the difference between the total income and the salary deductions.', 'The percentage of tax on the income is 10%.', 'The income tax is calculated as taxable income multiplied by 10/100.']}, {'end': 1269.192, 'start': 1092.414, 'title': 'Excel formulas: tax, sum, and compound interest', 'summary': 'Covers the calculation of tax at $27.5, the sum function for adding values in excel, and the fv formula for finding compound interest based on a 10% rate for 5 years with a periodic payment value of zero.', 'duration': 176.778, 'highlights': ['Calculation of tax at $27.5 Demonstrates the calculation of tax at $27.5, providing a clear example and amount.', 'Explanation of the sum function in Excel Explains the functionality of the sum function in Excel, including specifying parameters and demonstrating the calculation of the sum of values.', 'Demonstration of FV formula for compound interest Illustrates the use of the FV formula to find compound interest based on a 10% rate for 5 years with a periodic payment value of zero, providing step-by-step guidance on the calculation process.']}], 'duration': 255.756, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/hRsuACY8Wdc/pics/hRsuACY8Wdc1013436.jpg', 'highlights': ['The total taxable income is 275 dollars, calculated as the difference between the total income and the salary deductions.', 'Demonstration of FV formula for compound interest Illustrates the use of the FV formula to find compound interest based on a 10% rate for 5 years with a periodic payment value of zero, providing step-by-step guidance on the calculation process.', 'The percentage of tax on the income is 10%.', 'Explanation of the sum function in Excel Explains the functionality of the sum function in Excel, including specifying parameters and demonstrating the calculation of the sum of values.', 'The income tax is calculated as taxable income multiplied by 10/100.', 'Calculation of tax at $27.5 Demonstrates the calculation of tax at $27.5, providing a clear example and amount.']}, {'end': 1775.469, 'segs': [{'end': 1300.043, 'src': 'embed', 'start': 1269.813, 'weight': 0, 'content': [{'end': 1270.973, 'text': 'So the amount is present in c1.', 'start': 1269.813, 'duration': 1.16}, {'end': 1272.655, 'text': "So I'll just click on this.", 'start': 1271.034, 'duration': 1.621}, {'end': 1283.024, 'text': 'So as you can see over here the future value for $500 at the rate of 10% for five years is found to be 822.65 dollars.', 'start': 1274.617, 'duration': 8.407}, {'end': 1287.988, 'text': 'So now moving on towards the next very important formula, which is finding the average.', 'start': 1284.205, 'duration': 3.783}, {'end': 1293.558, 'text': 'Average basically depicts the median value of a number of values in Excel.', 'start': 1289.014, 'duration': 4.544}, {'end': 1300.043, 'text': 'The average can be easily calculated using a built-in function called average in order to show you the syntax of this function.', 'start': 1293.858, 'duration': 6.185}], 'summary': 'Finding future value of $500 at 10% for 5 years yields $822.65. explaining average calculation in excel.', 'duration': 30.23, 'max_score': 1269.813, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/hRsuACY8Wdc/pics/hRsuACY8Wdc1269813.jpg'}, {'end': 1402.295, 'src': 'embed', 'start': 1359.789, 'weight': 1, 'content': [{'end': 1363.63, 'text': 'So now moving on towards the next function, which is the count function.', 'start': 1359.789, 'duration': 3.841}, {'end': 1368.09, 'text': 'the count function of Excel will count the number of cells containing numbers in a given range.', 'start': 1363.63, 'duration': 4.46}, {'end': 1372.271, 'text': 'So in case I want to calculate the number of cells holding numbers in the table.', 'start': 1368.89, 'duration': 3.381}, {'end': 1382.719, 'text': "I've just created all I have to do is select a new cell then specify the count function and within this I'll specify this table as a range.", 'start': 1372.291, 'duration': 10.428}, {'end': 1391.867, 'text': 'So as you can see it has returned the value to be 12, which means 12 cells in my table contain number values.', 'start': 1385.561, 'duration': 6.306}, {'end': 1393.828, 'text': 'So I hope you guys are clear with this.', 'start': 1392.567, 'duration': 1.261}, {'end': 1398.512, 'text': 'So now moving on towards the next function, which is the round function.', 'start': 1394.609, 'duration': 3.903}, {'end': 1402.295, 'text': 'the round function basically rounds off values to some specific decimal places.', 'start': 1398.512, 'duration': 3.783}], 'summary': 'Excel count function returns 12 cells with numbers in given range.', 'duration': 42.506, 'max_score': 1359.789, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/hRsuACY8Wdc/pics/hRsuACY8Wdc1359789.jpg'}, {'end': 1471.932, 'src': 'embed', 'start': 1445.112, 'weight': 3, 'content': [{'end': 1449.335, 'text': 'So here we found the average now in case I want to grade these students.', 'start': 1445.112, 'duration': 4.223}, {'end': 1452.157, 'text': "I'll have to make use of the nested if statements.", 'start': 1449.896, 'duration': 2.261}, {'end': 1453.698, 'text': "So I'll just create a new column.", 'start': 1452.477, 'duration': 1.221}, {'end': 1457.982, 'text': "I'll say grade and within this I'll use the if function first.", 'start': 1454.159, 'duration': 3.823}, {'end': 1462.747, 'text': "So the if function like I've already told you,", 'start': 1460.566, 'duration': 2.181}, {'end': 1468.45, 'text': "will take one condition and it's going to return some value if it is true and another value if it is false.", 'start': 1462.747, 'duration': 5.703}, {'end': 1471.932, 'text': 'So now I just want to check if G2 is greater than 75.', 'start': 1469.31, 'duration': 2.622}], 'summary': 'Using nested if statements to grade students based on g2 scores.', 'duration': 26.82, 'max_score': 1445.112, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/hRsuACY8Wdc/pics/hRsuACY8Wdc1445112.jpg'}, {'end': 1552.957, 'src': 'embed', 'start': 1523.393, 'weight': 4, 'content': [{'end': 1524.973, 'text': 'Okay, so I hope you guys are clear with this.', 'start': 1523.393, 'duration': 1.58}, {'end': 1527.695, 'text': 'Let me just copy this for the other student as well.', 'start': 1525.654, 'duration': 2.041}, {'end': 1531.896, 'text': 'So now moving on towards the next function, which is the rank function.', 'start': 1528.815, 'duration': 3.081}, {'end': 1538.559, 'text': "So till now you've been seeing how to calculate the average of a student and then find the grade now.", 'start': 1532.757, 'duration': 5.802}, {'end': 1542.101, 'text': "I'll also show you guys how to give ranks to the students present in a class.", 'start': 1538.779, 'duration': 3.322}, {'end': 1548.052, 'text': 'To do that, you can make use of the rank function, which is already present in Excel.', 'start': 1543.126, 'duration': 4.926}, {'end': 1552.957, 'text': 'the rank function basically takes three parameters, which is the reference number and order.', 'start': 1548.052, 'duration': 4.905}], 'summary': 'Teaching how to use rank function in excel for student grades and ranks.', 'duration': 29.564, 'max_score': 1523.393, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/hRsuACY8Wdc/pics/hRsuACY8Wdc1523393.jpg'}, {'end': 1701.956, 'src': 'heatmap', 'start': 1664.787, 'weight': 5, 'content': [{'end': 1671.3, 'text': 'And the criteria is going to be if the values are greater than 70.', 'start': 1664.787, 'duration': 6.513}, {'end': 1678.342, 'text': 'So as you can see it has returned 10 as the output which means 10 cells contain values that are greater than 70.', 'start': 1671.3, 'duration': 7.042}, {'end': 1684.063, 'text': "So now moving on towards the last function that I'm going to be discussing in this session, which is the index function.", 'start': 1678.342, 'duration': 5.721}, {'end': 1689.025, 'text': 'the index function returns a value or cell reference at some particular position in a specified range.', 'start': 1684.063, 'duration': 4.962}, {'end': 1692.446, 'text': 'The index function can be used in two forms.', 'start': 1690.165, 'duration': 2.281}, {'end': 1694.006, 'text': 'The first is the array form.', 'start': 1692.746, 'duration': 1.26}, {'end': 1701.956, 'text': 'In this form if both the row and the column number are supplied, it returns the value that is present at the intersection of both the cells.', 'start': 1694.773, 'duration': 7.183}], 'summary': '10 cells contain values greater than 70; index function retrieves value at specified position.', 'duration': 41.553, 'max_score': 1664.787, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/hRsuACY8Wdc/pics/hRsuACY8Wdc1664787.jpg'}, {'end': 1741.797, 'src': 'heatmap', 'start': 1709.66, 'weight': 0.782, 'content': [{'end': 1720.085, 'text': 'the index function returns the reference of the cell where the row and the column values intersect area number will indicate the range which is to be used in case multiple ranges have been supplied.', 'start': 1709.66, 'duration': 10.425}, {'end': 1728.487, 'text': "So just to show you guys some example of this I'll specify the array to be between a3 and i3 and for the row and column values.", 'start': 1721.081, 'duration': 7.406}, {'end': 1732.049, 'text': "I'll just say 2 comma 3 you can give any values of your choice.", 'start': 1728.507, 'duration': 3.542}, {'end': 1733.911, 'text': 'These are just some random values.', 'start': 1732.069, 'duration': 1.842}, {'end': 1741.797, 'text': 'So as you can see the value that is present in the second row and the third column in this table is 85.', 'start': 1734.671, 'duration': 7.126}], 'summary': 'The index function returns the cell reference where row and column values intersect, with an example resulting in a value of 85.', 'duration': 32.137, 'max_score': 1709.66, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/hRsuACY8Wdc/pics/hRsuACY8Wdc1709660.jpg'}], 'start': 1269.813, 'title': 'Excel formulas and functions', 'summary': 'Covers the application of excel formulas and functions, including future value calculation, average, count, round, and nested if statements, with examples such as finding the future value of $500 at 10% for 5 years, calculating the average test marks for students, counting cells with numbers, rounding off decimal values, and grading students based on their scores.', 'chapters': [{'end': 1523.393, 'start': 1269.813, 'title': 'Excel formulas and functions', 'summary': 'Covers the application of excel formulas and functions, including future value calculation, average, count, round, and nested if statements, with examples such as finding the future value of $500 at 10% for 5 years, calculating the average test marks for students, counting cells with numbers, rounding off decimal values, and grading students based on their scores.', 'duration': 253.58, 'highlights': ['The future value for $500 at the rate of 10% for five years is found to be 822.65 dollars. Calculating future value using the given parameters ($500, 10% interest rate, and 5 years) resulting in 822.65 dollars.', 'The count function returns the value to be 12, which means 12 cells in my table contain number values. Using the count function to determine that 12 cells in the table contain numerical values.', 'The average of the test marks for Dave is found, and the function is copied to find the average for another student. Calculating the average test marks for a student (Dave) and replicating the process for another student.', 'Using the round function to round off 83.4 to 83. Demonstrating the use of the round function to round 83.4 to the nearest whole number, resulting in 83.', 'Using nested if statements to grade students based on their test scores. Employing nested if statements to assign grades based on test scores, with conditions for A, B, and F grades.']}, {'end': 1775.469, 'start': 1523.393, 'title': 'Excel functions: rank, count if, index', 'summary': 'Covers the usage of excel functions such as rank, count if, and index, demonstrating how to calculate ranks, count cells based on conditions, and retrieve values or cell references at specific positions in a range.', 'duration': 252.076, 'highlights': ['The rank function in Excel can be used to assign ranks to students based on their scores, with the ability to specify ascending or descending order, providing a practical demonstration of assigning ranks to students based on their scores.', 'The count if function in Excel enables the counting of cells that satisfy a given condition within a specified range, demonstrated by counting cells with values greater than 70, resulting in 10 cells meeting the specified condition.', 'The index function in Excel can be employed to retrieve values or cell references at specific positions in a specified range, with the ability to return values based on array form or reference form, illustrated by retrieving a specific value at the intersection of a specified row and column in a table.']}], 'duration': 505.656, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/hRsuACY8Wdc/pics/hRsuACY8Wdc1269813.jpg', 'highlights': ['Calculating future value using the given parameters ($500, 10% interest rate, and 5 years) resulting in 822.65 dollars.', 'Using the count function to determine that 12 cells in the table contain numerical values.', 'Demonstrating the use of the round function to round 83.4 to the nearest whole number, resulting in 83.', 'Employing nested if statements to assign grades based on test scores, with conditions for A, B, and F grades.', 'Providing a practical demonstration of assigning ranks to students based on their scores.', 'Demonstrated by counting cells with values greater than 70, resulting in 10 cells meeting the specified condition.', 'Illustrated by retrieving a specific value at the intersection of a specified row and column in a table.']}], 'highlights': ['Covers essential excel formulas and functions, including mathematical operators, cell references, date functions, if and vlookup functions, tax calculations, and practical demonstrations.', 'Excel provides convenience for formulating and utilizing equations, enhancing data utilization.', 'Introduction to working with Excel formulas and functions for online training certification in trending technologies.', 'Excel formulas can consist of mathematical operators, values or texts, cell references, or worksheet functions.', "The chapter explains how to write Excel formulas, including entering cell addresses or values to calculate, with an example of calculating the sum of 'a1' and 'a2' as 3+2=5.", 'The session also covers working with formulas in Excel, including writing, editing, copying, pasting, and hiding formulas, as well as understanding operator precedence.', 'Excel follows operator precedence: brackets, order, division, multiplication, addition, and subtraction rules.', 'Demonstrated example: 1* (C1 + C2) results in 33.', 'Excel provides a wide range of built-in functions accessible through the formula menu.', 'The VLOOKUP function is one of the most important and widely used formulas in Excel.', 'The if function in Excel returns true when a specified condition is met and false when it is not.', 'Demonstration of FV formula for compound interest Illustrates the use of the FV formula to find compound interest based on a 10% rate for 5 years with a periodic payment value of zero, providing step-by-step guidance on the calculation process.', 'The total taxable income is 275 dollars, calculated as the difference between the total income and the salary deductions.', 'Demonstration of assigning ranks to students based on their scores.', 'Calculating future value using the given parameters ($500, 10% interest rate, and 5 years) resulting in 822.65 dollars.', 'Using the count function to determine that 12 cells in the table contain numerical values.', 'Demonstrating the use of the round function to round 83.4 to the nearest whole number, resulting in 83.', 'Employing nested if statements to assign grades based on test scores, with conditions for A, B, and F grades.']}