Transcripts
1. Have Microsoft Excel Problems?: Hi, Welcome to Microsoft Excel. Learn the top 50 Excel formula. Sorry, you're starting
out in Excel formulas. This is perfect for you. What you learned from this
course are the following. And then shells,
logic formulas, Matt, datatype or menace,
informational and text format. So much more. So starting learning
Excel formulas now and see you inside.
2. How to Get the Most from this Course: hide it is Brian. Hello and welcome to the Top 50 Excel formulas courts. 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, excellent gives your head and you can see documentation and how it's used. What is it for right? So as long as you know or understand the meaning, those formulas will become second nature to you. Okay, so the first point formula parameters can be anything so it could use to it, like referencing cells or type in your act of value inside the formula parameters. That's perfectly fine, and we'll be showing that a swell later throughout the examples that we have Excel 2010 is fine. Any version is fine. Okay, so if you have 2013 2016 and 2019 our office streets expired. That's pretty fine. Okay, so the good thing with this formulas, it's it's very usable, cost different versions, and you could apply them to any Excel version that you have
3. Text Formulas In Excel: let's discuss the tax functions for the 1st 1 We have come continent where and we have multiple strings or text that we want to combine together. So for just one, we have Hello, Brian Hall and then we have a single string of Texas yourself. Let me did hear once we got in this different self old together, we combined them and then we added spaces in between. So for the continent formula, what we do ISS. We have this texts we add with other text, right, and they just keep on adding them together. So it's trying out over here, it's typing concurrent, innate right. We have the first text run to add a space after debt. Let's get the next texts or next cell at another space and then the last one which is home . And the cool thing is, you could just keep on adding text over here, coz it up and we have the same results. 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 off. The word are, and if we look here, it's going to be 12 tree for five. It's on the fifth character. So we just want the result this fight. So what? Five takes in ISS, whatever text we're trying to find and then Watters d source text. So it's type and find here. And then we want to look for the word are. And then the source Texas this one How are you and their assault ISS five. Now this top about left. So for left, we have this number over here or decide e over here. And then we want to get the first tree characters from the left. Okay? And what it gives us back? Yes. One to treat. So what left us iss from the source texts, right? And then how many letters are characters that you want to take? So if you type in here left and then the source texts and then how many characters do we want? We want to get the 1st 3 characters and now we get one to a tree. Let's talk about Lent. So this is returning the lent off this entire text over here. So if you check this, it just takes in the source text, okay? And that it will give you back the let off that one. So if we go here, which means this excel if school texts ISS for 10 characters long now for lower What it does is if we look here, it just takes in the source text and it converts. Whatever case the Texas sent it converts everything into lower case. Okay, so over here excel. This fun goes into lower case, so type and lower Put in your source texts. If you close deaths. Now you have everything in lower case. Let's talk about mid. So it's a bit more complicated because meat has three parameters. So what mid is good for is it takes in your source texts and then you tell it from which character it to start from 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 I s okay. So what we're doing here is we're trying to extract the ISS from the middle of this texts. OK, so mid takes it from this text. The source start here like from which character to begin with. And how many letters are characters that you want to take? Okay, so that's tried out over here. What we want to do is Smith, this text, okay. And then from where Where do we start? If we count here? 1234567 So we want to start from seven, which is the letter I and then take the next to characterise which would give us i s So that's where Mitt Let's move on the proper. So for proper Okay. What it does is if you look here, it takes in the source text and it converts whatever case that you have and then capitalizes each individual work. She could see her. E is capitalized. Eyes capitalized. See? Is capitalized. Okay. So that for proper let's try it up proper and then taking the techs OK? And then you have your proper case next district so wrapped 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. Hey! And then what we have for direct formula is we want to repeat a tree time. So we just why we have tree over here. So let's try using it right now. Repped and we have your source texts. And then we wanted to be three times and we have Hey, hey, We could actually change this to six times, for example, and it blew result in a lot more. Next this substitute So something should. What it does is it takes in tree parameters. OK, we have your source texts, right? And then what is the text that you want to replace and in Nexus? What? It's the text that you want to replace with it. So, for example, you have us great. So that's incorrect grammar. And what we want to do is change the word ISS into our. So what we're doing here is we have this right, what we want to change. And then this is the replacement, which is our okay, that's trying out for substitute. And then we have the source text. Okay. What is the old text that you want to place in so that it's this and then we want to change this with our Okay, close it up. And now we have your result Next stream. So for trim, what it does is if there's a lot of space, is additional spaces that you have at the beginning and at the end, off your texts. Trim is perfect for this. So trim takes in the source text just one parameter. And whatever additional spaces, it will just remove everything for you from the beginning and the end. So our example has a lot of space, is here at the start and also at the end. So we'll try out trim right now for sore steps. And now you have no more spaces, right at the beginning at the end. Nexus upper. So for upper, it takes it one perimeter, your source. Texas smoke. And what it does is everything inside the text. It gets converted into uppercase case. I could see here. Everything's now capitalized. So what we'll do for Excel is cool. It's type in upper that's taking our source texts, felt it up, and now you have everything in uppercase. Next is value in case. 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 the textile form, right and now checks and converts it into a number. Okay, so a few type in value and then just pass in one parameter, which is the texts. OK, it will give you the numerical number. Okay, but if it's actually a really text, then it'll just give you an error because it's unable to convert it into the proper value. So it's trying out right now. Then 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 air.
4. Text Formulas Exercise: now that's going to exercises. So what we want to do here is we have a couple of examples, right? We have to text. We have the goals, OK? And then what we want you is create your own formula to achieve that goal. OK, so use the text formulas over here and then go to the next video once here, ready to check your work against the answers.
5. Text Formulas Exercise - Answers: Okay, lets go over examples one by one. And then that's where Trudy Formulas. So for the 1st 1 changed the text into all up her case. So that's pretty straightforward. Just used the upper formula taking the text over here. Close it up. And now you have everything in uppercase next oneness. There's a type over here, right? So we want to replace all east with I. So what we could use here if substitute Okay, So what? We want to have its the source texts. What is the old text? Old Texas e and then replace it with I. And now that we just changed his to lower case and now you have the correct text. This is a pipo. Next, this removed t extra spaces. So we have extra spaces in front. We're here, so we'll use the trim formula. It's put in the source text, and now you have the extra space is removed. Nexus, get the 1st 3 characters. So you want to use the left formula that's taking our texts and then type and the tree. Okay, so that you have your first tree characters
6. Mathematical Formulas In Excel: Now that's tough about mathematical formulas. So for the 1st 1 this average So for average, as the name implies, it will get you the average out off the numbers that you specify. So, for example, over here we have 159 and the average would be five. So what you supply an average for the parameters would be numbers and you could just specify multiple numbers inside. So for example, over here and it's type in average. Okay. 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 is five. Another approach iss. To do this a swell. And then you will specify the individual numbers one way one. So I just go comma five, right, comma nine. And if you do this, you will get the same result for average. Now, let's go to hint So for and what it takes in ISS 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 would return to you only want to a tree. So it's like on the left side of the decimal point. So if you look here is gonna be into and then you pass Andy number. So let's do the same thing. Okay, let's select the number. And you have 123 for months. This is moderate, right? So for modular it what it does is you divide a number by an under number and then get the remainder. So if we go for attend about a by treat right there. Remainder is one. So what it takes in? So you see, over here we have the dividend on the left side on the first parameter and then divided by this number or the device, or 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 device, er, where the second parameter and it will give you one. That's for month for Red Ran just returns to you. A random number. McKay. So over here we have ran, right? So it's a random number, which is between zero and one. If you go here right, typing brand. Okay. And it gives you that number, so you could see as well want to be typing. Ran. It just updates everywhere. So if we type in there another random number, all of them gets updated to swell. Let's go to round. So for around what it does ISS, we have 9.675 right? You could select or up to have it rounded to two decimal places. So we just five have 9.68. Okay, so what it takes in ISS the first parameter is the number that you want around, right? And then the second parameter is how many digits after the decimal. So in our example, over here we have round and in the number and then two decimal places. OK, so that's trying to same thing over here. Ground. Okay, said at the number. And we want it to decimal places. So we have 9.6 date. So for this one, let's try a different approach. Let's go for around a swell. And instead of two decimal caters, we want one decimal place, so that gives us 9.7. Okay, Nexus some. So some ISS pretty much just adding all of the numbers together so you could specify multiple numbers in here. Okay, so one parameter for number and other number and it followed by many numbers. Okay, 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. Some just select all of the numbers for that range, and then it will give you 15. And you could do the same approach. A swell with individual numbers. So if I go here right and into turn number and close this up and you get the same result
7. Mathematical Formulas 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 mat formulas and then see what the result this. Okay, so jump over to the next video so that we can answer these questions one by one.
8. Mathematical Formulas Exercise - Answers: Okay, let's go over the questions fund by one. So for the 1st 1 get the average off. 1357 night. So we'll be using the average formula. And it was typing the numbers. One tree right, five, seven and night and averages five. Generate one random number. So let's go for and OK, close it up and that's your random number. Next. This around the number 10.12345 to 2 decimal places. So it's type in round, okay? And that number is 10.12345 and then two decimal basis. Okay, so that's 10.12 Laissus Get the summer off. 13579 So we'll be using these some function and then typing the numbers. 1357 and nine. And that gives us 25
9. Date and Time Formulas In Excel: Now let's discuss the date in time. Formless. So for the 1st 1 is the day formula. What this will do is you put in the date for your parameter inside a day and it will return to you the day component. So if you look at my dates, though my date format, it's actually day month and followed by the year. Okay, so when we're getting the data right now, is the left side off my date? Okay, so if I type in here day and then you put in the date inside, it will return 15 to now. Let's go to our our issue. Pass in a time for the parameter far and it will return to you the hour component. Over here we have 12 59. So what? It will do it. It will return 12 to you. So if you're typing, here are pat in your time and you get 12 next this minute. So similar to our you passing the time right for your parameter. And then it will return to you the minute component off your time. So, in other words, for this one told 59 it will return 59 to you if we type in minute. Okay, let's select the time it will give 50 90. Next. It's month. So for a month, it takes in the date. Okay, so remember my date format over here is actually day month year. Okay, so which means my mom over here iss treat for march. Okay, so we just passing the date to the month formula, and it will return the mont component for you. So it is type in month, nets pass in debate, and you get the month next ISS network base. Okay, so this is more of computing the number off working base from your start date to the end date. OK, so you could see over here that it's taking in two dates, right? So if we go here, we have your start date, so I could see this March 1. And then this is March 31 off 2020. Okay. And then what it computer is it's 22 working base between the two dates. Okay, so we pass in the start date for the first parameter, and then the second parameter s your and date. So free typing over here yet? Work base. We have to start date, and then let's select the and eight 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 we're here, right? Just gives me the time. And you see, it got updated. Swell. Because whatever the current time, miss, that's the exact time that we get. Okay, so that's for now. Next. It's second. So, for second, what it takes in is a time. Okay, 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 former. So it's tried it 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 just remove this and then it's type in to date. and it's July 17 off 2019. OK, so that's the day today. Next, we have Weekday. So for a weekday, Okay, we specify one date as a parameter for this one, and it will return to you. What day off The week is that date? Okay, so if we have this date over here March 15 of 2021 he just called with a formula. Passing the date and it returns to you a number from 1 to 7. One being Sunday. Okay. And then Father by Monday to stay cheddar until seven, which is Saturday. So which means over here to iss a Monday. So if we type it out right now, with day okay, passing the date way have the day of the week next this year. So for year, we just passed in one parameter, which is the date and what? It will return to you ISS the year component. So over here, what we have okay, is this one. We just pass in March 15 2020 and then he just passing the date, and it turns the year component off 2020. So it's type of out year. Select this okay, And you get trade between
10. Date and Time Formulas Exercise: So now we have date and time exercises, so go over the goals. Over here are 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 Formulas Exercise - Answers: Okay, so for the 1st 1 get the date today. So let's go for today. Today formula and we get the day today Nexus 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 of spell. Next this. Get the number off working days between the two dates. So we have two days over here, January 1st and February 28 Over here. The number of working days. So let's go forward. Net work base. We have to start. Okay. And then we have the end date. Close it up and we have 43 working days. Next. This get the year off the date. Okay, So what we're expecting here is 2025 the year to be returned. So that's just type in the year formula, passing the date, and you should get to a house on 25
12. Logic Formulas In Excel: Now let's talk about the lodge formulas First send. So what? I want you to focus first on this grid over here. What we have for end? Yes. We specify a list of conditions and what excel will do. Yes, 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 end what it does, is it evaluate multiple conditions. Okay, so we have here on a if it's true and false, their self this false If it's true and true theirselves, it's true If the conditions air falls and falls, the result is false. And then if the false and true are use an end, then the result is false and simplify this table. What it simply means is for end all off the conditions need to be true and their self will be true. If any one of them is false, then immediately it's false forever. So okay, so what and 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 wants to remember my date over here. ISS day, month year. Okay, so over here is what we're checking for. It is the year 2015 and it's the month. January. Okay. It's the year 2015 and the month, January and her salt is true. Okay, so if we do that same thing are same condition Over here, it's type out. And so you want to check its the year. Okay. Off the state over here. This it 2015. And it's the month off this year. If it equivalent to what? Or January, which is not because this is February. So we expect the result to be false. And which of this next? It's conflict. So for calm black. 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 black cells do we have in this specific list over here? And what we see is to write B B two over here and D two over here Okay, so we have this Sophie used Count Black right now, right? We can specify a range, and the result should be two blanks. Next is tell. If so, for Conniff, what we specify is a specific range and in values meeting disc arterial. And then it will return to you. How many values have met this criteria in the specified range. So if we look here, OK, we have four numbers. What we're doing over here is where checking is. How many numbers are less than 10. Okay, so we have to over here, so let's try it out. Right now. We have town if you specify the range that we want to check. Okay? And then what is the condition? Less than to Let's make it less than 10. And we expect the result to be two. Okay, So if we checked and change it to less than 12 then we expect the result to be treated okay , because this tree numbers are less than 12. Next is if so, for if Okay, if this is true, do this otherwise, do this instead. Okay? So what if will do is it will evaluate this condition first that you specify. And if it's true and it will return or do this result okay, otherwise, that 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, returned past and if not, return fail. No, that's for If so, let's try it out right now. So we have if okay, what the logical test would be. Yes, 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 gonna be a failed. And since this is less than 75 it's going to be a fail. Next is large, so for large, what it takes in, it's a less off numbers, and then you can specify the end largest number. So, for example, if you put its two dead, it will return to you the second largest number if you turn one and that's the largest number. Okay, so I could specify which largest number that you want to return. So we have four numbers over here. What we did with our example, ISS. We have the range of numbers, right? And then we have treat, which has deterred December. And if we arranged them right in this order and descending order, we have 1 50 asked the third largest number. So let's try it out. Right now we have a large it's put in our races numbers, and I want the turd largest number and we get 1 50 Next it's Max. So, Max, it's fairly straightforward. It just gives you the maximum number from a less okay, you specify less inside, and it just gives you the biggest number outright. So if we look over here, right, 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 returned next. This men, So men is the opposite of Max. Instead of getting the maximum number, it gives you the smallest number. Okay, so same thing you specify a less of numbers in it. So over here, what we have from this list of numbers is the smallest number is 100. Okay, so that's tried out for men Yeah, we specify the range of numbers over here, and we get the smallest one, which is 100. Next is not okay. So if we look here, right? Not a too greater than zero. Okay, So not and then you specify a condition. It just gives you the exact opposite. Okay, So what we're doing here is is this greater than zero? OK, so this is this is greater than zero right over here. No, no, it's not OK. But since we enclose this in, not it will return to opposite to you, which is true. So if we go here, it's type out the same condition. Okay. Created in Syria. So first things first. Let's about like this first is discredited. And Joyce 100 greater zero. Yes, true. But since it's not OK, so it returned the opposite, which is false. Next is or so or and end are used interchangeably, but it depends on your needs. Okay. So for or it also accepts a list of conditions. Okay. So what? I want to focus again. It's this specific table. So if we have true or false if you combine them together, it results to true. If it's true, are true, then the result is true. If it's false, are false than result this false. If it's false or true, did the result is true? So let me simply find us again, for or for or okay. If anyone off them is true, then the result this true. If all of them are false, then that's the only time that the result will be false hanky. So let's check this one again. We have dates again. So what we're checking here, remember, this is day month, year format. OK, so it's January 11 2015. So what? We're checking here ISS ISS the year 2015 or if the month may okay, so we just need any one of them to be true for a distribute. 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 up this for this formula. Okay, it's the year after state 2015 four. It's the mutt right off this date may Okay, so the first condition got satisfied, which is troops, so the result should be true as well. Next, it's small. So for small, it's fairly similar to large. The only difference is it takes in less of numbers as well. The only difference is it will return to you the end. Smallest number. So let's say you want the turds Morris number or the second smallest number. It will give it back to you. Okay, so for example, over here What? We have ISS we have this four numbers. Okay, We want to get the second smallest number. So if we write arranged this in ascending order, the second smallest number would be dissed, which is 1 50 So let's try it out small. Okay, let's get the range of numbers. And I want the second someone, this number which is 1 50 next this summit. So for some, if we have this perimeter 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 this four numbers over here, get all of the numbers that are greater than 200 then add them all up Okay, add them together. So for this four numbers, those greater than 200 are 2 50 and 400. Right? If you see this, two numbers over here and the self that IHS 650. So that's tried in action. Some If let's select the strange over here in our condition is greater than 200. And now we get 6 50 s. Well, okay. Last SV, look up. They look up this one of the most use for minutes. Let's go over this example first. So we have this table over here. We have name, gender and age. We have John write the names over here. We have the male female genders and that we have a So over here what we're trying to do. Yes, we want to do. Look up, Given this table over here, we want to look for caddy, and then we want the age off caddy to be returned. Okay, so what we have in this formula iss 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 get me the value in this column that matches that. Okay, so which means we specify tree over here. Which means one to treat return whatever. Got match in the TERT caught up. Okay, so what we're going to be doing right now is we look up, okay. They look off value, which is Kati typing. Carry over here, right? And Dan the entire table. And then the index column. We want the H right and ages. Deterred column. So we'll have to type in treat one thing. I want to know the swell, Miss, We want it to be an exact match over here, So let's just specify false, Which means it will look for the specific value off Catie s well in this table and it will return to age of catty over here. So it's close it up and we get stage of Turkey
13. Logic Formulas 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 where? And it takes in this name value over here. Okay. And then it will return to H.
14. Logic Formulas Exercise - Answers: Okay, so let's work on R V. Look up forming over here. OK, so it's type. And if you look up and then our look up value would be this and then we have our table. So that's just select the entire table in here. And we want to return to age. So which means it's going to be deterred color, 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 that's just play with it. That's changes to run. And we got trade. He eight if we go for Emma and we have 12 okay?
15. Financial Formulas In Excel: Now let's discuss about financial formulas. So the 1st 1 is F B. Which stands for future value. Okay, so we want to get the starting value, and then this is the interests. 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 deep, like, how many payments or how many years in our example. And we have each payment. Okay. What is the value of each payment and then water? STI starting value. So, for example, it's going to be the starting capital off $1000 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 be two would be the number of years and zero for the payment . Because we're not using this for our example or for purpose. An and water sti starting value our capital over here. So for the starting value, it has to be in the negative number. So let's try it out of smell feet and then the rate is 10%. Then that's compute for 10 years. Okay, that's gonna be fair. Too big, given the compounding interest. And then it's type of the payment of zero. Because we don't need that. It's, um, at the moment. And then we have negative one tiles and asked our capital, which is $1000 damn out this close to 2600. So for NPV, it's actually the NPV. So what it will do is given a rate, right? And then you have a less of payments from, let's say, from the present to the future, okay? And then it will return to you. What is the present value off all of this payments? Because money in the future. Okay, assuming there positive interest rate. Yes, lesser ready 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 tree 100 once you get the net person value, Okay. From today, it's only work. $248.6 tonight sense. So what it takes in? Yes, The rate became the interest rate for example, and then the list of payments for your second parameter. So let's type it out. At PV, we have the rate and then for our list of payments for values have this treat numbers over here and we get to 40.69 nexus PMT. So PMT is very cool when it comes to computing. That's a amortisation is like they have more gauge. And it can tell you how much do you need to pay okay for each period so that you can pay off that loan after X number of years, for example? So we have a furtive, simple example over here where we have the capital off millions and then we have the interest rate of 10 years and it's going to be paid over 30 years. OK, so we can use PMT. It takes in the interest rate. How many payments, right? And then what is the amount I could think of the assessed the loan amount, for example. Okay, so same thing, it's well over here we have a to B to C to the rate first number of years or number payments, right, and then the capital or the low and the result is there? So that's type about PMT. Okay, the rate, How many periods or how many years and then what is D amount? And over here, if it's 10 years, then of course, the amount you're paying per year right should be bigger. And just out of curiosity, if you multiply this very turning right, it's three million. OK, so which means the amount that you're paying towards the interest it's pretty big to be able to pay off that loan off a $1,000,000. Okay, so next spp Mt Soapy PMT 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 heart is ations to be mostly being paid towards the interest. We want to pay our capital where our principal slowly but surely okay, because to get great off that no, so the PP empty 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. So let's say which year are do you want to check on what's being paid towards the principle and then out of this many payments. Okay. How many? How many years is this running towards, too? And then for this much amount? Like how much with your principal. Okay, so where you're starting printable. So, for example, over here What? We have ISS ppm tee off 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 be, too, which is the number of years and then see two. Which means the starting loan that we're paying for. Okay, so that's trying out PP empty rate. And we want to check gear number one. Okay. The first period number fears would be turning, and then the little would be a $1,000,000. So which means on year one, we're just paying 6000 towards the capital. That's pretty pretty small, right? That's understandable. Okay, for to start now, if we want to play with this has changed this to last period, which is 30 okay? And it's fairly bigger now on what we're paying towards the principle, because the loan amount gets smaller over time, which means the interest get smaller. Swell. We should supply a bigger portion. Afar amortization now goes towards paying the principle.
16. Financial Formulas Exercise: now for the financial exercises. So go over this. Four questions over here. OK? 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 Formulas Exercise - Answers: Okay, So for the 1st 1 how much will it be 20 years from now? So that's asking for the future value. So that's type in s e. Okay, we have the raid off 3%. We have training years for our number of periods. Okay, Pmt we're not using that for the payment. And in our starting Interpol or capital would be 10,000 So let's coast it up. And which means after trade years, your 10,000 now would be 18,000 books. Okay, so for this one, Okay, we have our loan off 10,000 right now. And how much should be paid okay for each period or for each year to pay off the loan. Now, let's go over here and it's use PMT. Okay? We have our raped treat number appears with the training, okay. And then we have the starting loan off 10,000. So which means for each year you need to pay 6 72 came just out of curiosity. Okay, if we multiply, it was pretty. That'll total amount would be turned in Towson. Next is how much is being paid towards the principal and period number one. And in the next question as well. This how about for pure number 20 just to have a look, So we're gonna be using ppm T You have the rate of 3%. Okay, so the first period. Right? So which means it's one on year number one. Number of periods would be 20. Okay. And then the loan, right? Yes, this 1 10,000 Or this one. It's actually 3 72 Now, if we compared it, which means $300 it's actually being paid towards the interest, because what we're paying per year is 6 72 And then over here, what's being paid towards the principles 3 72 Which means Big Chuck of what you're paying is going towards the interest on your number one now for this morning. We have ppm t. OK, we have to rate. Okay. The period disappeared. Number 20 year 20 over here. Number of periods would be this, and then it alone would be 10,000 out at sea and he could see over here. It's fairly close now, 6 52 right to 6 72 Because most of what we're paying right now is going towards the principle on the final year
18. Informational Formulas In Excel: Now let's discuss about information for munis. So for the 1st 1 we have the column formula. What? It simply returns to us as Modesti column number off this specific cell. So we just specify a cell and returns the call 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 to treat for, which is the fort called him. So let's try it out, column and select this cell and we have number four. Nexus is black, so forest languages specify one value, and it will tell to you, is this empty or not? So if we look at this formula, we have s blank, we returned this. So which is it's empty, right? If we check this one cool application is we can combine this with if formula, because what it will do is it checks, right? It checks for this cell over here. If it's blank, then return to texts or show the text. It's empty. Otherwise return whatever's inside that cell, okay. And this one the smell. If we use that, it's not gonna be empty right because there's hello inside. So which means is it blank? Know if it's not black, then we jump over here and return whatever's inside a four, which is Hello. Now let's try this out right now. If okay, the logical testes, it's blank and we want to check this cell. If it's blank, right, and if it's blank, then retyping. It's empty. Okay? And it's not just returned it value and training. It's empty. Next. This is there. So for is there it just checks for a specific value if it's invalid or not. And if it's a valid there's an error, it returns true. And if there's nowhere in a deterrence false as 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 it's error would return troops. So if we try it out right now, is error right? It's trying dividing this, too. That's perfectly valid, right? So it should return false to us because no errors are found. Next ISS this number. So for its number, what it takes it into a cell or value and then it just checks for you. Is this a number or not? If it's number returned true, If it's not the number, then return falls. So our example over here, right? It checks one to the tree is this number. And since it's a number in returns, true. So let's try it out right now for hello, which it's not number. So the result should be false. Next, this is Tex. So his text takes in the value of svelte. He just checks. Is this text or not? Okay, so for example, over here we have a number of one to treat its this attacks? No. So which is why it returns false. Now let's try it out over here. If tex for hello And it should return true nexus rope. 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 and came 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 its role number two, it returns to. So if if we want to check this cell over here. OK? Which means this is roll number Tree. So let's try it out, bro. Okay, This one. And then he should have turned treat to us.
19. Informational Formulas Exercise: Okay, So for our info exercises, what we have, it's on this left side. We have this sells highlighted over here. Just want to return the role number off this cell on in the column number off this. Okay, So just grab the tree cells over here and populate the row and column numbers, respectively, for this one on the other side. Yes, we have dividend off this side over here for the first column, and then we have the device, her divided two numbers. If it's an invalid operation or if there's an error show, this is not possible. OK, but if it's a valiant operation, then return whatever toe coach in this or the result off the division operation.
20. Informational Formulas Exercise - Answers: Okay, so for this one, let's get the Rove this cell on. It's just typing here. Okay, So this should give us row number four for the column. That's just type of in column. Okay, so this is column number two. Next up, ISS roll number five. Okay. And then this one, we should have column number five. Okay for this one, this is on the six row, and for this phone, we have deterred color. Okay, Now let's try to divide the number. So what we can do is we can use toe if formula, Let's check first if there are errors speaking 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 nowhere, then proceed with returning the division between the two. Okay? So attended by the way to that looks good. That's fine. Let's just dragged us down so that we don't need to type the formulas over and over again. And now we have 500 divided by 25. That's 2010 about radio. Not possible. Syria, about of a zero. No, not possible.
21. Vlookup - Practical Examples: Hi. So we're going to discuss about V. Look up. So I have a more detailed example Over here, you can see on the left side that we have the Starbucks maneuver. I just grabbed, like, you can see the stuff expresses over here. Tall grande, venti. And you have a list of prices over here. So we'll be focusing on the expressive section to show you how he look up. It's being used, and you can see over here like this table. What I did was I simply type of the value it over here, and then we just have a an excel format for this and for my examples below, I'll be focusing on a tall americano so you can see it highlighted over here in a caramel macchiato at 70. So that's $4. And in 19 cents. Okay, So for we look up, the main purpose of feed look up is to simply so from your data table over here, we're gonna be looking for the values off. Very kind of tall and karma. Caddo off. Venti. Okay, you can see over here we have a first, a semi automated solution. So we're gonna use the V look up for this one. Okay, fi, look up. And then we're gonna point at first to the Americana value. So we're going to look for this value, and it's asking for the table area. So I just dragged all the way to the bottom. Okay. And the next is the column index. So which means, which column are we looking for The value for? So you can see over here. I number two columns. 123 and four. So, for example, over here, we're looking for tall, which is called him number two. So we're going to type into okay and enraged. Look up. Iss true. Approximate much. This is only like it's only usable for numbers. If you want to look for a closest value from a given, less off numbers. Okay, but over here, we're looking for the exact value off Americano. So we're gonna use false over here. So closing this one, you're gonna get two points here now, So it is the same as what you can see on the table above. And then we're going to do the same exercise on Carmen mcad. Oh, So first, what value you're looking for? So That's karma, Caddo. And then your source of data, which is the entire Harry. Okay, column, Index, iss. We're looking for Venti, right? So if you look at the column numbers that's called him number four over here. So we're gonna type in four and then race. Look up. We always want the exact match. So let's type in false. Okay, So what is doing right now? If it's going through the table and it's looking for the value of McKerrow caramel McKerrow and then found it over here and that since we told it to look at column number four, it's gonna go to column number four and then give us back the value of $4 in 19 cents. Okay, metiers that. Then you might be thinking right now that hey, look at the form And over here you can see the two and then ask for our formula. You can see column number four, wherein we specified over here, you might be thinking that Hey, how come we didn't use, like, devalues off tone and many over here? It's like we have just hard code at the column. Numbers for RB look a formula. So what? I'm gonna teach you next ISS a fully automated solution. Meaning that we're going to use the values over here and just create a formula. And then there's no need to hard code the columns. Okay, so what I did over here? Yes. I just created a under, like, a data table over here. But this data table contains the columns off tall Grandy and Venti. And then the column numbers are on the right. Take a thing off. I just created a transposing off the cells over here. And now I have told Grand event E 234 Okay, So here's the formula I'm gonna go for if you look up again and gay and then look up, look up. Value it would be the same. Mary Cano table area, on the other hand, would still be this source of data. OK, so it's pretty much the same steps from available. Here's where it changes the column index number we're not gonna hardcore anymore is to what we're gonna do. It's actually perform another fee. Look up. So ever gonna look over here are look up value. It's gonna be tall. Okay, So what you're thinking right now is we want to change the value of Tall into column number two. Which brings us to the other table above. So if you go on comma, our source data right now would actually be this one. Okay, so next is called him the next number. What column number are we getting the data from? Recall that V Look up. Always numbers. Two columns from one to end. So the first column over here is actually one. And the second column is actually number two. Okay, so this this is the column that we're after the 23 and four. So that's gonna be called him, Number two. Okay, So same thing we just want in the exactness for this one. Okay, so now we've actually translate a tall into the second column. So we just go for we're now typing back on the outside, We look up formula, so if you go for comma, it's gonna ask for the range. Look up off the first vehicle. Okay? So let's go for false, and it's close it and you can see over here that actually got the value of 2.0, night, too. And there's there was no need for us to like hard code. The tall column, which is number two. So what I'm gonna do right now is I'll just make this the Q three until 35 This is actually the data for the table off Tall Grande and Venti. So what, I'm gonna do this, make it hard coded. So once we reuse that formula for the other examples below, we could just copy paste the formula. It's fully automated at this point and gay. So what I'll do so I'll just copy this one now and you can actually see that there was no need to type the venti column. We just number four. And it just, like pop up automatically. So just to prove that it's working this, let's just try it out. Mocha. Okay, so what I'll do, I'll copied first over here. So it's still in a because it can't look for a specific value. And, um, it's just this pick a random one. Let's say Grandi, and you can see it got 3.79. Okay, we just actually this value above. So if you want to try it out, let's try Venti and Bingo. It got 4.9 and same four tall. And of course, if it's unable to, like, look for it, it's just type in random value. It becomes any so you can see that it's now fully automated to every even type. Right now, let's say express a tall and it can find Yeah, it's getting the value 1.69. Okay, so for this Excel spreadsheet, I'm also making it down with both so that you can see the exact formulas that I used and you can also fiddle around with it. Okay, Another practical application of V. Look up. It is one thing that I personally use a lot, which is comparing list of values. So I'm actually using the same values from a while ago from the Starbucks example. And if we have two lists and this is just a simplified example, we're just having less than 10 10 values. Which list? But let's see, if you have thousands of items, it's gonna be really hard for you to compare to this and then see what's different between each of them. Okay, so what I'm gonna do if it's actually be, look up again to the rescue. So let's say I just want to check this value and then compare it against this one. So the goal of this was just a C. Is if the steamer value does it exist in this number to think a and then column Index Nam, there's there's only one caught him in this number two, which is call them number one and always were using false for an exact match. Okay, so what I'll do is I'm actually making the list number two as hard coded. So so that I could just quickly copy paste the formula below. Okay, so if a copy pace Okay, so let's copy this one, and then I'll just highlight out of the field here and and pace it. Okay, so you can see over here that steamer is an a karma catalyst and a expresso max. Any this means that the values destry values did They don't exist in less number two. She can see it very quickly without, like manually comparing all of them together. We look up, we just compare or searched item for you, and it makes it like, quick work for us so we can do the same thing for this one. So if we go for if you look up. So now we're checking list number two. Does it exist in less number one? Okay, so I'm clicking on Americano, and then I'll pick this table area over here, and then we'll which call him number. We just said just one column, and then we just go for the useful false over here. Okay, so that's an exact match. Okay. And then before copy, pasting, I'll just our code, These list number one fields, and then I'll just copy this one, and it's just drag it all the way down and pace it. Okay, So you can see over here super coffee that's not exists. And an espresso. Also, it doesn't excessive in this number one. And what I usually do is also assists in the first column. Go for filter over here. And if it's a really long less, what I'll do is sell. Simply filter it out, and then I'll just leave out in a and I can see that. Okay, this this are the items step exist in this number one, but they're not existing in this number two. Okay, so let me put that back up, and then I also do the same thing in this number two and discovered that Okay, disorder, two items that existed number two. But they don't exist in less. Number one hope this can be useful in your everyday work.