Know Your Functions with MS Excel | MKCL India | Skillshare

Know Your Functions with MS Excel

MKCL India, Creating A Knowledge Lit World

Know Your Functions with MS Excel

MKCL India, Creating A Knowledge Lit World

Play Speed
  • 0.5x
  • 1x (Normal)
  • 1.25x
  • 1.5x
  • 2x
11 Lessons (2h 6m)
    • 1. Course Outline for Know Your Functions

      6:34
    • 2. Function Basic

      5:12
    • 3. Logical Function

      18:31
    • 4. Mathematical Functions

      12:20
    • 5. Financial Functions

      8:03
    • 6. Lookup and Reference Function

      9:34
    • 7. LOOKUP, VLOOKUP and HLOOKUP Functions

      19:03
    • 8. Arrays

      12:42
    • 9. Miscellaneous Functions- part 1

      11:41
    • 10. Miscellaneous Function- part 2

      15:59
    • 11. Error finding function

      6:49
  • --
  • Beginner level
  • Intermediate level
  • Advanced level
  • All levels
  • Beg/Int level
  • Int/Adv level

Community Generated

The level is determined by a majority opinion of students who have reviewed this class. The teacher's recommendation is shown until at least 5 student responses are collected.

117

Students

--

Projects

About This Class

Mastering MS Excel is rarely this easy and fast. We have come up with this course which will help you learn the more advanced functions and complex formulas used MS Excel skills by investing just over a couple of hours

Build a strong foundation in Excel and spreadsheet processing using complex functions and formulas and perform the most complex calculations, quickly and effortlessly! 

What will be covered in the course?

Functions is what makes Excel smart. In this course you will learn all about using functions. For those of you who haven’t used functions before, you will learn the basic fundamentals about them as well. 

At the end of this course you would have learnt about:

  1. Basic Functions

  2. Nested Functions

  3. Displaying and Analysing Functions

  4. Logical Functions

  5. Mathematical Functions

  6. Financial Functions

  7. Lookup and Reference Functions,

  8. Arrays, and

  9. Dynamic Formulas

Content and Overview (Now this is the important part)

Based on our vast training experience and a lot of research on MS Excel usage trends across industries, we have identified Excel functions that provide maximum utility with the least effort. We have focused on key functions from each category. Familiarity with these gets you set for effortless use of other similar ones. 


The simple training style will enable you to master seemingly hard-to-grasp concepts in a very short while. This course will cover functions under broad categories. 


You must have often spent hours trying to identify missing data fields or non-standard data. A special section has been devoted to “Information Finding Functions”. So you will learn to do previously cumbersome tasks in just a few seconds!


Another problem area is the annoying errors that pop up in spreadsheets. If a single cell involved in a calculation has an error, the dependent formula goes completely topsy-turvy. Not anymore! With knowledge of error checking functions, you can ensure that errors are nipped in the bud and all calculations go through smoothly.


So this course is also going to help you resolve problems in a jiffy! In most cases, since the root cause of the errors is eliminated, the problems won’t occur at all.

What are the requirements?

No specific eligibility requirements. This course is for anyone and everyone who is looking to learn the more advanced formulas and functions in Excel to increase their productivity. Very basic knowledge of Excel is required to enroll for this course. 

When you learn this course, we strongly recommend that you create those projects simultaneously for an effective learning experience. 

Who will benefit the most from this course?

The ideal student for this course is anyone and everyone who wants to learn the advanced functions and formulas in Excel to increase their productivity.

This course will be particularly helpful for 

  1. Individuals in supervisory roles, 

  2. Sales and Marketing professionals, 

  3. Human Resources and Payroll Professionals,

  4. Supply Chain Management analysts,

  5. Financial Analysts,

  6. Bankers,

  7. Students looking for a career in the fields of Data Analysis and Processing, and

  8. Data Analysts

  9. Students learning Excel, 

  10. High School and College students, 

Meet Your Teacher

Teacher Profile Image

MKCL India

Creating A Knowledge Lit World

Teacher

Maharashtra Knowledge Corporation Limited

MKCL is a eLearning company with more than 10 million learners for our courses. We are based in Pune, Maharashtra, India. We have over 16 years of experience in the field of e-Learning, e-Governance, and e-Empowerment technologies, solutions and services and are promoted by the Department of Higher and Technical Education (H & TE), Government of Maharashtra (GoM), India.

Our expertise is in creating eLearning courses which are self paced and rich in instructional design. We focus on the principles of 'learning by doing' in the courses by teaching the concepts and the tools while creating projects or assets.

See full profile

Class Ratings

Expectations Met?
  • Exceeded!
    0%
  • Yes
    0%
  • Somewhat
    0%
  • Not really
    0%
Reviews Archive

In October 2018, we updated our review system to improve the way we collect feedback. Below are the reviews written before that update.

Your creative journey starts here.

  • Unlimited access to every class
  • Supportive online creative community
  • Learn offline with Skillshare’s app

Why Join Skillshare?

Take award-winning Skillshare Original Classes

Each class has short lessons, hands-on projects

Your membership supports Skillshare teachers

Learn From Anywhere

Take classes on the go with the Skillshare app. Stream or download to watch on the plane, the subway, or wherever you learn best.

phone

Transcripts

