Basic Excel: Working with Formulas and Functions CHAPTER - 1: INTRODUCING FORMULAS AND FUNCTIONS Structure 1.0 Learning Objectives 1.1 Introduction 1.2 What is a formula? 1.3 How to enter a formula? 1.4 What is a function? 1.5 How to enter a function? 1.6 Different Functions in Excel 1.7Summary 1.8References 1.0 Learning Objectives After studying this unit, you will be able to: Explain What is a formula in MS Excel Explain how to enter formulas in MS Excel Explain What is a function in MS Excel Explain how to enter functions in MS Excel Explain various functions in MS Excel 1.1 Introduction Formulas and functions are the building blocks of working with numeric data in Excel. Formulas and functions are the bread and butter of Excel. A function is a Page 1 of 63 All Rights Reserved. Vol. TLE001/03-2022
Basic Excel: Working with Formulas and Functions predefined formula that performs calculations using specific values in a particular order. All spreadsheet programs include common functions that can be used for quickly finding the sum, average, count, maximum value, and minimum value for a range of cells. 1.2 What is a formula? Formulas in excel is an expression that operates on values in a range of cell addresses and operators. For example, =A1+A2+A3, which finds the sum of the range of values from cell A1 to cell A3. A formula in Excel is an expression that returns a specific result. For example, =1+2. Note: all formulas in Excel must begin with an equal‘s sign (=). Cell references In the examples above, values are \"hardcoded\". That means results won't change unless you edit the formula again and change a value manually. Generally, this is considered bad form, because it hides information and makes it harder to maintain a spreadsheet. Instead, use cell references so values can be changed at any time. In the screen below, C1 contains the following formula: =A1+A2+A3 Notice because we are using cell references for A1, A2, and A3, these values can be changed at any time and C1 will still show an accurate result. Page 2 of 63 All Rights Reserved. Vol. TLE001/03-2022
Basic Excel: Working with Formulas and Functions 1.3 How To Enter a Formula To enter a formula: Select a cell Enter an equals sign (=) Type the formula, and press enter. Instead of typing cell references, you can point and click, as seen below. Note references are color-coded: All formulas in Excel must begin with an equals sign (=). No equals sign, no formula: How to change a formula : To edit a formula, you have 3 options: Select the cell, edit in the formula bar Double-click the cell, edit directly Select the cell, press F2, edit directly No matter which option you use, press Enter to confirm changes when done. If you want to cancel and leave the formula unchanged, click the Escape key Page 3 of 63 All Rights Reserved. Vol. TLE001/03-2022
Basic Excel: Working with Formulas and Functions 1.4 What is a Function? Working in Excel, you will hear the words \"formula\" and \"function\" used frequently, sometimes interchangeably. They are closely related, but not exactly the same. Technically, a formula is an expression that begins with an equal sign (=). A function, on the other hand, is a formula with a special name and purpose. In most cases, functions have names that reflect their intended use. For example, you probably know the SUM function already, which returns the sum of given references: =SUM(1,2,3) // returns 6 =SUM(A1:A3) // returns A1+A2+A3 The AVERAGE function, as you would expect, returns the average of given references: =AVERAGE(1,2,3) // returns 2 And the MIN and MAX functions return the minimum and maximum values, respectively: =MIN(1,2,3) // returns 1 =MAX(1,2,3) // returns 3 Function arguments Most functions require inputs to return a result. These inputs are called \"arguments\". A function's arguments appear after the function name, inside parentheses, separated by commas. All functions require a matching opening and closing parentheses (). The pattern looks like this: =FUNCTIONNAME(argument1,argument2,argument3) Page 4 of 63 All Rights Reserved. Vol. TLE001/03-2022
Basic Excel: Working with Formulas and Functions 1.5 How to Enter a Function? If you know the name of the function, just start typing. Here are the steps: Enter equals sign (=) and start typing. Excel will list of matching functions based as you type: When you see the function you want in the list, use the arrow keys to select (or just keep typing). Type the Tab key to accept a function. Excel will complete the function: Fill in required arguments: Press Enter to confirm formula: Page 5 of 63 All Rights Reserved. Vol. TLE001/03-2022
Basic Excel: Working with Formulas and Functions Function Description 1.6 Different Functions in Excel Page 6 of 63 All Rights Reserved. Vol. TLE001/03-2022
Basic Excel: Working with Formulas and Functions =AND Returns TRUE or FALSE based on two or more conditions =AVERAGE Calculates the average (arithmetic mean) =AVERAGEIF Calculates the average of a range based on a TRUE or FALSE =AVERAGEIFS condition Calculates the average of a range based on one or more =COUNT TRUE/FALSE conditions Counts cells with numbers in a range =COUNTA Counts all cells in a range that has values, both numbers, and letters =COUNTBLANK Counts blank cells in a range =COUNTIF Counts cells as specified =COUNTIFS Counts cells in a range based on one or more TRUE or FALSE conditions =IF Returns values based on a TRUE or FALSE conditions =IFS Returns values based on one or more TRUE or FALSE conditions =MAX Returns the highest value in a range =MEDIAN Returns the middle value in the data Returns the lowest value in a range =MIN Finds the number seen most times. The function always returns a =MODE single number =OR Returns TRUE or FALSE based on two or more conditions =STDEV.P Calculates the Standard Deviation (Std) for the entire population =STDEV.S Calculates the Standard Deviation (Std) for a sample =SUM =SUMIF Adds together numbers in a range Calculates the sum of values in a range based on a TRUE or =SUMIFS FALSE condition Calculates the sum of a range based on one or more TRUE or =VLOOKUP FALSE conditions =XOR Allows vertical searches for values in a table Returns TRUE or FALSE based on two or more conditions 1.7 Summary Page 7 of 63 All Rights Reserved. Vol. TLE001/03-2022
Basic Excel: Working with Formulas and Functions Formulas and functions are the building blocks of working with numeric data in Excel. A function is a predefined formula that performs calculations using specific values in a particular order. Formulas in excel is an expression that operates on values in a range of cell addresses and operators. A function, on the other hand, is a formula with a special name and purpose. Most functions require inputs to return a result. These inputs are called \"arguments\". 1.8 References https://exceljet.net/excel-formulas-and-functions https://www.excel-easy.com/introduction/formulas-functions.html https://www.guru99.com/introduction-to-formulas-and-functions-in- excel.html#1 https://support.microsoft.com/en-us/office/overview-of-formulas-in-excel- ecfdc708-9162-49e8-b993-c311f47ca173 https://www.w3schools.com/EXCEL/excel_formatting.php CHAPTER - 2: USING FORMULAS FOR COMMON MATHEMATICAL OPERATIONS Page 8 of 63 All Rights Reserved. Vol. TLE001/03-2022
Basic Excel: Working with Formulas and Functions Structure 2.0 Learning Objectives 2.1 Count and sums 2.2 Count, Count A & COUNTBLANK 2.3 SUM, SUMI, SUMIFS 2.4 Summary 2.5 References 2.0 Learning Objectives After studying this unit, you will be able to: Explain Counting and Summing in MS Excel? Explain Count, CountA, Countblank in MS Excel Explain Sum, Product, SumProduct in MS Excel 2.1 COUNT and SUM The \"Sum\" and \"Count\" functions in Excel offer two different ways of looking at the numbers in your spreadsheet. If you use both functions to address the same range of cells, each one will return a different result in most cases. In Excel, a grouping of several cells is called a \"Range.\" When you enter a formula, such as \"Sum\" or \"Count,\" you need to specify a range of cells you want the function to calculate. Another common Excel term is \"Value.\" The value is the data you enter into a cell. A value can be numbers, letters, or combinations of the two. Page 9 of 63 All Rights Reserved. Vol. TLE001/03-2022
Basic Excel: Working with Formulas and Functions 2.2 COUNT, COUNTA & COUNTBLANK COUNT: The count function is MS Excel is very important. This function allows you to count the number of cells. The function has multiple variants that allow conditional counting. The count function is the basic function used for counting cells having numerical values. It doesn‘t count cells with non-numeric data. The syntax for the Count function is: =Count(―Cell Range‖) Page 10 of 63 All Rights Reserved. Vol. TLE001/03-2022
Basic Excel: Working with Formulas and Functions In the above figure, the total count is 6 which means, the formula has only counted cells with numeric values. CountA The CountA function is the basic function used for counting cells having all values. It doesn‘t count blank cells data. The syntax for CountA function is: =CountA(―Cell Range‖) Page 11 of 63 All Rights Reserved. Vol. TLE001/03-2022
Basic Excel: Working with Formulas and Functions In the above figure, the total count is 9 which means, the formula has counted all cells with values. Please note that this function doesn‘t count blank cells. CountBlank The CountBlank function is the basic function used for counting cells that are blank. It doesn‘t count cells with values. The syntax for CountBlank function is: =CountBlank(―Cell Range‖) Page 12 of 63 All Rights Reserved. Vol. TLE001/03-2022
Basic Excel: Working with Formulas and Functions In the above figure, the total count is 3 which means, the formula has only counted cells that are blank. 2.3 SUM, SUMIF & SUMIFS SUM The sum function is MS Excel is very important. This function allows you to sum the values of cells. The function has multiple variants that allow conditional sum. The SUM function is the basic function used for adding cells having numerical values. The syntax for SUM function is: =SUM(―Cell Range‖) Page 13 of 63 All Rights Reserved. Vol. TLE001/03-2022
Basic Excel: Working with Formulas and Functions SUMIF SUMIF function is a conditional sum function. It add values in a given range on the basis of given criteria. The syntax for SUMIF function is =SUMIF(―Range‖, ―Criteria‖, ―Sum Range‖). In the above figure, Range is defined from A1 to A9, whereas Criteria is set to Red and the Sum range is set to B1 to B9. So now the formula will look for the word Red in the given range and will sum the adjacent cells in Column B. Page 14 of 63 All Rights Reserved. Vol. TLE001/03-2022
Basic Excel: Working with Formulas and Functions In the above figure, the total sum is 179 which means, the word Red has occurred 3 times in the given range and the sum of its adjacent cells is 179. SUMIFS SUMIFS function is a conditional SUM function. It adds values in given ranges on the basis of given criteria. This function support multiple criteria for multiple range and the AND operation is performed between these criteria‘s. The syntax for sumifs function is =Sumifs(―SUM Range‖, ―Range 1‖, ―Criteria 1‖, ―Cell Range 2‖, ―Criteria 2‖, ….). In the above figure, Range 1 is defined from A1 to A9, whereas Criteria 1 is set to ―Red similarly, Range 2 is defined from B1 to B9 and Criteria 2 is set as White and finally the SUM Range is set to C1 to C9. So now the formula will look for the word Page 15 of 63 All Rights Reserved. Vol. TLE001/03-2022
Basic Excel: Working with Formulas and Functions Red in Range 1 along with having White in Range 2 and will perform sum function on cells where both criteria have been met. In the above figure, the total sum is 99 which means, the word Red and word White has combined occurrence on2 points. Product The PRODUCT function is useful when you need to multiply many cells together. For example, the formula =PRODUCT(A1:A3, C1:C3) is equivalent to =A1 * A2 * A3 * C1 * C2 * C3. Syntax: PRODUCT(number1, [number2], ...) SUMPRODUCT The SUMPRODUCT function returns the sum of the products of corresponding ranges or arrays. The default operation is multiplication, but addition, subtraction, and division are also possible. In this example, we'll use SUMPRODUCT to return the total sales for a given item and size: Page 16 of 63 All Rights Reserved. Vol. TLE001/03-2022
Basic Excel: Working with Formulas and Functions SUMPRODUCT matches all instances of Item Y/Size M and sums them, so for this example 21 plus 41 equals 62. Syntax: To use the default operation (multiplication): =SUMPRODUCT(array1, [array2], [array3], ...) 2.4 Summary The count function is MS Excel is very important. This function allows you to count the number of cells. The CountA function is the basic function used for counting cells having all values. It doesn‘t count blank cells data. The CountBlank function is the basic function used for counting cells that are blank. It doesn‘t count cells with values. The sum function is MS Excel is very important. This function allows you to sum the values of cells. SUMIF function is a conditional sum function. It add values in a given range on the basis of given criteria. SUMIFS function is a conditional SUM function. It adds values in given ranges on the basis of given criteria. This function support multiple criteria Page 17 of 63 All Rights Reserved. Vol. TLE001/03-2022
Basic Excel: Working with Formulas and Functions for multiple range and the AND operation is performed between these criteria‘s. The PRODUCT function is useful when you need to multiply many cells together. The SUMPRODUCT function returns the sum of the products of corresponding ranges or arrays. 2.5 References https://www.techwalla.com/articles/the-difference-in-sum-vs-count-in-excel https://www.certiology.com/tutorials/excel-tutorial/count-and-sum-function- in-excel.html http://alqashi.com/book/book18.pdf https://support.microsoft.com/en-us/office/using-calculation-operators-in- excel-formulas-78be92ad-563c-4d62-b081-ae6da5c2ca69 https://edu.gcfglobal.org/en/excel2013/simple-formulas/1/ Page 18 of 63 All Rights Reserved. Vol. TLE001/03-2022
Basic Excel: Working with Formulas and Functions CHAPTER - 3: USING FORMULAS TO MANIPULATE TEXT Structure 3.0 Learning Objectives 3.1 Introduction 3.2 How text is handled? 3.3 Compare two text strings in excel 3.4 Separate/Split text in excel 3.5 Summary 3.6 References 3.0 Learning Objectives After studying this unit, you will be able to: Explain how text is handled in MS Excel? Explain how to combine text in MS Excel Explain Separate/ Split text in MS Excel 3.1 Introductions Excel can perform an array of basic math functions, and the articles listed below will show you how to create the necessary formulas to add, subtract, multiply, or divide numbers. You can subtract, divide, multiply, and add in Excel within the cells of a spreadsheet. You can also do exponents, change the order of operations, and do various mathematical functions in Excel. These features rely on cell references to other cells to make calculations. Page 19 of 63 All Rights Reserved. Vol. TLE001/03-2022
Basic Excel: Working with Formulas and Functions 3.2 How Text is Handled? TEXT is simply the group of characters and strings of characters which convey the information about the different data and numbers in Excel. Every character is connected with a code [ANSI]. The text comprises the individual entity character which is the smallest bit that would be found in Excel. We can perform the operations on the strings [Text] or the characters Characters are not limited to A to Z or a to z but many symbols are also included. Text is an inactive number type[format] in excel. Anything stored as text [number or date] won‘t respond to any standard formulas or functions but specially designed text functions. [exceptions do occur in the case of numbers] If we need to make anything inactive, such as the Date to be non-responding to the calculation, we put it as a text. Similarly, if we want to avoid any calculations for a number it needs to be put as a text. 3.3 Compare Two Text Strings in Excel This situation can occur when we want to create some logical condition and check whether the two text strings are the same or not. Now the same can have two meanings. 1. Just the content is the same. For example ―TEXT‖ and ―text‖. 2. Exactly same [ Content is same and the case is same]. For example, ―teXT‖ and ―text. COMPARING THE TEXT FOR THE CONTENT We are going to compare just the content and not the case. This can be used simply in Excel. We can simply compare the cells by using the formula =Cell 1 containing text=cell 2 containing text. Page 20 of 63 All Rights Reserved. Vol. TLE001/03-2022
Basic Excel: Working with Formulas and Functions This formula will return TRUE if the content is the same [ignoring the case] and false if the content is not the same. COMPARING THE TEXT FOR THE EXACT CONTENT [CONTENT AND CASE]: We are going to compare the two text strings exactly for the content and the case. This is also not difficult. We will make use of the function called EXACT (text 1, text 2). It compares the text to be exactly the same and returns true if the texts have the same content and same case. [EXACT TAKES ONLY TWO TEXTS AT A TIME]. We can simply compare the cells by using the formula = EXACT (Cell 1 containing text, cell 2 containing text) The formula will return ‗TRUE‘ if both the text strings are the same otherwise ‗FALSE‘. Page 21 of 63 All Rights Reserved. Vol. TLE001/03-2022
Basic Excel: Working with Formulas and Functions COMBINE TEXT IN EXCEL Combining text is excel is very useful technique. Let us understand what we mean by combining the text. Combining means to stick the text to one another. For example, if one cell is having ―ABCD‖ and the other is having the value of ―EFGH‖. If we combine them in the third cell the result will be ―ABCDEFGH‖. 3.4 Separate/Split Text in Excel SPLITTING/SEPARATING the text is also required many times in Excel. This can be stated as removing any portion of the text or string from the cell and placing it into the different cells. Splitting and separating the text can be done in many ways. TEXT TO COLUMN We can split the text in a cell into different columns. The basis of the separation can be any delimiter like a comma, colon, etc. USING FLASH FILL Separating a single word into many portions. Page 22 of 63 All Rights Reserved. Vol. TLE001/03-2022
Basic Excel: Working with Formulas and Functions Separating Text on the basis of any character or pattern 3.5 Summary Excel can perform an array of basic math functions, and the articles listed below will show you how to create the necessary formulas to add, subtract, multiply, or divide numbers. TEXT is simply the group of characters and strings of characters that convey the information about the different data and numbers in Excel. Separate/ Split can be stated as removing any portion of the text or string from the cell and placing it into the different cells. 3.6 References https://gyankosh.net/exceltricks/manipulating-text-in-excel-part-i/ https://www.vertex42.com/blog/excel-formulas/text-formulas-in-excel.html https://www.universalclass.com/articles/computers/excel/excel-2013-how- to-manipulate-text.htm https://www.oreilly.com/library/view/excel-2016- bible/9781119067511/c11.xhtml Page 23 of 63 All Rights Reserved. Vol. TLE001/03-2022
Basic Excel: Working with Formulas and Functions CHAPTER - 4: USING FORMULAS WITH DATES AND TIME Structure 4.0 Learning Objectives 4.1 Introduction 4.2 Date Function 4.3 Time Function 4.4 Summary 4.5 References 4.0 Learning Objectives After studying this unit, you will be able to: Explain the date function in MS Excel? Explain the Time function in MS Excel 4.1 Introduction Excel Date and Time functions can be used to extract information from, and perform operations on, Excel Dates and Times. Some of the Excel Date & Time functions were introduced to Excel 2010 or Excel 2013, so are not available in earlier versions of Excel. You can find this information in the applicability section of the function. 4.2 Date Function The DATE function gives \"the sequential serial number that represents a particular date. Use it when you have to take three different values and merge them to form a date. Page 24 of 63 All Rights Reserved. Vol. TLE001/03-2022
Basic Excel: Working with Formulas and Functions The DATE function has the following syntax: =DATE(YEAR, MONTH, DAY). To understand this function, consider the example given below. A B CDE SR. NO. YEAR MONTH DAY RESULT 1 2019 08 19 ? 2 2019 11 5? 3 2019 10 5? Put the formula *=DATE(B1, C1, D1)in cellE1` and then apply it in subsequent rows. The result will be updated as shown below: A B CD E SR. NO. YEAR MONTH DAY RESULT 1 2019 08 19 19-08-2019 2 2019 11 5 05-11-2019 3 2019 10 5 05-10-2019 The DATEVALUE Function: The DATEVALUE function is used \"to convert a date that is available as text to a serial number that will be recognized as a date by Excel‖. The DATEVALUE function has the following syntax: =DATEVALUE(date_in_the_text_format) To implement the DATEVALUE function, consider the example given below: A B C Sr. No. Data Text Result 1 ―05-11-2019‖ =DATEVALUE(―05-11-2019‖) 2 ―18-08-2019‖ =DATEVALUE(―18-08-2019‖) Page 25 of 63 All Rights Reserved. Vol. TLE001/03-2022
Basic Excel: Working with Formulas and Functions 3 ―19-08-2019‖ =DATEVALUE(―19-08-2019‖) The result will be updated as shown below: A B C SR. NO. DATE_TEXT RESULT 1 \"05-11-2019\" 43774 2 \"18-08-2019\" 43695 3 \"19-08-2019\" 43696 4.3 Time Function Returns the decimal number for a particular time. If the cell format was General before the function was entered, the result is formatted as a date. The decimal number returned by TIME is a value ranging from 0 (zero) to 0.99988426, representing the times from 0:00:00 (12:00:00 AM) to 23:59:59 (11:59:59 P.M.). Syntax: TIME(hour, minute, second) The TIME function syntax has the following arguments: Hour: Required a number from 0 (zero) to 32767 represents the hour. Any value greater than 23 will be divided by 24 and the remainder will be treated as the hour value. For example, TIME(27,0,0) = TIME(3,0,0) = .125 or 3:00 AM. Minute: Required a number from 0 to 32767 representing the minute. Any value greater than 59 will be converted to hours and minutes. For example, TIME(0,750,0) = TIME(12,30,0) = .520833 or 12:30 PM. Page 26 of 63 All Rights Reserved. Vol. TLE001/03-2022
Basic Excel: Working with Formulas and Functions Second: Required a number from 0 to 32767 representing the second. Any value greater than 59 will be converted to hours, minutes, and seconds. For example, TIME(0,0,2000) = TIME(0,33,22) = .023148 or 12:33:20 AM 4.4 Summary Excel Date and Time functions can be used to extract information from, and perform operations on, Excel Dates and Times. The DATE function gives \"the sequential serial number that represents a particular date. Time Function states that ―Returns the serial number of a particular time‖. 4.5 References https://support.microsoft.com/en-us/office/time-function-9a5aff99-8f7d- 4611-845e-747d0b8d5457 https://www.tutorialspoint.com/advanced_excel_functions/advanced_exc el_date_time_functions.htm https://www.excel-easy.com/functions/date-time-functions.html https://www.pluralsight.com/guides/working-with-time-date-functions- excel-part-1 https://support.microsoft.com/en-us/office/date-and-time-functions- reference-fd1b5961-c1ae-4677-be58-074152f97b81 Page 27 of 63 All Rights Reserved. Vol. TLE001/03-2022
Basic Excel: Working with Formulas and Functions CHAPTER - 5: USING FORMULAS FOR CONDITIONAL ANALYSIS Structure 5.0 Learning Objectives 5.1 Introduction 5.2 Conditional Formulas 5.3 Single condition - if functions 5.4 Single Condition - NOT, AND and OR Functions 5.5 Multiple Conditions - IF Function (nested) 5.6 Summary 5.7 References 5.0 Learning Objectives After studying this unit, you will be able to: Explain What is Conditional Formulas in MS Excel? Explain the Single condition - if functions in MS Excel Explain the Multiple Conditions - IF Function (nested) in MS Excel 5.1 Introduction Excel provides several worksheet functions for performing conditional analysis. You'll use some of those functions in this chapter. Conditional analysis means performing different actions depending on whether a condition is met. A condition is a value or expression that returns TRUE or FALSE. Based on the value of the condition, a formula can branch into two separate calculations. That is, when the condition returns TRUE, one value or expression is evaluated while the other is ignored. A FALSE condition reverses the flow of the formula, and the first value or expression is ignored while the other is evaluated. Page 28 of 63 All Rights Reserved. Vol. TLE001/03-2022
Basic Excel: Working with Formulas and Functions 5.2 Condition Formulas If you want to perform a calculation when a cell contains a specific value you can create a conditional formula. A conditional formula returns one value if the condition is True and a different value if the condition is False. A conditional formula can be created in several different ways. There are 3 useful functions that can be used to create conditional formulas. The IF function can return a value based on whether a certain condition is True or False. The IFS function can return a value based on multiple conditions. The SWITCH function can return a value based on a list of exact matches. 5.3 Single Condition - IF Functions You can use any combination of cell references, operators, constants and even other functions to create this logical test. The following Operators can be used to generate True or False values. These operators include: greater than (>), less than (<) and equal to (=). Page 29 of 63 All Rights Reserved. Vol. TLE001/03-2022
Basic Excel: Working with Formulas and Functions 5.4 Single Condition - NOT, AND And OR Functions These three functions are often included in the \"logical_test\" for slightly more advanced conditions. Cell E3 - Return the opposite of a True or False value you would use the NOT function. Cell E4 - Return the logical AND for two different conditions you would use the AND function. Cell E5 - Return the logical OR for two different conditions you would use the OR function. 5.5 Multiple Conditions - IF Function (Nested) If you want to check for multiple conditions you can use \"nested\" IF functions. When you nest the IF functions, you specify an IF function as the value of either of the \"value_if_true\" or \"value_if_false\". arguments You can combine or nest up to 64 levels of IF functions within one formula. Multiple Conditions - IFS Function Page 30 of 63 All Rights Reserved. Vol. TLE001/03-2022
Basic Excel: Working with Formulas and Functions The IFS function can return a value based on multiple conditions. If you want to perform multiple conditions, instead of using nested IF functions you could use the IFS function. Multiple Conditions - SWITCH Function The SWITCH function can return a value based on a list of exact matches. 5.6 Summary Conditional analysis means performing different actions depending on whether a condition is met. A condition is a value or expression that returns TRUE or FALSE. Based on the value of the condition, a formula can branch into two separate calculations. A conditional formula returns one value if the condition is True and a different value if the condition is False. When you nest the IF functions, you specify an IF function as the value of either of the \"value_if_true\" or \"value_if_false\". The IFS function can return a value based on multiple conditions. The SWITCH function can return a value based on a list of exact matches. 5.7 References https://bettersolutions.com/excel/formulas/conditional.htm https://support.microsoft.com/en-us/office/create-conditional-formulas- ca916c57-abd8-4b44-997c-c309b7307831 https://exceljet.net/formula/conditional-mode-with-criteria Page 31 of 63 All Rights Reserved. Vol. TLE001/03-2022
Basic Excel: Working with Formulas and Functions https://www.real-statistics.com/excel-capabilities/excel-conditional- functions/ https://www.oreilly.com/library/view/excel-2019- bible/9781119514787/c13.xhtml https://onlinelibrary.wiley.com/doi/10.1002/9781119549376.ch13 Page 32 of 63 All Rights Reserved. Vol. TLE001/03-2022
Basic Excel: Working with Formulas and Functions CHAPTER - 6: USING FORMULAS FOR MATCHING AND LOOKUPS Structure 6.0 Learning Objectives 6.1 Introduction 6.2 Match Function 6.3 Index Function 6.4 LOOKUP function 6.5 Summary 6.6 References 6.0 Learning Objectives After studying this unit, you will be able to: Explain the match function in MS Excel? Explain the Index function in MS Excel Explain the LookUp function MS Excel 6.1 Introduction INDEX and MATCH is the most popular tool in Excel for performing more advanced lookups. This is because INDEX and MATCH is incredibly flexible – you can do horizontal and vertical lookups, 2-way lookups, left lookups, case- sensitive lookups, and even lookups based on multiple criteria. If you want to improve your Excel skills, INDEX and MATCH should be on your list. Page 33 of 63 All Rights Reserved. Vol. TLE001/03-2022
Basic Excel: Working with Formulas and Functions 6.2 Match Function The MATCH function searches for a specified item in a range of cells, and then returns the relative position of that item in the range. For example, the MATCH function below looks up the value 53 in the range B3:B9. Syntax: MATCH(lookup_value, lookup_array, [match_type]) The MATCH function syntax has the following arguments: lookup_value Required the value that you want to match in lookup_array. For example, when you look up someone's number in a telephone book, you are using the person's name as the lookup value, but the telephone number is the value you want. The lookup_value argument can be a value (number, text, or logical value) or a cell reference to a number, text, or logical value. lookup_array: Required the range of cells being searched. match_type: Optional the number -1, 0, or 1. The match_type argument specifies how Excel matches lookup_value with values in lookup_array. The default value for this argument is 1. Page 34 of 63 All Rights Reserved. Vol. TLE001/03-2022
Basic Excel: Working with Formulas and Functions 6.3 Index Functions The INDEX function in Excel is fantastically flexible and powerful, and you'll find it in a huge number of Excel formulas, especially advanced formulas. Actually, in a nutshell index retrieves the value at a given location in a range. For example, let's say you have a table of planets in our solar system (see below), and you want to get the name of the 4th planet, Mars, with a formula. You can use INDEX like this: =INDEX(B3:B11,4) INDEX returns the value in the 4th row of the range. INDEX gets a value at a given location in a range of cells based on numeric position. When the range is one-dimensional, you only need to supply a row number. When the range is two-dimensional, you'll need to supply both the row and column number. 6.4 Lookup Functions Use LOOKUP, one of the lookup and reference functions, when you need to look in a single row or column and find a value from the same position in a second row or column. Page 35 of 63 All Rights Reserved. Vol. TLE001/03-2022
Basic Excel: Working with Formulas and Functions For example, let's say you know the part number for an auto part, but you don't know the price. You can use the LOOKUP function to return the price in cell H2 when you enter the auto part number in cell H1. Use the LOOKUP function to search one row or one column. In the above example, we're searching prices in column D. There are two ways to use LOOKUP: Vector form and Array form Vector form: Use this form of LOOKUP to search one row or one column for a value. Use the vector form when you want to specify the range that contains the values that you want to match. For example, if you want to search for a value in column A, down to row 6. Page 36 of 63 All Rights Reserved. Vol. TLE001/03-2022
Basic Excel: Working with Formulas and Functions Array form: The array form is provided for compatibility with other spreadsheet programs, but it's functionality is limited.An array is a collection of values in rows and columns (like a table) that you want to search. For example, if you want to search columns A and B, down to row 6. LOOKUP will return the nearest match. To use the array form, your data must be sorted. Syntax: LOOKUP(lookup_value, lookup_vector, [result_vector]) The LOOKUP function vector form syntax has the following arguments: lookup_value Required. A value that LOOKUP searches for in the first vector. Lookup_value can be a number, text, a logical value, or a name or reference that refers to a value. lookup_vector Required. A range that contains only one row or one column. The values in lookup_vector can be text, numbers, or logical values. result_vector Optional. A range that contains only one row or column. The result_vector argument must be the same size as lookup_vector. It has to be the same size. Page 37 of 63 All Rights Reserved. Vol. TLE001/03-2022
Basic Excel: Working with Formulas and Functions 6.5 Summary The MATCH function searches for a specified item in a range of cells, and then returns the relative position of that item in the range. In a nutshell index retrieves the value at a given location in a range. Use LOOKUP functions, when you need to look in a single row or column and find a value from the same position in a second row or column. 6.6 References https://exceljet.net/index-and-match https://www.excel-easy.com/examples/index-match.html https://www.ablebits.com/office-addins-blog/excel-match-function/ https://support.microsoft.com/en-us/office/match-function-e8dffd45-c762- 47d6-bf89-533f4a37673a https://support.microsoft.com/en-us/office/lookup-function-446d94af-663b- 451d-8251-369d5e3864cb Page 38 of 63 All Rights Reserved. Vol. TLE001/03-2022
Basic Excel: Working with Formulas and Functions CHAPTER - 7: USING FORMULAS FOR FINANCIAL ANALYSIS Structure 7.0 Learning Objectives 7.1 Introduction 7.2 Financial Analysis in MS Excel 7.3 Present Values of a series of Future Payments 7.4 What is EMI? 7.5 Summary 7.6 References 7.0 Learning Objectives After studying this unit, you will be able to: Explain Financial Analysis in MS Excel? Explain Present Values of a series of Future Payments Explain What is EMI and how to calculate it? 7.1 Introduction Spreadsheets got their start in the accounting and finance departments back when it was all done with paper and pencil. And even though Excel has grown far beyond a simple electronic ledger sheet, it's still a required tool in business.yu In this chapter, you'll look at some formulas commonly used in accounting, finance, and other areas of businesses. Page 39 of 63 All Rights Reserved. Vol. TLE001/03-2022
Basic Excel: Working with Formulas and Functions 7.2 Financial Analysis in Excel Microsoft Excel gives to an user the whole toolkit for analyzing the financial performance of an enterprise, performing statistical calculations and forecasting. Built-in functions, formulas, add-ins allow you to automate the lion's share of the work. Thanks to automation, an user needs only to substitute new data and on their basis automatically generated ready reports, which a lot of people make for hours. You can perform financial analysis with Excel in an easy way. Excel provides you several financial functions such as PMT, PV, NPV, XNPV, IRR, MIRR, XIRR, and so on that enable you to quickly arrive at the financial analysis results. 7.4 Present Values of a Series of Future Payments The present value is the total amount that a series of future payments is worth now. You can calculate the present value using the Excel functions − PV − Calculates the present value of an investment by using an interest rate and a series of future payments (negative values) and income (positive values). At least one of the cash flows must be positive and at least one must be negative. NPV − Calculates the net present value of an investment by using a discount rate and a series of periodic future payments (negative values) and income (positive values). XNPV − Calculates the net present value for a schedule of cash flows that is not necessarily periodic. You can use Excel function PV: PV (rate, nper, pmt, [fv ], [type]) 7.5 What is EMI? An Equated Monthly Installment (EMI) is defined as \"A fixed payment amount made by a borrower to a lender at a specified date each calendar Page 40 of 63 All Rights Reserved. Vol. TLE001/03-2022
Basic Excel: Working with Formulas and Functions month. Equated monthly installments are used to pay off both interest and principal each month, so that over a specified number of years, the loan is paid off in full.\" In Excel, you can calculate the EMI on a loan with the PMT function. For example, Suppose, you want to take a home loan of 5000000 with an annual interest rate of 11.5% and the term of the loan for 25 years. You can find your EMI as follows − - Calculate interest rate per month (Interest Rate per Annum/12) - Calculate number of monthly payments (No. of years * 12) - Use PMT function to calculate EMI - You will get the following results: Page 41 of 63 All Rights Reserved. Vol. TLE001/03-2022
Basic Excel: Working with Formulas and Functions 7. 6 Summary Spreadsheets got their start in the accounting and finance departments back when it was all done with paper and pencil. Microsoft Excel gives to an user the whole toolkit for analyzing the financial performance of an enterprise, performing statistical calculations and forecasting. You can perform financial analysis with Excel in an easy way. Excel provides you several financial functions such as PMT, PV, NPV, XNPV, IRR, MIRR, XIRR, and so on that enable you to quickly arrive at the financial analysis results. An Equated Monthly Installment (EMI) is defined as \"A fixed payment amount made by a borrower to a lender at a specified date each calendar month. 7.7 References http://alqashi.com/book/book18.pdf https://www.oreilly.com/library/view/excel-2019- bible/9781119514787/c15.xhtml https://exceltable.com/en/analyses-reports/financial-analysis-example https://www.tutorialspoint.com/excel_data_analysis/excel_data_analysis_ financial.htm Page 42 of 63 All Rights Reserved. Vol. TLE001/03-2022
Basic Excel: Working with Formulas and Functions CHAPTER - 8: USING FORMULAS FOR STATISTICAL ANALYSIS Structure 8.0 Learning Objectives 8.1 Introduction 8.2 Tests performed using Excel statistical Analysis 8.3 Summary 8.4 References 8.0 Learning Objectives After studying this unit, you will be able to: Explain Statistical Analysis? Explain the Varieties Tests performed 8.1 Introduction You can perform statistical analysis with the help of Excel. It is used by most of the data scientists who require the understanding of statistical concepts and behaviour of the data. Before starting, you need to check whether Excel Analysis ToolPak is enabled in Excel or not (it is an add-in provided by Microsoft Excel). To check whether it is enabled or not, go to Excel → Data and check whether data analysis option is there or not on the top right corner. If it is not there, go to Excel → File → Options → Add-in and enable the Analysis ToolPak by selecting the Excel Add-ins option in manage tab and then, click GO. This will open a small window; select the Analysis ToolPak option and enable it. Page 43 of 63 All Rights Reserved. Vol. TLE001/03-2022
Basic Excel: Working with Formulas and Functions 8.2 Tests Performed Using Excel Statistical Analysis Descriptive Analysis You can find descriptive analysis by going to Excel→ Data→ Data Analysis → Descriptive statistics. It is the most basic set of analysis that can be performed on any data set. It gives you the general behaviour and pattern of the data. It is helpful when you a have a set of data and want to have the summary of that dataset. This will show the following statistic data for the chosen dataset. - Mean, Standard error and Median - Median, Mode and Standard Deviation - Sample Variance - Kurtosis and Skewness - Range, Minimum, Maximum, Sum and Count ANOVA (Analysis Of Variance) It is a data analysis method which shows whether the mean of two or more data set is significantly different from each other or not. In other words, it analyses two or more groups simultaneously and finds out whether any relationship is there among the groups of data set or not. For example, you can use ANOVA if you want to analyse the traffic of three different cities and find out which one is more efficient in handling the traffic (or if there are no significant differences among the traffic). You will find three types of ANOVA in the Excel - ANOVA single factor - ANOVA two factor with replication - ANOVA two factor without replication If you have three groups of datasets and want to check whether there is any significant difference between these groups or not, you can use ANOVA single factor. Page 44 of 63 All Rights Reserved. Vol. TLE001/03-2022
Basic Excel: Working with Formulas and Functions If the P-value in the ANOVA summary table is greater than 0.05, you can say that there is a significant difference between the groups. Moving Average Moving average is usually applicable for time series data such as stock price, weather report, attendance in class etc. For example, it is heavily used in stock price as a technical indicator. If you want to predict the stock price of today, the last ten days data would be more relevant than the last 1 year. So, you can plot the moving average of the stock having a 10-day time period and you can then predict the price to some extent. The same applies to the temperature of a city. The recent temperature of a city can be calculated by taking the average of last few weeks rather than previous months. Rank and Percentile It calculates the ranking and percentile in the data set. For example, if you are managing a business of several products and want to find out which product is contributing to a higher revenue, you can use this rank method in Excel. Regression Regression is a process of establishing a relationship among many variables. Usually, we establish a relationship between dependent variables and independent variables. For example, cases when you want to see if there is any increase in the revenue of product, which is not due to increase in the advertisement. Random Number Generator Although you can find a simple function to generate a series of random numbers, this option in data analysis gives you more flexibility in the random number generation process. It gives us more control over the generated data. Sampling This option is the data analysis tool which is used for creating samples from a huge population. Page 45 of 63 All Rights Reserved. Vol. TLE001/03-2022
Basic Excel: Working with Formulas and Functions You can randomly select data from the dataset or select every nth item from the set. For example, if you want to measure the effectiveness of a call centre employee in a call centre, you can use this tool to randomly select few data every month and listen to their recorded calls and give a rating based on the selected call. 8.3 Summary You can perform statistical analysis with the help of Excel It is used by most of the data scientists who require the understanding of statistical concepts and behaviour of the data. There are various tests mentioned in this chapter that requires excel to perform Statistical Analysis 8.4 References https://www.geeksforgeeks.org/statistical-functions-in-excel-with- examples/ https://magoosh.com/excel/using-excel-statistical-analysis/ https://support.microsoft.com/en-us/office/statistical-functions-reference- 624dac86-a375-4435-bc25-76d659719ffd https://openoregon.pressbooks.pub/beginningexcel/chapter/2-2-statistical- functions/ Page 46 of 63 All Rights Reserved. Vol. TLE001/03-2022
Basic Excel: Working with Formulas and Functions CHAPTER - 9: USING FORMULAS WITH TABLES AND CONDITIONAL FORMATTING Structure 9.0 Learning Objectives 9.1 Tables 9.2 Differences between Tables and Ranges 9.3 Conditional Formatting 9.4 Summary 9.5 References 9.0 Learning Objectives After studying this unit, you will be able to: Explain Tables in MS Excel Explain the Difference between Tables and Ranges in MS Excel Explain the Conditional Formatting 9.1 Tables A Table is a rectangular range of structured data. The key features are – - Each row in the table corresponds to a single record of the data. Example - Employee information. - Each column contains a specific piece of information. Exmaple - The columns can contain data such as name, employee number, hire date, salary, department, etc. - The top row describes the information contained in each column and is referred to as header row. - Each entry in the top row is referred to as column header. Page 47 of 63 All Rights Reserved. Vol. TLE001/03-2022
Basic Excel: Working with Formulas and Functions You can create and use an Excel table to manage and analyze data easily. Further, with Excel Tables you get built-in Filtering, Sorting, and Row Shading that ease your reporting activities. Further, Excel responds to the actions performed on a table intelligently. For example, you have a formula in a column or you have created a chart based on the data in the table. When you add more data to the table (i.e., more rows), Excel extends the formula to the new data and the chart expands automatically. Create Table: To create a table from the data you have on the worksheet, follow the given steps Step 1: Select the Range of Cells that you want to include in the Table. Cells can contain data or can be empty. The following Range has 290 rows of employee data. The top row of the data has headers. Step 2: Under the Insert tab, in the Tables group, click Tables. The Create Table dialog box appears. Check that the data range selected in the Where is the data for your table? Box is correct. Step 3: Check the My table has headers box if the top row of the selected Range contains data that you want to use as the Table Headers. Note: If you do not check this box, your table will have Headers – Column1, Column2, … Page 48 of 63 All Rights Reserved. Vol. TLE001/03-2022
Basic Excel: Working with Formulas and Functions Step 4: Click OK. Step 5: You can also convert a range to a table by clicking anywhere on the range and pressing Ctrl+T. 9.2 Difference between Tables and Ranges Following are the differences between a table and range − A table is a more structured way of working with data than a range. You can convert a range into a table and Excel automatically provides - a Table Name - Column Header Names - Formatting to the Data (Cell Color and Font Color) for better Visualization Tables provide additional features that are not available for ranges. These are: Excel provides table tools in the ribbon ranging from properties to styles. Excel automatically provides a Filter button in each column header to sort the data or filter the table such that only rows that meet your defined criteria are displayed. If you have multiple rows in a table, and you scroll down the sheet so that the header row disappears, the column letters in the worksheet are replaced by the table headers. When you place a formula in any cell in a column of the table, it gets propagated to all the cells in that column. You can use table name and column header names in the formulas, without having to use cell references or creating range names. You can extend the table size by adding more rows or more columns by clicking and dragging the small triangular control at the lower-right corner of the lower-right cell. You can create and use slicers for a table for filtering data. Page 49 of 63 All Rights Reserved. Vol. TLE001/03-2022
Basic Excel: Working with Formulas and Functions 9.3 Conditional Formatting In Microsoft Excel, you can use Conditional Formatting for data visualization. You have to specify formatting for a cell range based on the contents of the cell range. The cells that meet the specified conditions would be formatted as you have defined. Excel applies the format you chose, i.e. the green color to all the cells that satisfy the condition. If the content of a cell does not satisfy the condition, the formatting of the cell remains unchanged. The result is as expected, only for the salespersons who have met the target, the cells are highlighted in green – a quick visualization of the analysis results. You can specify any number of conditions for formatting by specifying Rules. You can pick up the rules that match your conditions from Highlight cells rules Top / Bottom rules You can also define your own rules. You can − Add a rule Clear an existing rule Manage the defined rules Further, you have several formatting options in Excel to choose the ones that are appropriate for your Data Visualization − Data Bars Color Scales Icon Sets 9.4 Summary A Table is a rectangular range of structured data. You can create and use an Excel table to manage and analyze data easily. Further, with Excel Tables you get built-in Filtering, Sorting, and Row Shading that ease your reporting activities. A table is a more structured way of working with data than a range. Page 50 of 63 All Rights Reserved. Vol. TLE001/03-2022
Search
