Excel: Top 50 Microsoft Excel Formulas!

1. Intro: Hi. Welcome to Microsoft Excel earn the top 50 Excel formulas. So are you starting out in Excel formulas, then this is perfect for you. What you learn from this course are the following financial logic formulas, math, date and time formulas, informational and text formulas, so much more. Start learning Excel formulas now and see you inside. 2. Getting the most of the course: Hi, this is Brian Hung and welcome to the top 50 Excel formulas course. So let's talk about first on how you can maximize this course. So first things first, there's no need to memorize because once you start typing in the formulas, Excel gives you a hint and you can see documentation, how it's used, what is it for. So as long as you know or understand the meaning, those formulas will become second nature to you. Okay? So the third point, formula parameters can be anything, so you could use it like referencing cells or type in your actual value inside the formula parameters, that's perfectly fine. And we'll be showing that as well later throughout the examples that we have. Excel 2010 is fine. Any version is fine. Okay? So if you have 2013, 2016, 2019, or Office 365, that's perfectly fine. Okay? So the good thing with this formula, it's very usable across different versions, and you could apply them to any Excel version that you have. 3. Text: Let's discuss the text functions. For the first one, we have concatenate wherein we have multiple strings or text that we want to combine together. So for this one, we have hello, Brian H, and then we have a single string of text as a result. What we did here was we gotten the different cells together, we combined them, and then we added spaces in between. So for the concatenate formula, what we do is we have this text add with other texts, and you just keep on adding them together. So let's try it out over here. Let's type in concatenate, right? We have the first text. We want to add a space after that. Let's get the text or next cell, add another space, and then the last one, which is home. And the cool thing is you could just keep on adding text over here. Close it up, and we have the same result as well. Let's talk about fine. So for this one on how are you, what we're trying to do here is we're finding the location of the word R. And if we look here, it's going to be one, two, three, four, five. It's on the fifth character, which is why the result is five. So what five takes in is whatever text we're trying to find, and then what is the source text? So let's type in fine here. And then we want to look for the word R, and then the source text is this one. How are you? And the result is five. Now let's talk about left. So for left, we have this number over here or this ID over here, and then we want to get the first three characters from the left. Okay? And what it gives us back is one, two, three. So what left does is from the source text, right, and then how many letters or characters that you want to take. So if we type in here left and then the source text, and then how many characters do we want? We want to get the first three characters, and now we get one, two, three. Let's talk about length. So this is returning the length of this entire text over here. So if you check this, it just takes in the source text, and then it will give you back the length of that one. So if you go here, which means this Excel school text is 14 characters long. Now for lower, what it does is, if we look here, it just takes in the source text, and then it converts whatever case the text is in, it converts everything into lower case. So over here, Excel is fun, goes into lower case. So if you type in lower, put in your source text. If you close this, now you have everything in lower case. Let's talk about MD. So it's a bit more complicated because MID has three parameters. So what MD is good for is it takes in your source text, and then you tell it from which character to start to extract text from, right? And then how many characters do you want to extract? So for our example over here, we're telling mid to get right from this text over here, start from the seventh character, which is actually this one, letter I, and then get the next two characters, which is IS. Okay? So what we're doing here is we're trying to extract the S from the middle of this text. Okay, so mid takes in from this text, the source, start here from which character to begin with, and then how many letters or characters that you want to take. Okay? So let's try it out over here. What we want to do is mid this text. Okay. And then from where, where do we start? If we count here, one, two, three, four, five, six, seven. So we want to start from seven, which is the letter I, and then take the next two characters, which would give us IS. So that's where M. Let's move on to proper. So for proper, okay, what it does is, if we look here, it takes in the source text, and it converts whatever case that you have and then capitalizes each individual word. So you can see here E is capitalized, I is capitalized, C is capitalized. Okay? So that's for proper. Let's try it out proper, and then take in the text, ok? And then you have your proper case. Next is rep. So rep, if we look here, it takes in the source text, right? And then how many number times do you want to repeat that specific text. So over here we have a, and then what we have for the rep formula is we want to repeat it three times, which is why we have three over here. So let's try using it right now. Rep and we have your source text, and then we want it to be three times. And we have hey hey, hey. You can actually change this to six times, for example, and it will result in a lot more. Next is substitute. So substitute, what it does is it takes in three parameters. We have your source text, right? And then what is the text that you want to replace? And then next is, what is the text that you want to replace with? So for example, you have U is grade, so that's incorrect grammar. And what we want to do is change the word is into R. So what we're doing here is we have is, right, what we want to change, and then this is the replacement which is R. Okay, let's try it out for substitute. And then we have the source text. Okay? What is the old text that you want to place in? So that is is. And then we want to change this with R. Okay, close it up, and now we have your result. Next is trim. So for trim, what it does is if there's a lot of space, additional spaces that you have at the beginning, and at the end of your text, trim is perfect for this. So trim takes in the source text, just one parameter, and then whatever additional spaces, it will just remove everything for you from the beginning and the end. So our example has a lot of spaces here at the start and also at the end. So we'll try out trim right now for our source text, and now you have no more spaces, right at the beginning at the end. Next is upper. So for upper, it takes it one parameter, your source text as well. And what it does is everything inside the text, it gets converted into uppercase. You can see here everything's now capitalized. What we'll do for Excel is cool, let's type in upper. Let's take in our source text, close it up, and now you have everything in uppercase. Next is value. Okay? So what value does is it checks if it's a numeric value. We have string, we have a text first, right? It's a number, but it's in a string format or in a textual format, right? And now it checks and converts it into a number. Okay? So if you type in value and then just pass in one parameter, which is the text, okay, I will give you the numerical number, okay? But if it's actually a real text, then it will just give you an error because it's unable to convert it into the proper value. So let's try it out right now. Value. Just type it in here, and that's perfectly fine. But if we try the same thing right now on over here, it will give you an error. 4. Text Exercise: Now let's go into exercises. So what we want to do here is we have a couple of examples, right? We have the text, we have the goals, okay? And then what we want you is create your own formula to achieve that goal. Okay? So use the text formulas over here and then go to the next video once you're ready to check your work against the answers. 5. Text Exercise (answers): Okay, let's go over the examples one by one and then let's work through the formulas. So for the first one, change the text into all upper case. So that's pretty straightforward. Just use the upper formula, taking the text over here, close it up, and now you have everything in uppercase. Next one is there's a typo over here, right? So we want to replace all Es with I. So what we can use here is substitute. Okay? So what we have is the source text. What is the l text? Al text is E and then replace it with I. Now, let me just change this to lowercase, and now you have the correct text. This is a typo. Next is remove the extra spaces. So we have extra spaces in front over here. So we'll use the trim formula. Let's put in the source text, and now you have the extra spaces removed. Next is get the first three characters. So you want to use the left formula, let's take in our text and then type in the tree, Okay so that you have your first three characters. 6. Mathematical: Now let's talk about mathematical formulas. So the first one is average. So for average, as the name implies, it will get you the average out of the numbers that you specify. So for example, over here, we have 159, then the average would be five. So what you supply on average for the parameters would be numbers, and you could just specify multiple numbers inside. So for example over here, let's type in average. And then what I'll do is you can just put in the range of your numbers over here. And it will compute the average as five. Another approach is to do this as well, and then you will specify the individual numbers one by one. I'll just go five, nine. If you do this, you will get the same result for average. Now let's go to Int so for Int, what it takes in is a number, and what it will do for you is it will just return the integer portion, right? So for example, over here, we have 123.99. It will return to you only one, two, three. So it's like on the left side of the decimal point. So if you look here, it's going to be Int, and then you pass in the number. So let's do the same thing. Int. Okay, and select the number and you have one, two, three. For mod, this is modula, right? So for modular, what it does is you divide a number by another number and then get the remainder. So if we go for 10/3, right, the remainder is one. So what it takes in, as you see over here, we have the dividend on the left side on the first parameter and then divide it by this number or the divisor for your second parameter. Okay? So let's do this over here. We have mod. Let's pick this, right? And then we have the divisor for the second parameter, and it will give you one. That's for mode. For RN, RN just returns to a random number. Okay? So over here, we have RN, right? So it's a random number which is 0-1. So if you go here, right, type in RND. Okay, and it gives you that number. So you can see as well, once we type in RN, it just updates everywhere. So if we type in another random number, all of them get updated as well. Let's go to round. So for round, what it does is we have 9.675, right? You could select or opt to have it rounded to two decimal places, which is why we have 9.68. Okay? So what it takes in is the first parameter is the number that you want to round, right? And then the second parameter is how many digits after the decimal? So in our example over here we have round and then the number and then two decimal places. Okay? So let's try the same thing over here. Round. Okay? Select the number, and then we wanted two decimal places. So we have 9.68. So for this one, let's try a different approach. Let's go for round as well. And instead of two decimal places, we want one decimal place. So that gives us 9.7. Okay, next is sum. So sum is pretty much just adding all of the numbers together. So you could specify multiple numbers in here. So one parameter for number, another number, and then followed by many numbers. So it's up to you. So over here, we have one plus five at six plus nine, that would give you 15. So we can try it out over here, sum then just select all of the numbers for that range and then it will give you 15. And you could do the same approach as well with individual numbers. So if I go here, right, and then the third number, and then close this up and you get the same result. 7. Math Exercise (answers): Okay, let's go over the questions one by one. So for the first one, get the average of one, three, five, seven, nine. So we'll be using the average formula. Let's type in the numbers one, three, right? Five, seven and nine. And the average is five. Generate one random number, so let's go, close it up, and that's your random number. Next is round the number 10.1, two, three, four, five to two decimal places. So let's type in round. Okay, and then the number is 10.1, two, three, four, five, and then two decimal places. So that's 10.12. Last is get the sum of 13579. So we'll be using the sum function. And then type in the numbers one, three, five, seven and nine. And that gives us 25. 8. Math Exercise: Now let's go for some mathematical exercises. So we have this goals over here. Your objective is to create your own formula that uses the math formulas and then see what the result is. So jump over to the next video so that we can answer the questions one by one. 9. Date and Time: Now let's discuss the date and time formulas. So for the first one is the day formula. What this will do is you put in the date for your parameter inside the day, and it will return to you the day component. So if you look at my dates, though, my date format is actually day, month, and then followed by the year. Okay? So when we're getting the date right now is the left side of my date. Okay? So if I type in here day, and then you put in the date inside, it will return 15 to you. Let's go to R. R is you pass in a time for the parameter of R and it will return to you the R component. So over here, we have 12 59, so what it will do is it will return 12 to you. So if we type in here, R, pass in your time and you get 12. Next is minute. So similar to R, you pass in the time for your parameter and then it will return to you the minute component of your time. So in other words, for this one, 12 59, it will return 59 to you. If we type in minute Okay. Let's select the time. It will give 59 to you. Next is month. So for month, it takes in the date. Okay? So remember, my date format over here is actually day, month, year. Okay? So which means my month over here is three for March. Okay. So we just pass in the date to the month formula and it will return the month component for you. So let's type in month. Let's pass in the date, and you get the month. Next is network days. Okay. So this is more of computing the number of working days from your start date to the end date. Okay? So you can see over here that it's taking in two dates, right? So if we go here, we have your start date. So you can see this is March 1 and then this is March 31 of 2020. Okay? And then what it computed is it's 22 working days between the two dates. Okay? So we pass in the start date for the first parameter, and then the second parameter is your end date. So if we type in over here, net workdays, we have the start date, and then let's select the end date over here, and we get 22 working days. Next is now. So for now, what it simply gives you is the date and time exactly now, okay? So right now, if we just type in now over here, right, it just gives me the time. And you see it got updated as well because whatever the current time is, that's the exact time that we get, okay? So that's for now. Next is second. So for second, what it takes in is a time, you specify a time to it and it will return to you the second component to you. So in other words, we have 12 59 and 55 seconds, so it returns 55 to you by passing in the time to the second formula. So let's try that right now. Second, I'll select the time, close it up, and you get 55 seconds. Next is today. Today is fairly similar to now. The only difference is it doesn't return the time component for you. So it just returns the date today. Okay? So let me just remove this and then let's type in today and it's July 17 of 2019. Okay? So that's the date today. Next, we have weekday. So for weekday, okay, we specify one date as a parameter for this one, and it will return to you what day of the week is that date. Okay? So if we have this date over here, March 15 of 2021, you just call the weekday formula, passing the date, and it returns to you a number 1-7, one being Sunday, o, and then followed by Monday, Tuesday, et cetera until seven, which is Saturday. So which means over here, two is a Monday. So if we type it out right now, weekday okay, passing the date, and we have the day of the week. Next is year. So for a year, we just pass in one parameter, which is the date, and what it will return to you is the year component. So over here, what we have, okay, is this one. We just pass in March 15, 2020, and then you just pass in the date and it returns the year component of 2020 to you. So let's type it out, select this. Okay. And you get 2020. 10. Date and Time Exercise: So now we have date and time exercises. So go over the goals over here or the questions, right, and then create your own formula. So it's highly suggested that you go through these exercises first before moving on to the next video so that you can see the solutions and then compare them to your answers. 11. Date and Time Exercise (answers): Okay, so for the first one, get the date today. So let's go for today today formula, and we get the date today. Now, next is date and time today. Okay? So the similar one is now because it returns both the date and the time. Let's go enter, and now we have the time as well. Next is, get the number of working days between the two dates. So we have two days over here, January 1 and February 28 over here, the number of working days. So let's go for net workdays. We have the start date, and then we have the end date, close it up, and we have 43 working days. Next is get the year of the date. Okay? So what we're expecting here is 2025, the year to be returned. So let's just type in the year formula, pass in the date, and you should get 2025. 12. Logic: Now let's talk about the logic formulas. First is. So what I want you to focus first is on this grid over here. What we have for is we specify a list of conditions, and what Excel will do is it will evaluate the conditions one by one and it will return either true or false to you. So if you need to check on something, this is very useful for you. So for, what it does is it evaluates multiple conditions. So we have here on A. If it's true and false, the result is false. It's true and true, the result is true. If the conditions are false and false, the result is false. And then if the false and true are used in, then the result is false. And to simplify this table, what it simply means is for, all of the conditions need to be true, and the result will be true. If any one of them is false, then immediately it's false for the Okay, so what Ann checks for is it's all or nothing. Everything has to be true for it to be true as a result. Otherwise, if there's something that's false, then immediately it's false. Okay? So, over here, we have this date, right, January 1, okay? January 1. Remember my date over here is day month year, okay? So over here is what we're checking for the year 2015. And it's the month January. It's the year 2015 and the month January. And the result is true. Okay. So if we do that same thing or same condition over here, let's type it out and. So we want to checks the year of this date over here, is it 2015, and is the month of this year, is it equivalent to one or January, which is not because this is February. So we expect the result to be false and which it is. Next is count blank. So for count blank, if we look over here, what it specifies is we need a list to be provided to count blank and what it will do over here, right? So we're checking how many blank cells do we have in this specific list over here. And what we see is two, right? B, B two over here, and D two over here. Okay? So we have this. So if we use count blank right now, right, we can specify a range, and the result should be two blanks. Next is count F. So for count F, what we specify is a specific range and then values meeting this criteria. And then it will return to you how many values have met this criteria in the specified range. So if we look here, we have four numbers. What we're doing over here is we're checking is how many numbers are less than ten. Okay? So we have two over here. Okay. So let's try it out right now. We have count F. You specify the range that we want to check. Okay? And then what is the condition? Less than two. Let's make it less than ten, and we expect the result to be two. So if we check and change it to less than 12, then we expect the result to be three. Because these three numbers are less than 12. Next is if. So for if, if this is true, do this. Otherwise, do this instead. So what I will do is it will evaluate this condition first that you specify, and if it's true, then it will return or do this result. Otherwise, it will return the other result. So if we look here, what we're trying to do is we're evaluating this grade and checking if it's greater or equal to 75. That's our condition. If that's true, return pass, and if not, return fail. That's for if, so let's try it out right now. So we have I. Okay. What the logical test would be is, let's type in greater than 75. Okay. And it's going to be passed if it's greater than 75, if not, then it's going to be a fail. And since this is less than 75, it's going to be a fail. Next is large. So for large, what it takes in is a list of numbers, and then you can specify the nth largest number. So for example, if you put in two, then it will return to you the second largest number. If you return one, then that's the largest number. Okay. So you could specify which largest number that you want to return. So we have four numbers over here. What we did with our example is we have the range of numbers, right, and then we have three, which is the third largest number. And if we arrange them right in this order in descending order, we have 150 as the third largest number. So let's try it out right now. We have large let's put in our range of numbers, and I want the third largest number, and we get 150. Next is Max. So Max is fairly straightforward. It just gives you the maximum number from a less. You specify less inside and it just gives you the biggest number outright. So if we look over here, we have Max and then we specify the range of numbers. So let's try it out right now. And if we look here, the biggest number is 400, so that should get return. Next is Min. So Min is the opposite of max. Instead of getting the maximum number, it gives you the smallest number. Okay? So, same thing. You specify a list of numbers in it. So over here, what we have from this list of numbers is the smallest number is 100. Okay? So let's try it out for min. We specify the range of numbers over here, and we get the smallest one, which is 100. Next is nut. Okay, so if we look here, right, nut a two greater than zero. Okay, so nut, and then you specify a condition, it just gives you the exact opposite. Okay. So what we're doing here is this greater than zero. Okay? So this is greater than zero, right? Over here? No, no, it's not. Okay. But since we enclose this in nut, it will return the opposite to you, which is true. So if we go here, let's type out the same condition. Okay, greater than zero. So first things first, there's a value is first. Is this greater than zero? Is 100 greater than zero? Yes, true. But since it's not, okay? So it returns the opposite, which is false. Next is. So and N are used interchangeably, but it depends on your needs. Okay? So for O, it also accepts a list of conditions. Okay? So what I want to focus again is this specific table. So if we have true or false, if you combine them together, it results to true. If it's true or true, then the result is true. If it's false or false, then the result is false. If it's false or true, then the result is true. So let me simplify this again for, for, if any one of them is true, then the result is true. If all of them are false, then that's the only time that the result will be false. Okay? So let's check this one again. We have dates. Okay? So what we're checking here, remember this is day, month year format. So it's January 11, 2015. So what we're checking here is is the year 2015 or is the month, May. Okay. So we just need any one of them to be true for this to be true, right? So which means this is going to be true because that's right. The year is actually 2015. So let's type it out this for this formula. Okay? Is the year of this date, 2015. Or is the month, right? Of this date. May. Okay, so the first condition got satisfied, which is true. So the result should be true as well. Next is small. So for small, it's fairly similar to large. The only difference is it takes in a list of numbers as well. The only difference is it will return to you the nth smallest number. So let's say you want the third smallest number or the second smallest number, it will give it back to you. Okay, so for example, over here, what we have is we have this four numbers. Okay? We want to get the second smallest number. So if we, right, arrange this in ascending order, the second smallest number would be this, which is 150. So let's try it out. Small. Okay. Let's get the range of numbers, and I want the second smallest number, which is 150. Next is sum I. So for Sum if, we have this parameter wherein we specify a range of numbers, and then we specify condition because we want to look for the values meeting this criteria. Okay. And then once we have those numbers, it will sum them up together. So let's go over this example. What we want is with these four numbers over here, get all of the numbers that are greater than 200 and then add them all up, add them together. So for these four numbers, those greater than 200 are 25400, right? If you see these two numbers over here, and the sum of that is 650. So let's try it in action. Suth. Let's select this range over here, and our condition is greater than 200. And now you get 650 as well. Okay, last is VloAp. VloUp is one of the most used formulas. Let's go over this example first. So we have this table over here. We have name, gender and age. We have John, right, the names over here. We have the male, female genders, and then we have the age. So over here, what we're trying to do is we want to do a look. Given this table over here, we want to look for caddy, and then we want the age of caddy to be returned. Okay? So what we have in this formula is the first parameter is this value. What value are we matching from the first column? Okay? And then in this list, which means we specify the entire table that contains our data. And then get me the value in this column that matches that. Okay? So which means we specify three over here, which means one, two, three, return whatever got match in the third column. So what we're going to be doing right now is look up. The lookup value, which is carry. I'll type in carry over here, right then the entire table. And then the index column, we want the age, right? He is the third column, so we'll have to type in three. One thing I want to note as well, is we want it to be an exact match over here. So let's just specify falls, which means it will look for the specific value of Katy as well in this table, and it will return the age of Caddy over here. So let's close it up and we get the age of 30. 13. Logic Exercise: So for this logic exercise, I want you to have this table. We have name, gender and age, and then I want you to write a formula wherein it takes in this name value over here, okay? And then it will return to age. 14. Logic Exercise (answers): Okay, so let's work on our V look up formula over here. Okay? So let's type in Vlookup. And then our lookup value would be this. And then we have our table. So let's just select the entire table in here, and we want to return the H. So which means it's going to be the third column. And then we just specify false so that it will be an exact match when it comes to searching for the name. Okay, so we have 25, so let's just play with it. Let's change this to run, and we get 28 if we go for Emma, and then we have 12. Okay? 15. Financial: Now let's discuss about financial formulas. So the first one is FV, which stands for future value. Okay? So we want to get the starting value and then this is the interest. And after a couple of years, what would be the future value be? So what we specify over here is the rate, okay, then the payments, and then what is like how many payments or how many years in our example? And then we have each payment. Okay? What is the value of each payment? And then what is the starting value? So for example, it's going to be the starting capital of $10,000, and it's going to be placed in for two years at 10% interest. What will be our future value? Okay? So we have the rate, okay, specified first, A two, and then B two would be the number of years and then zero for the payment because we're not using this for our example or for purpose. And then what is the starting value or capital over here? So for the starting value, it has to be in the negative number. Okay? So let's try it out as well, a fee, and then the rate is 10%. Then let's compute for ten years. Okay? That's going to be fairly big given the compounding interest. And then let's type in the payment as zero because we don't need that at the moment. And then we have negative 1,000 as our capital, which is $1,000. And the amount is close to 2,600. So for NPV, it's actually the net present value. So what it will do is given a rate, right? And then you have a list of payments, let's say, from the present to the future, and then it will return to you. What is the present value of all of these payments? Because money in the future, assuming there's a positive interest rate is lesser already in the future. Okay, because the money that you have right now has more value as compared to what you have in the future. So he's why even though this is 300, once you get the net present value, okay from today, it's only worth $248.69. So what it takes in is the rate, the interest rate, for example, and then the list of payments for your second parameter. So let's type it out and PV. We have the rate. And then for our list of payments or values, we have these three numbers over here, and we get 248.69. Next is PMT. So PMT is very cool when it comes to computing, let's say, amortizations, let's say you have a mortgage, and it can tell you how much do you need to pay, for each period so that you can pay off that loan after X number of years, for example. So we have a fairly simple example over here where we have the capital of 1 million, and then we have the interest rate of ten years, and it's going to be paid over 30 years. Okay. So we can use PMT. It takes in the interest rate, how many payments, right? And then what is the amount? We could think of this as the loan amount, for example, same thing is available over here, we have A two, b2c2, the rate first, number of years or number of payments, right? And then the capital or the loan. And the result is there, so let's type it out PMT. Okay? The rate, how many years or how many years? And then what is the amount? And over here, if it's ten years, then of course, the amount you're paying per year, right, should be bigger. And just out of curiosity, if you multiply this by 30, right, it's 3 million, okay? So which means the amount that you're paying towards the interest is pretty big to be able to pay off that loan of $1 million. Okay, so next is PPMT. So PPMT is pretty cool because it allows you to calculate, Okay, on how much are you paying towards the principal at a specific point in time? Because for monthly amortization, for example, we don't want our amortizations to be mostly being paid towards the interest. We want to pay our capital or our principal slowly, but surely, okay, because to get rid of that loan. So the PPMT will allow us to compute that and it takes in this parameters over here, we have the rate at this rate, okay? And then on this payment, let's say, which year do you want to check on what's being paid towards the principal? And then out of this many payments, okay, how many years is this running towards too? And then for this much amount, how much is your principal? Okay. So your starting principle. So for example, over here, what we have is PPMT of A two, which is our interest rate, okay? And we have one because we want to check on year one, how much are we paying towards the capital? And then B two, which is the number of years, and then C two, which means the starting loan that we're paying for. Okay. So let's try it out. PPMT. Okay? The rate. And we want to check year number one, the first period. The number of periods would be 30, and then the loan would be $1 million. So which means on year one, we're just paying 6,000 towards the capital. That's pretty small, right? That's understandable, for the start. Now, if we want to play with this, let's change this to the last period, which is 30, okay? And it's fairly bigger now on what we're paying towards the principal because the loan amount gets smaller over time, which means the interest gets smaller as well, which is why a bigger portion of our amortization now goes towards paying the principal. 16. Financial Exercise: Now for the financial exercises, go over these four questions over here. We have our interest rate, years and capital here on the left side, and then I want you to create your own formula so that you can put this into action. 17. Financial Exercise (answers): So for the first one, how much will it be 20 years from now? So that's asking for the future value. So let's type in FV. Okay, we have the rate of 3%. We have 20 years for our number of periods. Okay, PMT, we're not using that for the payment. And in our starting principal or capital would be 10,000. So let's close that up, which means after 20 years, your 10,000 now would be 18,000. Okay, so for this one, we have our loan of 10,000 right now and how much should be paid for each period or for each year to pay off the loan. Now, let's go over here. Let's use PMT. Okay. We have our rate or three. Number appears to be 20, okay? And then we have the starting loan of 10,000. So which means for each year, you need to pay 672. Okay. So just out of curiosity. Okay. If we multiply it with 20, the actual total amount would be 13,000. Okay? Next is, how much is being paid towards the principal in period number one? And then the next question as well is, how about for period number 20? Okay, just to have a look. So we're going to be using PPMT. We have the rate of 3%. Okay? So the first period, right? So which means it's one on year number one. Number periods would be 20 Okay. And then the loan, right, is this one, 10,000. Now, for this one, it's actually 372. Now, if we compare this, which means $300 is actually being paid towards the interest because what we're paying per year is 672. And then over here, what's being paid towards the principal is 372, which means a big chunk of what you're paying is going towards interest on year number one. Now for this one, we have PPMT. Okay, we have the rate. Okay, the pier is per number 20, year 20 over here. The number of periods would be this, and then the loan would be 10,000. Now, let's see. And you could see over here it's fairly close now 652, right to 672, because most of what we're paying right now is going towards the principal on the final year. 18. Informational: Now let's discuss about informational formulas. For the first one, we have the column formula. What it simply returns to us is what is the column number of this specific cell? We just specify a cell and it returns the column number to us. So for example, this one, we have this cell over here, which is in the first column. So if we try for this cell, it would be one, two, three, four, which is the fourth column. So let's try it out. Column. Let's select the cell, and we have number four. Next is blank. So for blank, we just specify one value and it will tell you is this empty or not. So if we look at this formula, we have S blank. We return this cell, which is it's empty, right? If we check this, one cool application is we can combine this with the I formula because what it will do is it checks, right? It checks for this cell over here. If it's blank, then return the text or show the text, it's empty. Otherwise, return whatever's inside that cell. Okay. And then this one as well, if we use that, it's not going to be empty, because there's hello inside. So which means is it blank? No. If it's not blank, then we jump over here and return whatever's inside A four, which is hello. Now, let's try this out right now. I o the logical test is it's blank, and then we want to check this cell if it's black, right? And then if it's blank, then we type in it's empty. Okay? And then if not, just return that value. And true enough, it's empty. Next is is error. So for is error, it just checks for a specific value if it's invalid or not. And then if it's invalid, there's an error, it returns true. And if there's no error, then it returns false. It's a quick way for you to check. So for example, over here, we're trying to divide one by zero, so that's an invalid operation. So which is why is error would return true. So if we try it out right now is error, right? Let's try dividing this two. That's perfectly valid, right? So it should return false to us because no errors are found. Next is this number. So for this number, what it takes, it is a cell or a value, and then it just checks for you, is this a number or not? If it's a number, return true. If it's not a number, then return false. So our example over here, right, it checks one, two, three, is this a number. And since it's a number, it returns true. So let's try it out right now for Hello, which is not a number, so the result should be false. Next is Itex. So Itex takes in a value as well. It just checks. Is this text or not? Okay? So for example, over here, we have a number one, two, three. Is this a text? No, which is why it returns false. Now, let's try it out over here. Is text for hello, and it should return true. Next is row. So for Row, what it tells you is it gives you the row number for that specific cell that you specify. So it takes in a cell for the parameter and then it just gives you the row number. So for example, over here, we're checking this cell. Right. And for this one, since it's row number two, it returns two. So if we want to check this cell over here, o which means this is row number three. So let's try it out. Row. Okay. This one. And then it should return three to us. 19. Informational Exercise: So for our info exercises, what we have is on this left side, we have the cells highlighted over here. Just want to return the row number of this cell and then the column number of this cell. So just grab the three cells over here and populate the row and column numbers respectively. For this one on the other side is we have a dividend of this side over here for the first column, and then we have the divisor. Divide the two numbers. If it's an invalid operation or if there's an error, show this is not possible. Okay? But if it's a valid operation, then return whatever the cotient is or the result of the division operation. 20. Informational Exercise (answers): Okay, so for this one, let's get the row of this cell, and let's just type in here. Okay, so this should give us row number four for the column. Let's just type it in column. Okay, so this is column number two. Next step is row number five. Okay. And then this one we should have column number five as well. Okay. For this one, this is on the sixth row. And for this one, we have the third column. Okay. Now, let's try to divide the numbers. So what we can do is we can use the I formula. Let's check first if there are errors on the division operation. So let's divide this up by this value over here, right? If there's an error, what we need to show is this is not possible. Right. But if there's no error, then proceed with returning the division between the two. Okay, so ten daba by two, that looks good. That's fine. Let's just drag this down so that we don't need to type the formulas over and over again. And now we have five t divided by 25, that's 2010 dibta by zero? No, not possible. 0/0. No, not possible. 21. Thank you: Thank you, and congratulations on completing this course. And I hope that this has helped you in learning the top 50 Microsoft Excel formulas. So you're also able to leave a review on this course simply by clicking on the reviews link over here, and you would have the leave review button enabled for you. So simply click on this button to leave a review on this course. So please, please leave an honest review as I would love to hear your feedback. You can also check out my profile by clicking on my name over here, this link. To view more Microsoft related courses. So once you have clicked on the link, it brings you to my profile page, and you can continue your learning journey by going through my top Microsoft courses.