1. Course Outline for Know Your Functions: - Thanks . - All right. 2. Function Basic: objectives. By the end, you should be able to define function, stayed the parts off a function, explain how to insert functions, list the categories off functions, described how to search for a specific function. Functions form a very important part in Excel. It is what makes Excel one off the most powerful productivity software suits available for business use. Function is nothing but pretty find formula that performs calculations using specific values provided by the user. And in a particular order, Excel includes many common functions, like some average count etcetera, which are performed on the range of cells. Now, in order to use functions correctly, one need to understand the different parts off of functions and how to create arguments to calculate values and sell references. Now let's see what the parts off a function are. In order for a function to work correctly, it must be written in specifically the way in which it is written is called as a syntax. The basic syntax off a function is as follows. You have to write equal sign in a cell where we want a function, then the function name, for example. We can use some we can use average and one or more arguments. Arguments are nothing but references to the cells, which contain information that we want to calculate. Ah, function in an example given below would add the values off the cell even to a 20. Arguments can refer to both individual cells and to sell ranges, as we wanted in the parentis is one can use a single argument on multiple arguments. Depending on our requirement. Let us take a small example to demonstrate this. I have written some random numbers in these cells. Now, if I want to calculate some off these cells, which are highlighted, I would write equal to some open parentis. Is Sylhet the cells with the mouse, close parenthesis and enter Now hear what Excel has done is it has added all these numbers . If we look at the formula bar over here at the top, it is summing up the sin. Now this is a type off a single argument. What if I want a number off arguments to be added in a function that's easily possible? Let's look at the same example. What if I want to add these numbers with these two numbers and this number I would write in the cell equal to some open parentis is with my mouth. I'm selecting these cells. I will put a comma Sylhet, the other cells put a comma and select the last cell with close parentis is I would press enter Now. Here I can see that I can add a number off arguments and calculate these values. This was a simple way to add a few numbers. Now Excel has about 100 different types of functions in it. Do I have to memorize all the functions in order to use them? Not really. Excel has a simple way to go about it. It has classified the functions in different categories. On the top ribbon, you can go to the formulas stab and below the formula stab. We can see that there are more number off functions which are categorized as auto. Some recently used financial logical text related functions, date and time related functions look up and reference, mathematics and trigonometry and more functions. So with the stab, we can easily figure out which functions we want to use. One can insert a function by looking from these categories or by selecting insert function and searching for the function that we want. Once we start making our own functions, it could become easier for us to memorize fuel recently used functions, and we would be able to quickly form functions right from the Excel sheet just the way I showed you. Summary. You have learned what is a function parts off a function inserting functions, categories of functions, searching for a function. 3. Logical Function: object IBS. By the end, you should be able to use logical functions and, if not and are use a combination off functions. Use nested if functions. Use the some and average functions. Use the auto. Some icon use the formula. Text function used the sum if and some ifs functions. Analyze functions using the functions argument window. Now we will see how to use some off the logical functions that I have mentioned in Excel like and if not are, and we will try to get some interesting outputs out off them by mixing one function with the other in the sense we will use an if function inside another. If function, I will try a combination off an R and and function together and see what interesting output we can make. Let us begin. Let us consider an example. I like to order number of things online, even groceries, and I like to keep a track off when I ordered them and when I received them and the total cost that I spent on them. And now here is my food supply inventory. I have ordered some tomatoes, black beans, all purpose flour, etcetera and the quantity for the same I have mentioned with price per unit. Now what I want to do is to calculate the total cost. So I would write equal to quantity that is cell B three into price per unit. That is cell C three and hit on Ente. After getting the total cost, I would select this cell and clicking on this small box with the left mouse button. I would drag it down till the last cell, and I can see that Excel has calculated the total off all the quantity and the price and has given me the total cost off each. Next is I want to know how many items have I ordered. So in this cell I would write equal to some opened racket and select all these cells blues bracket and enter. Now I get the number off items that I have ordered. Another way to go about. This is by going to the top over here and selecting this auto some button. It automatically understands how many cells about it toe include for calculating the total , and it is showing over here with the dotted lines moving around the cells selected, I will simply hit on enter and I can see the total quantity ordered Average price per unit can be calculated by writing equal to a V E r a g e average open bracket and selecting all these cells close bracket and hidden Enter. Next is total cost. Total cost is equal to some open bracket Some off all these cells close bracket and enter with this. My basic sheet is ready for further analysis. Now Let us challenge excel and see if it can help us understand certain hypothetical scenarios that we create. The first scenario is I want to find out if the quantity off items ordered by me it is more than 50 or not. So for that, what I'll do is equal to a function open bracket. By the way, what we can also do is when we write if and we can see that Excel has given us three options Over here, we can choose the option by hitting on the tab key. So now let us right further we have If the condition is that this cell is greater than 50 and if that is so, then excel should tell me that I have exceeded my budget and if that is not the case and if the number off items ordered is less than or equal to 50 then it should tell me that I am within my budget. And then that in place I will close the bracket and hit on Enter. So yes, it is telling me that I have exceeded my budget. Let us analyze dysfunction by going toe the formula bar and clicking on effects. Oh, here it tells me that the logical test is the cell be 16. That is the quantity off items ordered. If this condition is true, then it will tell me you have exceeded your budget else. If it is false, then it will tell you that you are within your budget. If we are writing any textual message in excel as an output, we need to put it in double coats. And that's what I have done over here. Had it been a numerical value, then we would have simply written like this. I will change the first part also. I will write one if it is greater than 50 and zero. If it is not. And now it has showing that I have exceeded number. So this way we can use the if function, I will just get back to the previous sentence format. No, with this basic if sentence in place, I would like to go further and find out if my total cost is exceeding my budget or not. For that, I would write a similar sentence equal to if pressing the tap key. This particular cell is greater than 500. If that is so, I would like excel to tell me that my total cost is exceeding. And if that is not the case, then my total cost is not exceeding my budget Close bracket and inter. So here also Excel tells me that my total budget is getting exceeded. I will just change this total value to 1000. So now it is telling me that the total cost is not exceeding your budget. What if we want to find out in just one cell that a quantity ordered for a total cost is not exceeding my budget and vote have different conditions, the quantity ordered. If it is more than 50 then it should tell me that it is exceeding my budget and the total cost if it is more than 1000. Then it should tell me that I am exceeding my total cost. So this is a classic case off an artist statement in order to frame and our statement Michelle right equal to more logical one That is this greater than 50 mama. Because now we want to write another condition. Logical too. Is this greater than 1000 for the output of this statement will be either true or false. In our case, it is true because the quantity off items ordered is more than 50 now. Had this condition being more than 100 then this condition would have changed to false. What if I see that instead off an art statement I would like to know if my quantity ordered and the total cost both are more than a prescribed limit. For example, the quantity ordered is more than 50 and the total cost is going more than 1000. In that case, let us see and and condition we would require an and formula and we'll write it this way. Equal toe and logical one this condition greater than 50 mama this condition greater than 1000 close bracket and enter. And now when we will present a it will tell us that this is false. Why? Because although the quantity off items ordered is more than 50 the cost is not going to more than 1000. Hence the condition is false. Had this total been more than 1000 say for example, 1100. Then the condition would have converted to true. Now let us complicate this a little bit more. What we will do now is we will try to write an R statement inside an if condition. What we want to find out is if the quantity off items ordered is more than 50 or the total cost is more than 500 then it should tell me that I have exceeded my budget instead off writing these two If conditions it would be great if we could merge it in one like this equal to if are logic one Is this cell greater than 50 or this cell is greater than 500. If this is true, then tell me that I have exceeded else. Tell me that I have not exceeded close bracket ente. So here we can see a very simple example off using an AR statement inside and if function similarly, we could have also put an and function inside and if function like this. So here it tells me that I have exceeded 50 in the quantity off items ordered and also have exceeded 500 in the total cost off items and hence it is showing that output is exceeded. Now let us try to write a formula in which there is an if condition inside another, if condition such kind of formulas unknown as nested. If let me create a scenario for you, I want to know if my number off items ordered a more than 50 and the total cost is going more than 500 then I want Excel to tell me that the number off items is more and the total cost is also more else. If the items are more than 50 but the cost is less than 500 then it should tell me accordingly. Also, if the items are less than 50 it should tell that the number off items is less than 50. In order to write this in a formula, please go like this equal to if the first test is whether the quantity is more than 50 or not, and If this is true, then I would like to check whether the total cost is more than 500 north. In order to put that we would write. If this is greater than 500 then it should tell me that the quantity is more and the cost is more else. It would tell me that the quantity is more but the cost is less close. The bracket and the last condition is that the quantity is less close bracket enter In our case, it is telling me yes, the quantity is more than 50 and the cost is more than 50. And it is showing like this We will try different scenarios. What if the total cost was less than 500? Say, for 50. So now this condition has changed. The quantity is more but the cost is less. What if the quantity off items ordered is less than 50? Say 45. So here it is showing that the quantity is less. In order to go deep inside dysfunction and understand it, we can click this FX and it will tell us that the first condition is the B 16 greater than 50. And then if it is true. Then there is another if condition, which say's that if d 18 that is the total cost is more than 500 than it should show a particular value and if it is less, then it should show a particular value else. If this condition the first condition, is false, then it will directly go over here and it will show that the quantity is less like this. You conform, different, nested if conditions and create a formula that is designed by you. We have another interesting logical operator known as the not function. What this function does is it converts a logical output c a true statement into false and vice versa. Let us see how let's take average price off unit this time will write this in north statement like this equal toe not C 17 greater than 10 in this case, What it will do is we know that it is greater than 10. So it should have bean a true if we would have written and if condition. But if I hit on enter, it will tell me false. That means it is just converting a true statement toe false statement and representing this can be really useful in some off the scenarios 1 may require to see what Formula One has written in any particular cell. Excel has provided such a condition, and that, too, is a formula. For example, if I want to see what formula I have written over here, I would have to go to the top and see in the function bar. Instead of that, I could write a formula next to it. Equal toe formula, select formula text. Select the joining cells, F 23 close bracket and India. Here It converts the formula that was there in the cell and write it as a text. This can be very helpful in some off the cases. Let us see how to use some if and some ifs function. What some if does is it adds up a number off cells based on one single criteria, and what some ifs does is it adds a number of cells based on multiple criteria. Let us take a very small example. I want to add the total cost off all those items. Who's quantity is more than five. In order to write this in a scenario I would write equal to some if select the range that is quantity on which we want to put some criteria comma the criteria in double courts greater than five comma The column where we want to some close bracket and enter. So here we can see the total cost off all the items Let us now create a criteria in which we want to find out the some off the total cost off all the items where the quantity is more than fight and the price per unit is less than 10. We will write equal to some ifs Open bracket. Now here there is a difference if some if we had written the summing Lynch at the end But for some X function we need to write this summing range first. That is the cells that will be added up coma, the cells on which we would like to put some criteria. So here I will select the quantity column, Mama, I will put the criteria for the same greater than five second the range of cells on which I would like to put some other criteria comma. And here I would write the criteria, see less than fight close bracket ender. No, here, we can see that Excel is adding up all these cells wherever the quantity is more than five . That is 123 and four cells, and the price per unit is less than five. That is. Among these cells, only the cells that satisfy the condition are these two, and hence the get the some summary. You have learned logical functions and, if not and are using a combination of functions. Nestor. If functions, some and average functions using the auto, some button formula, text function, some if and some ifs function. Analyzing the functions using function argument window. 4. Mathematical Functions: objectives. By the end, you should be able to use the average more and median functions. Use Excel for area and volume calculations. Use the round down, round up and round function. Use the mot and iron T functions. Use the rand and rand between functions. Excel can be very effectively used to perform a number off mathematical calculations. Let us look at some off the functions in excel that are capable of doing so. Let's consider an example in this stable. I have written a number of companies and their respective turnover. We will try to find out the average sternal off. These companies, average or mean can be calculated by adding all the values in the observation and dividing by the number off observations. In our case, we would be adding all the values. I am just using an auto some function and dividing this obtain value by the number off observations. That is 13. This is the average off the turnover. We could have also done this using an inbuilt formula in excel, which is average. Let us right over here equal to average. Now, these numbers could have been selected one by one. Our ah better way to do it is by selecting all these cells together, closing the bracket and hit on Ente no V c that this value and this value are the same. Let us try to find out. The more off these observations a mood is nothing but a value that has occurred Maximum number of times in this data Excel can directly find out that particular value for us. Let us right moored and select all the cells, just as we did before, close bracket and in tow from the observation. Also, we can see that 22,000 has appeared twice in this data and none off. The other values have appeared twice and hence the output is 22,000. Median is nothing but the central value off the data. That is, if we organized this data in ascending order, we have done from smallest to largest, then the middle value off. The observation is a median. In our case, we have 13 observations, so the median value could be seventh observation, hence one to to the cool five, six, seven. This will be our median. Let us try to find that directly from the formula equal to median and select all the numbers, just as we did before. Clues bracket and enter. So we see that Excel has very well calculated average mode and median. Now let us see how we can use Excel, toe, calculate area, surface area, circumference and volume. And for that, we need to know the formulas for each of these for the object that we want to calculate. Let us take an example. Suppose we want to find area off a square. We know that the formula for it is the side square. Let us consider that we got the side of values as 40. So in order to find the area off this we would write Equal toe will select the cell, which contains decide and then Reese to. That is by holding on the shift key and will press numerical key six. After that, we'll write to So this shows that we are raising the side to the power off to and when we hit on enter, we get the value off area. No. Similarly, we could have calculated the area off a rectangle by multiplying the length and the breadth . There is say, Lent is 25 the bread is 13. So will ride the formula equal to selecting the cell that contains the length in tow, the cell that contains the breath and hit on enter. So this is how we can find the area off rectangle. Similarly, we could find out value off perimeter volume circumference off the object that we want to find while performing mathematical calculations. Reno that we may not get an output like this or we may not get an output that is an anti jer. Always we may have values in decimal just like this. 45.67249 I am just taking a random number. Now, if we get such a value and we would like to use only a number of decimal places, then Excel has certain functions for that. If you won't do around this digit to the lower value, then we could use round down and select the number mentioned the number or digits that we would like to round down and hit on India. So as we can see, it has round down this number toe one numeric digit. We could also round down this same number toe to digits, and we get this value want If we want to round up the number that can also be done using their round of function? Cilic the number mentioned the number off digits that we would like to round up and hit on India. We can also use around function and we can mention the number of digits that we would like to round. No want. This around function does is it takes the number off digits that we want from the selected cell and checks the digits in the decimal place. The next digit, if it is more than five, it will convert the previous digit. That is four in our case to the next digit. Hence it has returned 6725 If this was four, it could have mentioned 67 to 4 itself. So from five onwards it converts the number to the next value. Now let us look at some very interesting functions more and end. Let me explain you by giving an example. Suppose we have time and seconds. Let's say 5 23 seconds and we want to convert this into minutes and seconds Now How do we go about it? One way is by dividing the time and seconds by 60. You can get the value off minutes, but this will give some fractional value. Also, how do we find out seconds from this we would have to write minutes minus the part which is before decimal point, and we get the seconds. But this is all infractions. We can display this into anti Asia by using these buttons over here that decrease in the decimal point. But as we can see, it is not an accurate value. Excel has two very interesting functions called in teacher and model ist. Let us see how to use them. What Indonesia does is it will convert this divide by 60 and it will take only the Indies apart and give us the result. That's what we wanted. For seconds. We would have to find a way to remove the minute spot from the total time and display the seconds. One way to go about it is take the time and substrate act the time taken from the minutes and get an answer as 43 seconds. There is a better way to go about this by using them more function. Let us right more a number that is time, and we want to divide it by 60. Now, what more will do is it will divine 5 23 by 60 and whatever is the remainder. That will be the output. So when we will hit on, enter it directly gives us 43 seconds hence, these functions can be very useful for certain occasions. Now, let us see one interesting formula in Excel Gold as random. What this formula does is it generates random numbers. If we use the formula R A nd, which is a short form four random, it generates numbers between zero and one that is all in decimal randomly. We can then specify the number off digits that we want to show after the decimal point. By selecting these buttons or here, every time a cell is written in the sheet, Excel will re calculate the random numbers that have generated. Now, if we want to generate numbers between specific number off from and do, then that can also be done. Let me explain you by giving an example. I have some employees or here now these employees need to go for a particular test, know which order should I send them If I want excel to generate a random number for each of these employees that can be easily done using the rand between function, let us ride dysfunction over here, equal to van between the bottom value and the top value. Now here my observations are only for six values, so I would write one as the bottom and six as the job when I hit on Enter it is generating random values for a. If I drag this formula down, I can see that it has generated random values for each of these employees. I can use this function and places where I do not know which order I would like to take our I want to shuffle the data that I have. This can be easily done using this ran between function somebody you have learned. Average mood and median function Use Excel for area and volume calculation. Round down, round up and round function mot and I M P functions rand and rand between function 5. Financial Functions: objectives. By the end, you should be able to use Excel. For loan related calculations, use the PMT function for air. My calculations. Use the PV and if you functions toe calculate present and future value off investment. Today we will see two very important concepts in Excel that help us to plan our finances. The first concept is Am I that is equated monthly installment or equal monthly installment , which we have to pay when we have any loon. The formula for the same little complex. I have returned it over here it is he am I equal to be into our into one plus our race to end divided by one plus R race toe end minus one, where P is the principal and R is the rate off interest permanent. So if 11% rate off interest per annum, then pour monthly interest would be our divide. 12 in 200 N is the number off monthly installments Now, although this formula is little complex, Excel has given us a ready made formula that I have used to create this ear. My calculation sheet and I'll show you how easy it is to create this. Let me first remove these values. Let us start. Let us stay the loan amount as too lax the loan term as three years payments. Bowyer would be 12 because there are 12 months and read off interest. I have taken as 18%. The monthly installment can be written as be empty. Let me open this for you. Read off Interest is to be selected as cell B six that is 18% divided by payments per year . That is be five. Temper is the number off the monthly installments in our kiss. If you're paying it for three years, it will be three into 12. That is 36 payments to be made, so we would ride this before in Toby. Five BV is the present value. Now here we will write minus and the loan amount, which is too lax and then click on OK, so now we will see. Monthly instalment amount is 7230. I have prepared this stable over here serial number, monthly installment interest, principal balance as the table headings serial number extending from 01 and so and so forth until 36 because I will be giving 36 payments. The initial balance that is the loan amount is too lax. Hence I written that over here. What is this? Monthly installment Monthly installment will be 7230. And I would give that an absolute reference because we don't want that to change. When we do that Hit on enter and will copy piece the formula. Throw two column. Next is the interest. Now, as you know, interest amount is taken on the balance. That is the principle. Civil right, equal toe the violence amount. Now here we will not give the absolute reference because we want the previous rules balance to be taken over here multiplied by the rate of interest. So in our case, it will be 18% divided by 12. But here these last two values have to be given an absolute reference by hitting on a four key. Now we will move ahead with the principal. So when we pay an interest off 3000 from ₹7230 we get the principal amount. For the first year, we will be paying an interest off ₹3000. So the principle for the first year is 4230. So the balance principle is nothing but previous years. Balance miners the current year's principal payment and this will not give an absolute reference because every year we want it to take from previous rules value and the current rules principal hit on enter. No, we are almost done. All that we need to do is select these three cells and dragged this formula by clicking on the Phil handle over here till the bottom off the table. That's it. We have our am I calculation sheet ready? Simple, isn't it? Now we look at one more interesting concept called inflation. Now we know that due to inflation, the price off the good that is today doesn't remain the same tomorrow it keeps on increasing. Also, if somebody tells me that he or she would give me one lack rupees today or ₹1 lakh after the year, which one I would prefer. My answer would immediately be today because tomorrow's ₹1 lakh is not worth one lack of given today. Let us see how first will work on the future value. I have returned the stable or here future value off ₹10,000 years from now on one year and inflation rate is 7%. So what we are trying to calculate is the present value off this 10,000 which is given to us one year later. We have got the answer. 9345. Let us see how we will write equal toe present value PV I will open this formula for you. Rate off interest. We will click the inflation read Temple is the number off payment periods in an interval for that filled right one and payment We will write as negative beat resell and that's how we can get the present value. Now let us see future values formula. We will try to find out future value off 10,000 given to us today. Two years hence that is after two years with an inflation rate 7%. We have got the answer as 11,449. Let us see how we will write in the cell. Every for rate will select the inflation rate. MPAA We will select the years from now and Demond we will write as zero for now because we are not doing any payment in this year and present value we will select just like before. Negative 10,000 and that's how we get the future value. We can also use this to compare with our previous answer. I have just changed the number off years toe one and now I know that 10,000 given to me today is worth 10,700 future Veras 10,000 given to me after year is just worth 9345. Somebody you have learned using Excel for loan related calculations using the PMT function for ear. My calculations using the PV and a few functions toe calculate present and future value off investment. 6. Lookup and Reference Function: objectives. By the end, you should be able to use look up and reference function. Jews match and index combined the match and index function for flexible output. Now we will see how to use some off the look up and reference functions In excel, we will see how to use choose index and match and also how to use index and match together . Let us start with the use of Jews. Now suppose I have a sheet where there are certain number of candidates who have appeared for interview. These candidates have given written test and their interview. Their schools have been put here in the sheet and I have arranged the data in the descending order off there to three. I have a requirement that whenever I write a rank in this field, sea food drank the result Cell should show me which particular person has the fourth rank. So in this case, the results cell should show me Krishna, over here Now, in order to get this output, we can use choose function in excel. The former it off juice function is like this equal toe Choose in next number. Now what is this index number? It is the number off output that we are looking to get. In this case, we will write for drama all the values in the order off their ranks. So we'll write Courts machine Mama Kavita coma and so on and so forth. In this way, Now we close this bracket and hit on. Enter. It will give us the value, Krishna. So what we have done here is we have written choose four and in courts we have written all the names. As for the order, this is one way off writing this formula another way is equal toe. Choose by giving references. For example, choose rent called him because we want to change the result. As for the rank that we have input comma, select all these candidates one by one. Now we will select this for cell B two comma Vetri, comma before and so on and so forth Close the bracket and enter with this We get the same result and it also has a benefit that whenever we change the rank, the output changes. Hence this the better. Be off writing the function Now let us see how to use the match function. Match function searches for specified value in a range of cells and returns the relative position off that cell, for example, From this list, I want to find who has got a written test score off 16. For that I have created these cells over here. A written test score off 16. I want to find the result that is the name off the candidate who has got written test score 16. For that, I would write equal to match look of value that is 16. Instead of giving reference here, I could have also done 16 as in the given case, but that would make this formula static. Hence, I would select the cell so that whenever I change the cell value, I would get a different result. I would write drama All these cells, Mama. Now this is a match type. It's 10 or minus one. For the time being, we will ride sido because we want an exact match. Clues bracket and enter. And here we can see the candidate with rank tree has got a written test score. 16. No vile writing this formula. We saw that the last cell can be either 10 for minus one let us understand this minus one is for greater than as in whenever I write written test score is 16 and my data is organized in descending order just the way we have done. We would get the result that is matching the greater than type. For example, here it is showing rank tree is matching 16. What if I changed it to 17? It will give us rank to What Excel has done is it's returning any value that is greater than 17 in this look up range and has returned the position off the value that is matching 17 or greater than 17. If I would have written value off 17 and last match type as zero, the exact match, the formula would have given us an editor like this. Similarly, we could use match type as less than only when our data is organized. In an ascending order. We had seen how to use a choose function on one column. What if I want to extend this facility off one column and extended to my entire table? For that? I would need to use an index function. Let us see how to use it. I want a total off the candidates who have gone to rank six in this case. So Mitra has got rank six. So we should get an output. So, Mitra, in this particular cell, let us see how to do it. Using an index function, let us right equal to index and Ari. It has two days off writing. It will consider the 1st 1 The Ari format will select all the cells from which we can get an output. In our case, it is B 22 e eight comma, the room number. Now the room number in our case is given by the rank. So I will select this cell comma the column number. Now our table has 12344 columns and we want an output off the total. Hence will select the foot column, clues bracket and enter. So here we know that the ranks six candidate got a total score off 21. We could also change this formula to represent a written test score by changing the skull of number two and enter. So now we know that we can get any output from the stable if we use an index function. We have seen how to use the match function and how to use index function. Now we will see how to use these functions together. Toe get an output, which is much more flexible than the V look up function, moreover, takes less time than the we look up function in order to use index and match function together. Let us create a scenario. What I want from this entire table is whenever I write the name off the candidate, I should get the scores off the candidate. Or, if I want, I should get the rank off the candidate for this. Let us right equal to index. Now insulate the Indict table, Mama for the room number. I would use the match function. Match the name with the column where the name occurs in the stable mama exact match. So I will put a zero close bracket comma and the column number. Now for the column number. We can use any value from 1 to 5. If I want rank, I could write one. If I want the total scores, I would write five for the time being. Let us right. Five. Close bracket and enter. So now we get an output as 29 because the total score off Roger in this stable is 29. If I change the name to something else like Sunita, the total score off Sunita is seen in this output cell. Now, as I mentioned earlier, if I change the column number to something else, I would get a different output. Let's say I don't know the rank of this particular candidate and I want the rank off the same. So I would write column number one at the end of this function and hit on Enter. Now I know Sunita has got rank five in the table now this particular case off searching for the rank with the name off, the candidates would not have been possible by using. We look up function because the rank column appears on the left hand side off the candidates column. Hence index and match can be very useful In such cases, we can also use index and match as an ari formula and give results off a nested. We look up, we will see it in some other exercise somebody you have loaned used look up and reference function. Jews match and index combined the match and index function for flexible output 7. LOOKUP, VLOOKUP and HLOOKUP Functions: objectives. By the end, you should be able to use cell function to get information about cell use. Other information functions like is blank East formula and ease number use. Ever finding function is error is ER, and if Edgar used the offset function, use the counter function. Use the indirect function, construct dynamic name ranges, construct dynamic formulas. Today I will share with you some functions in Excel that, if use efficiently, can give you an added advantage. Let us see them. Let us go toe formulas, More functions. Information. Now here is the list of functions that help us get some off the other information about some cells or some functions in excel. Let us stay the 1st 1 that is cell. The cell function helps us find a number of things about certain cells in Excel. This shows a list of things that we can find We can find an address or a cell are a column number off cell color contents and so on and so forth. Let us see how to find address off a cell. For that, we would write equal toe cell Sylhet, Andris from this list drama, let's take this cell for now clues bracket enter. So as we can see it returned the value as before. We could also use the self formula to find a column number off a cell. For that, we will select column drama Let's stay this cell for now if four close bracket and enter. So as this cell belonged to the e column, which is the 50 number, it returned five as value. Similarly, we can use this to find room number off a cell for that filled right sell rule and let's select the cell for now, see 12. So as we know that since it is a cell belonging in a 12 row, the output will be 12. We can also use cell to find out a file name off the file that we are working or some other file. We want to refer toa for that fill right cell, select file, name, comma and give reference toe Any cell in the file. For now, we will just give reference to even off the same file close bracket and enter. So as we can see, it gives us the fight name with the sheet name from where we are refering to let us see some other formulas. There is a category off functions under more functions in the information tab that start with the word is no. These functions give us an output as true or false, depending whether the condition is met or not. For example, the 1st 1 over here is is blank Now is blank. Function tells us if a particular value off a cell that we have refering to is blank or not . For example, let us just double click this and select this cell When we click on OK, it says that it is false. That means this particular cell, which we refer to, is not empty, so it is not blank. Similarly, there are other functions which give us the information whether the cell is having any error or is having even number or an odd number, all the cell is a text known text. What a number. It also gives us an information if the cell that we are refering to as formula or not, so these functions can be used to give us certain information about the cells that we want to refer to, and you can use them in formulas to perform certain calculations based on the outcome off these functions. Now let us look at three error finding functions. Let me first right, Avi, look up function. Let me find out the value that is there in Region two for me month. Using this, we look up function. For that I will write equal toe. We look up in courts me Then I will select the cells Drama column index for region to would be three because the first column is the month. Second is a region 1/3 column is region to hence ah have returned three comma falls because I want a perfect match Close bracket and hit on Enter. So it tells me that for the month off me and region to the value is 1 35 No, we have got an answer over here. What if there was some better in dysfunction or the outcome? Let me purposely add an editor over here instead, off me, Let me write me with an error, an extra vie and hit on Endo. So it gives me an editor hash n A. That is not available. If I want to know that whether this formula gives an error or not, I can use this function. Is it, uh, and reference to this cell? Hey, don't ender. So it tells me that a particular calculation has gone wrong and there's some error to it. Similarly, we can also use is e. R r. Function, where the value off the other is not off the type hash and a it can be any other type, like hash value hash reference. Whether reference is not found divide by zero error or some other errors which are mentioned over here. The third interesting error formula which help us to give an output which is logical. Or, in other words, if there is an error in any function and we don't want a sheet to show that error, we can put some value instead off an error. Using that function, let me show you how that function is. If editor let me select this, let me write our function again. We look up me with wrong spelling comma, same table, Ari, same column number and false as we had selected earlier Comma know if there is an error in this particular calculation, it should return one close bracket endo. So as we know that there was an editor in this function. Hence it has returned. One. If I create this formula and write the correct value off me and hidden enter, it gives me the output as 1 35 So whenever there is an error, it will give me the value that I want to specify. I can also write some text over here if I don't want a numerical value for that. This is a very useful formula, having the knowledge off functions and that are placed over here under the information tab . Let us move ahead and look at some other functions which are marked under look up and reference. Let us see offset and indirect offset function returns a cell or arrange off cells that is specified by us. Toby. A certain number off rows or columns away from the mention cells. Let us understand it with an example. Suppose if I want to find out the region four sales in the month off Munch For that, I would write equal toe off said, Let's take this reference as a three cell Goma. How many rules down now? Since we want to find out for the month off March, I would write three three rows down or three months coma columns. Now, if I want to find out vision for, I would write 1st 2nd 3rd 4th So four columns away from this particular cell 83 and height . And with it these two are optional over here. If we want only one cell, Toby returned. We would not write anything. But if we want to be specific, we can write one cell height and one cell Weird close bracket enter. So it perfectly gives us 105 as an answer. We can use this same formula toe. Find out any other cells from this particular table on this particular sheet, for example. Now, if I want to find out for the month of December for Region three, I would simply change these two values. Hey, Don Endo, that's it. I'll get my output Offset function can also be used to calculate the sum off all these values. Let us see how let us right some because we want to find out the some offset. Let us select the same reference as before. 83 Cell coma. One drew one draw down Goma. One column I had the height would be 12 because they want to find out for all the 12 months . And the wit would be one, because it is extending only 41 column close bracket. Close the out of bracket hit on Enter. This value is nothing but some off all these values. Let us verify it by using the auto some function. Hence we can Hughes offset and some together to find some off any regions. Or here we only have to change this particular value. Which off the column number and we can find out some off any off. This region's very easily offset function can also be used to create a dynamic name. Ringe. Let us see how Let us first, right some random values here I have written some values. Let me a sign a name to this range off cells. I have given it a name as value. Now, if I want to find out some off this I would write some value Close bracket enter. So I have got this some off these values. What if I want to add value over here? But as you can see, once I have written this as some off name range value, it does not expand automatically. If I want to expand this particular formula to extend any time I add any values over here, then I can use the offset function in the name Ringe. Let me show you how Let us go toe the name manager, the value name renge that we have created. Let us editor it refers to we would write equal toe offset open bracket. Let us a late the first cell over here. Mama zeros down because he want to started from Ruben itself. Comma zero columns ahead because we want to calculate in a column only coma For the height . We want to have some dynamism or there hence the height will not be specified by numbers. Instead, we would right a formula that calculates the number off cells that have certain numeric value, and the formula for the same is count A. So we would write Count a select, this column that is a column close bracket. And for the went, we would write it as one. Only because only one column we want to calculate. Close bracket. Okay, we'll close this. And now, as you can see, the value in column C has changed. Let us try to add some more values and some more so automatically This name range changes asked for the number off cells that I added. Now, since this is a dynamic name Ringe, if we select the cells over here, it will not show the name Ringe in this particular box. Hence we need to be a little careful when we use dynamic name ranges. Let us now have a look at a very interesting function called as indirect. This indirect function returns the reference specified by a text string. Let me give you an example. Let me right before over here and let me right in direct and let me give reference toe the cell. Given where I have returned before, lose bracket and ente so won't be see v C. That indicted function has gone to the reference Jeevan taken the value that was written over there before searched in the sheet for the cell B and full and give us the output as 102 change this value. Do you see de 11? So my indirect function will goto jeevan did this value as D 11 search over here and return me D column 11 true that is 1 41 value. Interesting, isn't it? So we can find out any value from the sell by using indirect function and changing just one cell. Let me show you all how to use indirect function to find out some off the some values in the stable. Based on my conditions, I want to find out the some in region two, but not for all the values. I want to specify certain range over here. For an example, let me take 50 rule still 10 through. So I want to find out the values from this particular cell till this cell only. And I want to change this any time. I've warned for that Let me show you how to use indicted and some together Let us right some indirect. Now we want to give a dynamic reference over here we would use No. Since it is column C, we would write c in courts and this is usedto con coordinate. Let me give the reference off this cell Etch Four and v von Colon See in courts and reference to the cell edge five. Let me explain you what I have done over here. Rayvon to find out the values between C five and C 10. Correct. So now we won't see five to be written over. Here is to that is colon see 10 to be written over here. But we are using this end so that it gone. Captain. It's the value for these cells when we hit on Enter. It gives us the some off all the values between C five and C eaten. We can verify it from Hill 7 10 Now, with the change off only these cells, I can find out any values that I warned between a specified range dynamically. So that's the beauty off. Using indirect summary you have learned using the cell function to get information about the cell information function like is blank is formula and ease number. Air finding functions is error is here are and if error off search function, counter function, indicted function. Constructing dynamic name ranges, constructing dynamic formulas 8. Arrays: object IBS. By the end, you should be able to use the look of function, Use the vehicle function, use the edge, look up function. We look up in live action lookups nested within lookups. We know that excel can be used to store a huge amount of data. If I want to find out where a particular value is lying in a huge table, then I would have to manually go row by row and search my value. But there is no need to do this manual searching as Excel has provided us with three powerful formulas. Let us look at them. The first and the least use formula is a look of formula. Using this formula, I can find out ah corresponding value Present in particular column. When I find a particular value in another column, I have a data over here, often automobile shop. There are some courts off some parts that are sold the name of the part cost ordered quantity and the stock of the meaning. I can use the look of formula to find out the cost off the part by giving the code off the part. Let me show you how we will right, look up! Look of value. Let me give an example. Etch 11 Mama, the look up, Victor, This is a column or row that has this look of value Edge 11 drama. Now this is an optional part. If I won't look up to return a value from this column itself, then I would not complete further, but I won't. The cost off part Toby returned. Hence I'm using this particular column. One thing we need to take care is whatever range we have selected for the look up director , we have to select a similar range for the result vector close bracket and enter. So using look up, we can find out that 11 automoviles part is costing us 1 ₹50. This is not a very accurate formula to use. Let me explain. You've I suppose if I change this h 11 toe age 13 it would still give me 1 50 as an answer Only when I change edge 13 0 j 03 which is a next court in this list, it will give me a next value. Hence we rarely use this formula. A better alternative to this is we Look up, We look up formula can be used to find out the corresponding value off cell from this stable. Very accurately, let me show you how equal to we look up now the look of value remains a scene 11 table, Ari. No. Here. The only thing we need to take care is this. 11 value should be lying on the left. Most column off the table selection. That is the table, Ari. If we are selecting it as even e eight h 11 should be of value, that lies between even do eight itself. Which is the case in our example. Suppose if I wanted to find out the name off the part, then I would have selected Beavan two e eight. Suppose I wanted to search for ordered quantity. Then I would have selected divan toe e eight And one more thing that we need to take care is we look up, will only search from left to right. Hence, our data should be organized in such a way that the cell that we are looking for lies in the left Most column and the output cell lies in the column that are on the right. So here will continue column index number. Now we have selected from Avon Toe E eight. So a condom is the first column. B is a second and see is third. Our cost off the part lies in the third index number column Mama Range, Look up! Now this is an optional value by default. It is true. By true it means that if my data is organized in the ascending order which is a case in my table, then it will return an approximate value. So even if I right edge seven, it will still give me the value off Etch 11. We will try that a little later. For now, we want to find out an exact match and hence will write falls close bracket, enter. So here we get the cost off the part for each 11 code. If I change this toe edge 12 it will give me Ah, hash and a better that is this particular court edge 12 is not existing in the stable. If I change this false to true, that is I want an approximate match. Then it will give me the value 1 50 This formula is very useful in finding out values in any off this columns by giving a reference off the first column. No, I can change this. The column index number 25 and I can get the output as stock demeaning. So the stock remaining for each 11 Let me change this back toe edge 11. So the stock remaining for each 11 is 73. The third interesting and powerful formula is H Look up we look up stands for vertical Look up! This is column buys Etch Look up stands for horizontal Look up so it will search the value horizontally. But now since our data is organized in a vertical order let me convert it in the horizontal order using the transpose option in paste special. If our data is organized in this way that is all the court are written from left to right names off the parts again left to right in the next room Cost off the part in the next true and so on and so forth Then I could have used the Etch Luca formula Here we look up Formula cannot be used to use Etch Look up! Let us straight equal to And she look up the look up value Let's take it j 03 this time, Mama, The remaining things remain almost the same. The only thing is with the Etch Luca, the value that we are trying to find should lie in the top mostro for we look up the value of was lying in the left. Most column So here I have selected a 13 to etch 17 because my code J 03 should lie in the top. Mostro comma Drew index number here the output If I want to find out ordered quantity then I would count like this First row, second row, third row, fourth row So this will be four coma I would select false because I want an exact match Close bracket and enter. So the order quantity for G 03 is one. This is how we can use Look up we look up and edge Look up having the information on how to use a V Look up. Let me give you a small case. I have prepared a sheet over here that has the name off the part and where it is stored. Now this information is present and she do and sheet one, as we have already seen, has information off court off the part and cost of the part. If I want to find out where this particular part C J 33 is located, it would be very difficult for me to find out first where j 33 in the stable. Find out its name. That is oil. Go to the second sheet and find out that oil is located in pony. Now, this is just a sample data. Hence it has very little ruse. If I have an actual later, this may extend 2000 rules. Hence let us see how we can do this. We can achieve this by using a nested. We look up, that is Ah, we look up inside of a look up. Let me show you how. Let us go step by step. First part will try to find out where is located. So we will write. We look up A look of value is oil coma table Ari, isn't she too Will go to sheet too. Select this stable because the left most column will contain the name off the karaoke Goma column index could be too Goma false for the exact match Clues packet and enter. So we got the first part, right? The second part of this function is finding the name off the part from the court off the part. So for that will, right, we look up. Look off. Value is J 33 comma table, Ari, is this particular table comma column index is to this time because you find the name off the part comma exact match. So will select falls close bracket and enter. So now we have got oil as output using the court J 33 and this oil, we will search in the second sheet. So we need to give this particular output inside this particular V. Look up in this portion. Let us try that We will select the cell Cilic the formula. Copy it and basted way. We want that output to come and hit on. Enter. So now our formula is ready. It has a V look up inside of a look up. The inside we look up is giving me the product name and this spot name is found and she to in this particular table and from sheet to we are extracting the stock location off the part. So that's how we can use a nested we look up summary You have reached the end of the lab. You have learned using the look up function using the V look up function using the edge Look up function we look up in live action lookups nested within lookups 9. Miscellaneous Functions- part 1: objectives. By the end, you should be able to describe how Excel Stores, date and time values explain the concept off volatile functions. Use date and time functions today and now use the year month day are minute and second functions. Outlined. The process off calculating difference between two dates. Use the date if functions to calculate age in years, months and days described the process off Khan. Coordinating functions use the date value and time value functions to serialize, date and time stored in text format. Excel has a very unique way off storing dates and time. Excel stows all the dates as Indonesia's and all times as decimal fractions. With this system, Excel can add subtract or compared dates and times just like any other numbers, and all dates are manipulated using this system. In this system, the serial number one represents first Jan. 1900 with a time off. 12 a.m. Times are stored as decimal numbers between 0.0 and 0.99999 We're 0.0 is 12 AM and 120.99999 His 11.59 point 59 p. M. The date in teaches and time decimal fractions can be combined to create numbers that have a decimal and an impeach er potion, for example. The number 32,331 0.6 represents the date and time. 7 July 1988 and time. 1 26 minutes 24 seconds. AM Let us see how we can use Excel to manipulate date and time. Excel includes many built in date and time functions. Now this is the list off the functions that are there under date and time. Among this list, Let us see how to use today. Duty is function that gives today's date without the time. Now. This can be very useful while preparing bills or invoices or talons, and some level off automation can be achieved using today function. There is one more function known as now. This function returns two days did, and the time along with it. These two can be very useful. However, one should note that these are volatile functions in the sense their value will keep on changing. When you change any off the cell in excel, let us use this time with did and let's try to see how we can get individual parts from this In this sheet I have written the now function over here and let us see if we can get your month. They are minutes, seconds, all from this cell. In order to do that, we should right equal. Do you're select this particular cell clues bracket enter So as we can see using the year function, we can extract the year from the date. Similarly, we can use month function for extracting month from the dead. No, this month is given as a number. We will have to calculate separately if we want February to be written instead off to de function This function, as the name suggest will give us a day off the date out of function. I will return the time which is mentioned over here. Minute function will give us the minutes that is seen and lastly second function will give us sickens. Now, if you're wondering how will excel help us calculate the difference between two dates. Now that is very simple. Let us see an example. Let us right. Some two random dudes Let us take the first date as 1/7 Gen 2016 and second it as third Gen 2016. Now we want the difference between these two dates. Excel is smart enough to understand that we are calculating the difference in deeds. Hence, we can simply right eight and sell minus beat and sell. And it automatically shows that the difference between these two dates is four. If we want the from date and do did that these two dates to be included in the difference, then we can simply add one toe this. So we know there are five days between third Gen and seven gen. Inclusive off these studies moving further. How do I calculate a person's age? Interesting. Because age has a number off calculations in years, in months and Indy's we can use a formula as shown over here. No, this, as you can see, is a big formula, mainly the reason, because one needs to calculate the difference in the years and then once that differences over one needs to see if the month from which we are calculating to the current date is more or less. And if that is so, each has different calculations for month, and once that is done, the number off days has to be again compared whether the current deed is more or less. And according to that, the number off days will change. We will not go into this formula because this is quite complex. Instead, Excel has given us of freedom to use a hidden formula. Now, why do I say hidden? It is hidden because if you go through this list, you will never find that formula. How we will we can still use it and the name off that hidden formula is dated. If this data if formula calculates the difference between two dates and based on the condition whether we want years or we want months off even days, it gives us an output. Let us see how with these two dates 11 September 84 1st Gen 2016. Let's see how many years of difference is there between these two dates. So we would write equal toe dated if now, if you noticed Excel didn't give us an option for dated a function right, the earlier did comma the new did and we want output in years. Hence, I'm writing why in capital in courts and closing this bracket hit on Enter It tells me that this person is 31 year old. Similarly, we can use equal to dated if to find out the months off difference between this date and this date. When I write em hit on enter, it will give me the total number off months. If I write why I m it will calculate the years, remove the difference off the years and give us the difference off the months that is remaining. When I hit on Enter, it tells me that this person is 31 years and three months old. We can also use equal dated if to find the difference in days hit on Enter. Now, if you don't want the number off days and just one number off days which is after the direction off years and months, then we should right instead, off the right MD hit on enter because we are deducting the months also And now we can see that this person is 31 years, three months and 21 days old. We can represent this entire information using a con captain it function and joining these three dates with the formulas written in them, as I have done over here. And with this just one sentence, we can get the output off this complex formula. Interesting, isn't it? What if I have a date in text format and I want it to be converted into the serial form it just to know how Excel would store it? That is called serializing deeds. There are two functions for the same. The first function is for the date, and that function is date value. Let us see how to use it equal to date value, bracket and heavy. Want the date to be written in text format. Let's take an example. 11th Gen 2016 Complete the courts and bracket hit on Enter as we can see acceptable store this particular date in this serial format. For time, there is function known as time value. What this will do is convert the time, which is written in text into serial. Let us take some random example. Let us right 1 15 54 PM Close the courts then close the bracket and hit on Enter, So Excel will store this particular time. 1:15:54 p.m. In this particular serial format summary you have learned storage off date and time values in Excel want are volatile functions, date and time function today and now you're month B R minute and second functions. Calculating difference between two dates using the date if functions took. Calculate age in years, months and days. Con coordinating functions using the data value and time value functions to serialize date and time stored in text format. 10. Miscellaneous Function- part 2: object IBS. By the end, you should be able to use the left and right functions. Use the len and trim functions. Use the case changing functions proper, lower and upper. Use the con. Coordinate function, Use the replace and substitute functions. Use the mid and find functions. Use the text function. Excel has many useful textual formulas. These formulas help in manipulating certain text and getting a desired output. Let us look at some off them By giving certain examples. I have considered hypothetical examples off some chords that I have now. The 1st 3 digits off this cell is representing Mumbai, the city, and the last four digits is a number given to a particular place in Mumbai. Now I want to find out with City. Is this by extracting this information from this cell in this particular cell? How do I go about it? We can use left function in excel. Let us right equal to left. The text means select the text that we want to extract the information. Goma The number of characters. Now here we know that our text has three characters from the left, which we want to extract. Hence I write three close bracket hit. Enter. So in this way I can extend this formula down, toe other cells and get the information off the city extracted right from the cell. Now, if I want to extract the courts given toe the different places in each of these cords, Excel has a way to go about it. And the text manipulation formula for the cell is right. Let us right. R I g h t open bracket. Select this cell comma. No, we know that there are four characters from the right that correspond that we want to extract. So I write four over here, close the bracket and enter. In this way, we have successfully extracted the cords on this entire cell. We just tried the formula to other cells to get codes out of them. Now let us look at some other interesting sexual functions. I have some sentences which I have copy pasted from some other applications. As you can see, these sentences are not for Madrid correctly. There are randomly excessive spaces in between. The first character is not capital. The middle character is made capital, etcetera. Let us manipulate these sentences First, let me find out the length off the each off the sentences. This information may be useful in some other formulas. So in order to find land, we have to write equal toe l e n for lent and reference to the sell off. Lend. We want to find close the bracket and enter. So Excel tells me that the first sentence Lent has 16 characters. Similarly, by dragging the formula to other cells, we can find out the length off the different cells. Now 1 may wonder these sentences are not formatted correctly, and there are excessive species which we want to remove. I would have to do it manually for each of these cells. Now I may have a long list off such sentences, which are not formatted correctly. What I do excel has a very nice function called dream. We can use trim to remove any excessive species in between the sentences. It will only keep a single space in between. Let us try it out equal to dream and the reference to the cell, which we want to trim. Close the bracket and enter. The first sentence was written correctly and there was no excessive spaces. Hence the output is exactly the same as the cell itself. Let us see what happens when we dragged this formula for other cells. Now we can see the second sentence is formatted a little better. There were excessive spaces between S and P which have been removed. Similarly, there were excessive spaces in this cell between name and is which have been removed. In this way we can format the cells now. What if I want this formatting Toby a little better than what has been written in the sense that I've warned the first character off any sentence to be capital and the remaining Toby Small for that Excel has a function proper. What proper does is it makes all the first characters off each off the word as upper case and the remaining as Lucchese. Let us right proper select this sentence and India. So now we can see each off. The first characters have converted to an uppercase and remaining characters have converted in Luhukay's. We could also convert all these characters into Lukis by using the function lure. Or if we wanted, we could convert all these characters into the upper case by using the function upper. So that's how we can manipulate sentences in excel. What if I want to join a number off sentences which are placed in different cells? Let us consider an example. There are three cells in which some information about this person has been written. I want to join these three sentences in just one cell. We can use the con coordinate function for this. Let us right equal to gone catty need next one is the first cell Goma, the second cell Mama, The third cell closed bracket and enter. Now we can see Excel has joined all these three cells and written it in one cell. I could have added some more characters between just to enhance the look off the output. For example, con captain ate. My name is Ravi Goma. I want a space in between. So I would write a space between the double courts, then a comma. And after I am from India here, I would want some more species. So I would write court, space, court and comma. In this way, I can add characters between the text using the double courts with the sentence that we have just formatted. Let us try to manipulate it. What if instead off text. My name is Ravi. I want to write. His name is Ravi. I have two options in Excel one using replace and other using substitute. Let us try to use replace equal to three, please. Old text is the cell which we want to change comma start number. That is one in our case because my is appearing as a first character number off characters that is to and the new text and the new text is his which I am writing in courts and clothes. So here, if I know the location off the characters which I want to change, I can use a D place function. When I hit on Enter the sentence changes to his name is Ravi. What if I don't know? Where are the characters lying? I can use the substitute function. Let us right. Substitute the text. Which is this cell? Goma. All text that is my in court and with the proper case with em capital and why small comma new text is his and the last is instance number. Now we have a choice to change a particular instance off the text where my is appearing. It may be the just first instance or I want all the instances to change. If I omit this, it will change all the instances. If I write to, it will change the second instance. Let us just close this and hit on Enter. So now in this case also we will see that without knowing where is my appearing in this sentence. I'm able to change the characters. Let us try to find some information from within a particular cell. For example, I want to find the age of this person from this particular cell. For that we cannot use the left function all right function because the left function will give us only the starting would off this sentence. For example, If I write left and select this cell with the number of characters say then it will give me I am 12 and some extra characters. So it is not giving me the output Well as desired. Similarly, if I use right, it will give me only certain characters which are specified by me instead of just 12. So what will I do? I can use mid function. Let us arrived. M i d select the text that is Cell 11 comma Start number. Now I need to count the number off characters before 12. Or here it is I species e m and space and it starts from the sixth character That is one. So I will write six Mama. The number off characters in my case is too, because 12 has two characters close bracket and enter so I can get the each of this person . However, as you saw, it is very difficult for me to calculate the number of characters and extract the information in excel. We can do this by using a combination off two functions find and mid Let us see how to do it. For that we will right equal to made the next of which is a 11 coma start number Now here I will use the find function. I like to find open bracket. We want to find em because a sentence will always be I am 12 years old or I am 20 years old . Mama within text text is again a 11 cell mama start number No, here I would write one because we want to start searching from the first character off the sentence itself. That is I because we don't know where we'll am be placed in the sentence. Mama. Now here is the trick. I will explain you little later. The number of characters, a number of characters. I know that we've owned two characters as the output close bracket and enter. We would not get the right value. I will explain you how I am getting em. Why? Because it is searching M and returning to characters with this. If I return as four characters, it will give me an output M one. If I write five characters, it will give me an output. I'm 12. No, still, this am is extra and we don't want it. Hence we would write, find I m in a 11 to 1 and I would add three characters off E. M and the space before 12. For that, I am writing three characters and hidden enter. Now it's showing me 12 VIII because I am returning five characters after the start off 12 so I will dry just two characters As the output and hidden enter. Let us analyze dysfunction. I am finding a particular value between this text and I am returning to characters. There are made into the find function finder, dext em in between the entire text and starting from first character I itself. So in this way we can find out a particular value from any sentence without knowing location off the same. What if we have date written in a particular format and we want to write it as text in some other format, for example, Now I have a date. 11 Dash September Dash 84 Written. I would like to write September in full form, so I need to convert this particular date in a different format. We can use text function in Excel. For that, Let us right equal to text the value, which is this particular Sell it to Mama. Former text Now here I want this text output Toby off the format DD dash M M M M Dash. Why? Why? Why? Why and courts? When I write like this, it will give me an output. 11 dash September in full form Dash 1984. So in this way we can manipulate text the way we want somebody you have loaned left and right functions. Len and trim functions case changing functions, proper lure and upper con continent function. Replace and substitute functions mid and find functions fixed function 11. Error finding function: objectives. By the end, you should be able to use if ari formulas used transports. Shift hiring formulas are very useful formulas. Let us see the use off if condition in an airy formula. Using an example, I have a list of products and the corresponding colors and shapes. What the management wants to do is to find out how many off the products are having a color red or if devore and they can change this particular cell and find out how many products off the color blue. Also, the company wants to find out how many products are off a particular shape. Let us see how to go about the same. One way to do is using an if condition for each off this sells. Let us create a column over here. Let us right the formula. If this particular cell is equal to the cell specified over here, and we will give it an absolute reference, and if these two values are equal, let the output B one or, if not equal, let the output B zero. When we do this, let us extend the formula. So the end off the table using this Phil handle no As you can see, there are some ones and the remaining Zito's. When I do account or a some off the entire list, I can see the some as five among 19 entries we can right over here, equal to some off this entire column so that we can see that there are five items off red color. Similarly, we would have to create one more column for the shape and find out the number of items which are off the particular shape. Instead of doing this, let us use Harry's and ease up our task in order to create an airy formula. Let us right in the cell, equal to some because we want to. Some all the entries which satisfy our condition and our condition is if all these cells let us make it an absolute reference by hitting on F four so we can see a dollar sign in front of C and dollar sign in front, off 10 and similarly over here. So if these values equal to these cells, we warned the output off. If condition Toby one, it's Toby zero. Close this bracket and close the outer bracket and now hit on control shift and enter. Now it gets converted into an ari formula. So, like this, we can see that using just one formula, we are able to get the output. Similarly, this formula can be extended down, and with mine of changes off selecting the cells, we can get an output for the shape. Also, when we are entering data in tables, we at times realize that it could have been better if our rose have been written in. Columns and columns have been written in Drew's here. I have considered an example off a company who have mentioned it's shift off employees the time off, the each off the shifts and the employees in each of these shifts. Now I realize that it would have been better if the shifts would have been written in the column format. With the times off each off the shifts next to it and the number off employees this way, the data could have easily been extended to the number of shifts that get added as the company grows. If I want to change this data into this format, I would have to manually type each of these entries. But thankfully, Excel has an option that with just a click off the mouse, you will be able to do it very quickly. Let us see how to do it. Let us select the data that we have, right. Click a copy and select the cell where you want it to be pasted, Right Click Goto based special And we have a number off options. Whether we would like the formulas also to be replicated or only values or only formats or only comments. Let us sell it all and click on the check box for transpose and head on. Enter now here we see the entire table has been shifted as we want. There is one movie in Excel where we can do this using formulas. The formula for transposing a particular table is an ari formula. As our data is three rose by nine columns. Let us select three columns and nine rows over here as the data has to be put accordingly. Once we seal it, these cells click on equal to T r a N s p o s e open bracket. Select the table that we would like to transpose close bracket and here is a tricky part in order to convert this formula into Ari. We need to hit on control shift and Indo all together. As we can see now, the formula is not simply written as equal to transpose, even though I three it is written as breast bracket, equal to transpose a one toe I three with a close braced bracket. Now these braised brackets show that it's an airy formula. So as we can see, we can also use Aires formulas to transpose a particular table. Only thing that needs to be added is some formatting somebody you have learned using if Ari formulas transpose shift.