Succeed in Excel 2013 - Master formulas | Dan We | Skillshare

Succeed in Excel 2013 - Master formulas

Dan We, Anything is possible

Play Speed
  • 0.5x
  • 1x (Normal)
  • 1.25x
  • 1.5x
  • 2x
31 Lessons (1h 29m)
    • 1. Word of caution

      1:27
    • 2. The If formula - How to deal with conditions

      2:08
    • 3. If Part 2

      4:04
    • 4. Sum formula with a condition

      3:21
    • 5. How to sum with several conditions

      5:00
    • 6. Let us count

      2:18
    • 7. Let us count with several criterias

      2:01
    • 8. The counta formula

      1:21
    • 9. Max min small large ?

      2:45
    • 10. The Vlookup function

      2:23
    • 11. Vlookup Part 2

      3:31
    • 12. Hlookup - the vlookup brother

      2:05
    • 13. Match formula

      1:46
    • 14. Vlookup with several tables

      4:49
    • 15. Index & match

      3:00
    • 16. Days and time

      4:39
    • 17. Sumproduct formula

      4:17
    • 18. Array introduction

      1:58
    • 19. Basic statistical formulas

      2:44
    • 20. Excel for finance - Future Value

      2:40
    • 21. Excel for finance - Pmt formula

      1:44
    • 22. Excel for finance - Rate formula

      2:10
    • 23. Excel for finance - Nper formula

      2:12
    • 24. Excel for finance - Net present value

      1:56
    • 25. Excel for finance - Internal Rate of Return

      1:58
    • 26. Excel for finance - XIRR & XNPV Formulas

      3:20
    • 27. Goal Seek

      2:29
    • 28. Data Tables

      5:31
    • 29. Consolidate data

      2:25
    • 30. Forget Filter use Dsum instead

      2:48
    • 31. Create a dynamic chart

      3:42

About This Class

You will learn the most important formulas in MS Excel 2013 as well as using them in the most efficient way. 

I designed the course in a way which offers students with different excel knowledge the perfect environment to increase their skills and become proficient with one of the most important tools microsoft has to offer.

The course is structured in short efficient lectures with valuable content. Any student can decide on his/her own how fast he or she wants to complete the course.

Transcripts

1. Word of caution: Hi, guys. Before we start with this course about Microsoft Excel 2013 and beyond I wanted to tell you about would I want to give you a word of caution. All right? The thing is, I'm using the German version of Microsoft Excel because I'm from Germany. And, um, if I use, for instance, a function like, for instance, some, um, and I would select ah, two values like G two and the second number, for instance. Um, the bonus year. Okay, I would separate them with a seamy column, as you can see here. Okay. And close parenthesis presenter and I could calculate the sum. Um, if he using normally ah, American version of Microsoft Excel or maybe other kind of version of excel from other country. You might only use a comma instead of a semi coma. Um, but as you can see here of a presenter, that doesn't work for me. Okay, So, um, depending on your version of excel Ah, whenever I use a seamy column, two separate, um, values in formulas, you will use a comma. All right. Just important to know if you're following the course. All right? Thank you. And see you in the next video. 2. The If formula - How to deal with conditions: Hi, guys. In this first lecture, we won't talk about the If function. All right, now, let's go. Now, what we have here is a table, and here we have some names which are salespeople, and we can see their sales. All right. And now we want you to term mine whether our sales people receive a bonus for this particular sale they made. All right. And in order to get the bonus, they need to exceed this level, this threshold off $500. All right, so now we will insert equals if and this is helpful tool in excel. Because here you can see formula, gives you some kind off help and tells you what it does. All right, So now if you put press the tap, then we will check a logical test, and we will check whether the sales exceed so, uh, greater sorry than the level the threshold. And since we copy it down, we will lock it. All right. So press the F four key to lock this level. And now what if the level exceeds? Well, if the sales exceed the level, he will receive the bonus or she really receive a bonus So we will use this G three and again because we copied down press at four toe lock it. Okay. And if not, he will receive zero. All right, so we just type in zero. Okay, Now we close parenthesis and we presenter. And now we get Go back up here and use this year. Double click, and we'll send it down. And now we see who of ourselves. People will receive the bonus because his sales exceeded the level of 500. All right, so this was the first formula. We learned it function and can't wait for the next lecture, so stay tuned for more CIA guys. 3. If Part 2: Okay, guys. What? Come back. Let's continue our journey. Shall we? Now? In the last video, we were dealing with a function rate. But now let's come. Captain ate the function with two other functions. So the or function or the and function. All right. Now let's see in this table again, we have our sales people and we can see their sales. And we can also see whether they were able to acquire new customers or not. Now, this time we will be a little bit mawr. Strict. All right, So we will say our sales people will only receive the bonus if their sales exceeded the threshold. So the level of $750 they also acquired new customers. Okay, so how do we do this? We will use the and condition in on comm, Captain eight with a functional right. So, again, type and equals if press tab. And now we have to check to conditions. Right? So this time we will use the and function because both conditions must be met. So type in and press tap. And now the first logical test is whether the sales here and exceeded the level. And since we will copy down, after which press a four to lock the cell. Okay. And the second condition is whether he or she was able to acquire new customers. So whether the cell C two is equal to And now since we're dealing with text, we have to put it in quotes, So type end quote. Yes, quote. Okay. And these are two conditions. So close parenthesis. Now we close the and function right, And this is the logical test. So the first parameter of the function. And now what if these conditions are met? Well, then they will see received the bonus. Okay? And again, we will copy down. So pressing four to lock it. And otherwise they will. They will receive zero, right? So type in a zero. Okay, I close the parenthesis, and we're done. So percenter. All right. And now we will copy down, and we will see this time we'll only two of our sales people then and Mindy who whose sales exceeded the threshold and who were also able to acquire the customers, receive the balance. Okay? And all others will. They don't receive anything now. The second formula, I wanted to show you is the or function. So if we say that, I'll says people will receive the bonus. If their sales exceeded the threshold or they were able to acquire new customers. Only one condition of the two has to be met. We just simply get rid of the end. And it set. We will use the or type or And Chris Tap. And that's it. All right. So now, with or function only one off the conditions within the parentheses off the or function must be met in order. Well, to receive the bonus in this case. All right. So if a presenter and of course you have to copy down, double click. All right. And now this time, as you can see, much more of our sales people receive a bonus. For instance, Christina, because while her sales did not exceed the level but she was able to acquire new customers and also great, right. Okay, so we pay had the bonus. All right, this was second capture chapter of their function hopefully aligns with the new and as always, stay motivated. Stay on track. Keep going with the course and hopefully see you soon in the next lecture. Thank you for watching 4. Sum formula with a condition: Hi, guys. And welcome back. Now, in this video, we want to talk about the some function. So, um, as you can see here again, have created a table with names off salespeople a rate and can see their sales. And this time we want to know how much sales has then generated. So all his sales, all right, and what we will create, we will use the data validation tool okay to create a drop down list. So at first, we have to select all the names in our table. Right. Then I will copy those names controlled, see to copy and control V to Paste Here, for instance. Controlling. All right. And this time I will go to data here, and I will remove duplicates. All right, so off the column H here. Okay, okay. And now I have only single names here. Okay? And when I will go here, and I would want to create a drop down list with these names, All right? So I go to data validation here, and I would go to that of elevation and I will not allow any value. Only will allow a list off values, all right. And the source of this list. This one here. Okay, No click here. Click. OK, now we have created our data validation. I'll drop down list here, so only you can select the names from the doctor. Rob Thomas. Okay. And just in case you don't like this or to see this here this list, you could also create the list on another sheet. Okay, So you would not be able to see this list on this feat that now that's not the main part of the video. So doesn't matter. Okay? And now I want to know the sales in total for Mindy. And in order to calculate this, I will use to sum it function so type and equals some if. Okay, press tap. And what we need is a range A criteria for this range and some range. So the range, all the names. Okay, So So, like, all the names from all table here and we do not have to lock it because we wouldn't will not copy down. Okay. And now we need a criteria for the range, while the criteria is the name. So select D three. Okay. And the some range is this one, so the sales okay, selected. And no, we just have to close the parenthesis press center. And now we have the some off all the sales from Indy, okay? And without a drop down list, which would create it now, we can select all salespeople, for instance, Dan, and you can see all the sales for then. Okay, so this was the first part of the summit function. And, as always, stay tuned for more interesting videos and functions from Microsoft Excel. Follow the course and thank you for watching by. 5. How to sum with several conditions: hi, guys, and we'll come back now. In the last video, we were talking about the summit function rate. But of course, we could also have the case that there are more conditions which have to be met in order to some, the range right. And therefore, like yourself, created the some dysfunction. Okay, No, let's start here again. We have our table with names, all right? And we have the sales. But now we also have a column called Region, which tells us where the sales will were created. Right? So United States, Germany, UK in India and so on. Okay, now, actually, this video is quite similar to the summit Function video because at first again, we will create data validation will use the data validation tool to create a drop down lists for name on four region. Okay, so again, let's repeat it. Just select the range controlled CD. Copy it, control V to paste it here and go to data. Remove duplicates. Comb. L Okay. And we get it. And we also do this for the region. So select this. Okay, Control. See again. Here. Control the to paste and remove duplicates. No. Continue of the current selection. Remove duplicates. Okay. Okay. And this interesting, Because here is concede have made an error. And I'm gonna show you why United States is here and here. Okay, so two times. So just delete this and click here ago and select the range here, Control. See? And again here. Control V paste. Go to remove duplicates. Continue the curse election and my data has headers. No, don't select this. Okay, Press, OK? And as you can see now, United States only appears one time. Okay, so this is interesting, because this is a common mistake, which happens quite often. So you've seen it here and now. You won't do the mistake. Okay, now great. Now again, Go here and we go to here. Data validation, Click Data Validation. And again select the list. And for the names we will use there was Okay. Breasts. Okay, again, we're done. And for the region, the same procedure again. Data validation. Click here, and we also use list. And the source of the list here is this one. Okay, press ok. And we can select the region as well. And now type in equals some. If here, that's a sale specific by a given set off conditions. So several conditions. Okay, breast hap. And now the arguments are in a different well, rhythm, as you can see here. Okay, so some range all the sales. So this one here, select the range and the criteria range one. All the names. Okay. Select the names and the criteria. Is this one here? Okay. And the criteria arranged to is the region, so select the region table and criteria for the region is here g three. And now if we have additional criterias, we could also use them. So basically there is no end, but here we only have two. So now we close to some its function press enter and we have all the sales which Mindy created within the United States. And now we can play along in Germany in UK made zero in India 300 Japan, or just use damn made no sales in Japan and so on. Okay, so this was the second part of the summit function to some ifs function. So, yes, if you have more criteria than one and use this function and as a waste, stay tuned form or keep learning and join me on the course on Yeah. Thank you for watching By 6. Let us count: Hi, guys. Welcome back now, In the last two videos, we were talking about some if in some dysfunction. Right now, if there was some if off course, there's also account. If right now in this video, we just want to count all the numbers which are greater than this 333. All right. So just simply type in equals. Count if and here again counts the number of self in the range which meets the given condition. So one condition used account if press tap and we have selected range This one here and the criteria is here this 333. But now we want to count if all the numbers which exceed this value on In order to do this , we need to type in in quotes. Greater then so type. Include. And now we need to come, Captain eight this greater with a cell. And in order to do this, we will use the and percent sign and then select the cell. Okay. And no, just type in. Well, close the parenthesis. Okay. And presenter. And that's it. So we can see that only two numbers and as we can see here the 453 and the 789 which are the two numbers who exceeded the 333. So important lesson here is that first, of course, the county function. But if you're type of two, you can get back in the formula. And the important thing here is if you need to come, captain, it a greater or greater or equal or smaller whatever. But if you can cabinet a sign with a cell, you need to use you put the signing quotes, OK? And you need to n percent sign to come. Captain eight with cell. Okay, so let's accounting function. Thanks. Watching em as always, keep on learning, Stay tuned for more and thank you for watching See you guys. 7. Let us count with several criterias: Hi, guys. Welcome back. Well, as I've promised, if there's accounted function, of course their souls to account ifs function. Right. So what if we want to count? Ah, well, a range And we want to count with several conditions. So we will use the count ifs function, so type in equals count if seer so accounts a number with a given set of conditions. So several conditions used account eighths function. Okay, press tap. And the criteria range, of course, is our range like this one selected. And the criteria the first criteria. Well, we want all the numbers which exceed 50. Okay, so we top in again as we fluent in the previous chapter in quotes, greater quotes, ankle caffeinated. We have to sell here, all right. And the criteria range too. Again, of course, is the same range selected. And the criteria to is smaller. So again in quotes, smaller fruits and come caffeinated again. Then 100. All right, C four and now close the parenthesis press enter and we have 10 numbers which a greater than 50 and smaller than 100 in our range. All right, so that was the countess function. And thank you for watching again. Stay tuned for more interesting videos. Keep following the course and you will be an excellent professional in no time, believe me. All right, So see you soon. Bye. 8. The counta formula: Hi, guys. Welcome back in this quick video, we want to talk about another interesting function called the counter function. Okay, so just go to an empty cell type in equals counter. And what does this function do? Well, it counts the number of sales in a range that are not empty. Okay, so press tab. And now we select, for instance, the whole com one just have to click here. So if this black down arrow appears, just click. OK, so you've selected the whole column bone A Okay. And now just close parenthesis and percenter and we will get the value 14 because we have 14 names here. Okay, So if we would delayed, for instance, Leo would get 13. We type in something else, for instance, Here. 123 We get 14. Okay? Because no matter if it's text or if it's a number that counts all the values which are not empty. So that's the count affection. Okay, So, as always, people learning keep keep on going on the journey with me and looking forward for the next video. CIA guys by 9. Max min small large ?: Hi, guys. Welcome back in this video we will talk about some really basic functions and excell rate. So if you already know them, you can just skip the video. But if not them, come with me and let's explore them. All right, So if you could see again, I've created range here and in this range there are seven numbers and now we will use some basic functions you can see here and calculate some values. So the 1st 1 we want to calculate the maximum off this range. Okay, So just stoppin equals Max. Annals returns a lot of value. Select the range here, close parentheses, press enter. And with the Mexico, the same with the men. Now the men okay and select the range again. Close parentheses press enter and we get the minimum value. So there's also a small and a large function show. Why is that? Well, I'll show you. He quotes small OK, and now will select the range. And now we can select which smallest value do we want to check? So for instance, the first smallest, then that's the same as the men. Right? So press enter, it will get the three But what abouts? Press f two and get back into formula? The second smallest. Well, that's still three. Why? Because there are more than 13 Here's a three, for instance. And he has three. Okay, so if I would change this one here too, I don't know. Six. Then second, smallest reality is the four. Okay, so here. Okay. And the same with the launch function. So instead of the maximum value we could use large and we will select the range. And now we could select okay persons to so the second largest value close burn. Today's press enter and we see that 453 it's the second largest value. Okay, in the last function, of course, with some function. So typing equals some. So we will sum up the whole range All the numbers close parentheses, press center and get some. So leave for some basic functions and excel. And if you haven't know them before now you know them. All right. So that's don't waste any more time. And let's go to the next video. Join me there. See you 10. The Vlookup function: Hi, guys. And what? Come back in this video. We want to talk about one of the most support functions which basically everyone needs to know the V look, a function. So in case you don't know it now join me in this video. Okay? Now, as you can see, I have created gim table with names, country and a city. And here we want to check which person lives in which city. Okay. And in order to do this at first we will click here and we will create a drop down list for the names. So go to data that a validation collect out of elation and we will select the list with our names, the source, all the names here. Okay. And close this like, okay. And we have our names. And now we will use the V look, a function so type and equals V Look up. And what does this function do you? Well, the biblical function always searches in the first column. Okay? And we look of value. Is the name okay? Because we want to search for the name, the table. Just select the table here, the whole table. Ok. And the column index. Well, we want the city. So this is the third column, right? 1st 2nd 3rd So type in three and now type in the folds to get an exact match or chew toe for an approximate match and we will deal. Deal with this later on. Okay, so for now, just type in faults or zero. So zero also means false. One also means to do so if a type of zero I gotta faults and close the parenthesis press enter. And we were Sief Las Vegas Dance them in Las Vegas And if we're, for instance well, Jook Xiao, where's our living? Ah, in Shanghai. Okay, so this is a vehicle function or introduction to the vehicle function as a waste. Stay tuned form or keep on learning and we'll see you in the next video. Okay, thanks. 11. Vlookup Part 2: Hey, great to have you back. Now. In the last video, we've seen kind of introduction to the V. Look, a function. And we were using the exact match. Right? And I tell you that there are also situations where we have to use the approximate match. And in this video, I want to show you one of the situations. OK, so SNC here have created table with sales, okay, and 40 sales. We want to calculate the commission personage. Okay. And here we have all table and we have sales figures here. And if they're greater or equal to this sales number, then he can have you can see the commission. Okay. So again, go to the cell, beat you top in equals V. Look up. Okay. And we're looking for this value 30,000 in that case. Okay? And the table ary is this one here? Okay. Just selected. And since we want to copy down afterwards again, we have to press at four to lock it. The column index is the second, because here we find the commission person, such figures. Okay, so type in two. Oh, too. Yeah. And now we use approximate match. Okay, so type in true or one. So I tap in the one because it's shorter and close parenthesis. And the reason why we use approximate match here is because we have a sales figure 30,000 and well, this saves figure really appears here in the table. But what about 46,000 for instance, does not appear here, right. So if you would use exact match, we would get an arrow message here because there is no 46,000 that table. But now, since you use approximate match type in enter and double click to send it down, Okay, we will get 5%. And that's why if we type in a one so approximate match, we would also we always search the table and with the approximate match, were searching for greater or equal. Okay, so 46,000 for instance, is greater or equal to 30,000 and so we'll get 5%. The 52 for instance, is greater than 50,000. So we get this 10% all right, so that's in a situation where you have to use approximate match and you have to type in a one or type in true okay and the last thing in this video to calculate the commission. Because this is the main task. Typing equals the sales figure times. Oh, sorry. Times the commission rate, and we get the commission and again, double click to send it down. And we have calculated commission. We have done our job and learned something new, right? So, as always, at first, thanks for watching and stay tuned form or interesting videos and keep on learning. Guys, stay with the cores. Thank you. See you. 12. Hlookup - the vlookup brother: Hi, guys. Welcome back. Glad you joined me again. Now, in this video we want to talk about the h look of function. So in the last two videos, we learned about the V look of function. Um, the exact and the approximate match. But there's also aged look of function. So if you take a look at this table, the table looks quite different from the last two tables we've seen. Right? And here I've already created that. That a validation, as you can see here for all the names in our table. And since we've done this a few times before, you should be able to do it yourself. But in case you're not, go back to the videos and watch them again. Or just put a comment in the comment section, and I'm gladly will help you. All right, so now let's again take a look at the city. And in order to do this, we will use the h look and function so type and equals h look up. Okay. And again, you can see a brief description of our formula type in the tab. And the look of value is the main here. The table ary Just select the table. And, well, the be looking function was looking in the first column. So I call him a okay of our table. And the H look of function is looking in the first row off our table. So this one and that's exactly what we want here, right when we're searching for the name and well, the row index, which rose do we want to have or search for? Well, we want the city. So we're searching in the third room. Okay, so just type in three. And here again, we want to have an exact match. So type in zero or false and close the parenthesis presenter. And we got the city for Mindy and again with the dead. A validation tool. Let's see what Joey's living always living in Sydney, and this is true. All right, so that's all you have to know about h. Look up. So thank you for watching and see you in the next video. My 13. Match formula: Hi, guys. Welcome back in this short video, we want to talk about the match function. And this is also really interesting function. Because later videos we will concur. Captain eight this function and use it together with the V Look of function. Okay, now, what's the match function? Basically doing? Well, let's find out. Okay, so just stop in equals match, okay? And here you have your brief description. It returns the relative position off on item in an array that matches the specific value in a specific order. So just type in a tap, OK? And the look of value is your dan. Okay? They look up array. I will select it. This one, okay. And the match time. Well, we're searching for an exact match. Okay, so type in a zero close parentheses. Press enter and we will get a two. So why do we get to Well, then is on the second position in this table. Ok, so, again, you can play around with this. For instance, Alexander in eight. Okay, so we will get a number with the match function, and as I've said before, this will be interesting because we can use this function and canned caffeinated with a look of function or the index function. And that's what we see in the next videos. All right, so stay tuned. Stay interested. Keep on going with me and let's find out. Okay. Great to see you and see you soon. Thanks. 14. Vlookup with several tables: Hi, guys. Glad to have you back now in this video. We want to come, Captain. Eight functions we've learned in the videos in the provision before. Okay, so I'm talking about the V look of function, the match function and choose function. Now, as you can see here, I have three different tables. Okay. Where I want you get some information from Okay, for instance, here, for he created that of allegations. Now the table. Because we have three tables, the revenue, the information we want to get in the year now, since these tables are look exactly the same, normally we could just put all these information one table and use a simple you look up function, right? Of course, we could do this. But now in this video, would you want to come? Captain ate the functions. Okay, so I suppose this is not possible. Now, let's just use it Equals V. Look up, all right. And the look of value, what we're searching for. Yes. You can see here the information which we want to get the table array. Well, right now we have three different tables, so we need to choose our table right and so typing shoes OK, the index number is a table we select here. And the values or the first table ary is this one. The second table ary is this one and the third table re for the B Look up function. It's this one. Okay, so now we will choose the table a rate close the choose function and the column index. We will use the match function to the town on the column index because the column index depends on the year we select here. Okay, so type in m match press tap. The look of valley is the year and the liquor beret. Well, the array depends on the table. OK, so we will use the choose function again type in shoes. The index number is defined by the table we select and the value well for the match function were searching for right range is the first strange is this one The second range is this one for the second tale and the third range is this one for the third table. Ok, so we close to choose function okay. And we're searching for an exact match, so type in a zero to close and close the match function, and now we're backing off. You look a function. We have chosen our column index with a match function attitude in combination with the choose function, and we're searching for an exact match. So type in a zero close parenthesis. Press enter and we get in in a Why do we get in a Well? Because in a second table there is no revenue for 2008 rate. It's in the first table, so if we select he at the first table, we will get the revenue for 2008. And for instance, if we select third table and the revenue for 2000 and 15 we get the revenue for 2015 right ? We could also look up some kind of other information on that income, and we get the net income for 2015. So now in this specific example, for instance, if you do not want to see um, an error message here, what you could do is you could use another function in front of the the look of function. So it's called the if era function. So if you type in an if ero here, so this function checks whether this is an arrow and if this is an arrow, we could type in something instead of this arrow Missed, for instance. Um zero. OK, then close parenthesis Press enter and we would get zero because there is no net income 2015 in the first table. OK, so there was just an example where we can caffeinated all the functions we've flown before and hopefully have learned something. Hope you Hopefully we were able to follow the course and thank you very much for following the course, as I said, And as always, stay tuned, keep on learning and I'll see you in the next video by 15. Index & match: Hey, guys. Nice to see you again in this video. You want to talk about the index function? Now? What does the index function do? Well, the index function is a little bit or quite similar to the V. Look, a function. Now it's concede here ever table and here already created some drop down list with name and here named Country City. You can see here. Yeah. Um, and now that's used index function. Okay. Just type in equals index. Okay? And now we needed no rate, which is our table. So just select the table here, and index function needs a row number and a column number. Okay, so just type in a row number. For instance, we could type in the row number two, okay. And the color number three and close parenthesis. And we get Las Vegas. Why? Because we're searching in this Ray and we're searching in the second row here and 1/3 column here and we get Las Vegas. Okay, Now, instead, off typing in numbers, we could also Captain ate the index function with a match function. Okay, so type in equals match. Oh, sorry. Equals index. Okay. And Ari again is our table. But this time a row number. We use the match function, and the match function is looking for for the ruse. It is looking for the name here from our drop down. At least the look a beret is this one. Okay. And we're searching for an exact match type in a zero close to match function. And now we have select the row number with the match function for the index function. And for the color number ah, number. We will do the same. So type in match and the look of value. Is this one here? Okay, look, a beret is this one okay? And we're searching for an exact match. Happened a zero again close a match function. Now we have selected the column function, and now we just need to close the next function. Okay. And now if you present her, we'll get fronts because we're searching for Leo and we're searching for the country and we get fronts. And with city, for instance, it's Paris. So that's the index function. Ah, in concatenation with a match function. So as a waste. Keep on learning. Stay tuned for more interesting videos and hopefully I'll seal you in the next video. Thanks 16. Days and time: Hello again in this video we want to talk about days and time functions in excel which also exist and sometimes can be quite useful. Okay, now let's start. The first thing, of course, is the today function. So which day is it today. So type and equals today returns to current date close parenthesis and we get the date. Okay, The next function is the now function. So typing equals now. So which gives us the date and the time here. You can read in the description. So close parenthesis. You don't need to type in any perimeter. Just close the parenthesis percenter and we get the date and the time. And now to other functions. The first is the workday function. Okay, so typing equals workday. And if we type in a start date, for instance, this one here today. Okay. And days. So for instance, 20. Ok, 20 days and holidays in this time period of these ones here. Okay. So close purposes. And we will get the date. Okay, So this is the end date with workday function. So if we start today, we have 20 work days and we have, of course, these holidays in between then, the last day when we finished, our task will be this one. But if use the workday function, you need to keep in mind that it will count always Saturday and Sunday as weekend. And so they do not count is working days. Okay, so if you want to change this, you could use another function called the Work Day International function. Okay, so here we also have a start date. For instance, today you could also type in 20 days. But now we could also select What is the weekend? So is it Saturday and Sunday? Normal. And we would type in a one, and we would get the same result as here. Okay. For instance, type meant one. And here you can see the other for since so they only Friday only. Okay, so 17 to sort of the only. Just remember that, for instance. Okay, type in one. So now the weekends and the holidays again, we will use those. If we close parenthesis, we should get here. This number. So in case in case you get something this it just means that it's not former. All right? So if you go here and you tell it it's a date here. Then you get the date. Okay. Now, this is same as before. He said, because Saturday and Sunday is holiday. This is the standard. Okay. Our weekend. Sorry. So if you change this, he had to 17. Which was when we take a look at this Saturday only, for instance. And percenter. We get the second off, August, Um, so we will finish earlier. And just in case, if you are struggling with the date here, um, well, I'm from Europe, so exactly. I'm from Germany. So this is Ah, the date. Ah, former. It's quite different from us. Former. I know. So just in case you're do not get this, Okay? So just let's summarize what we've learned here. Well, we talked about that today. Function the now function, the normal workday function where we could select holidays. And the weekend is, um, considered as days off. Okay, Saturday and Sunday. Or if we have for different kinds off weekend. Too short a weekend, for instance. Here only. Saturday is the day off. Then we could use the Work day international function. All right, so thanks. Watching and stay tuned form or interesting videos. I'll see you in the next video. Okay. Can't wait. Thanks. 17. Sumproduct formula: Hi, guys. Welcome back. Now, in this video, I want to talk about one of my most favorite functions in excel. There's some product function. Interested. Well, you should be okay now. What you can see here is we have a table and we have our customers. And they ordered a certain amount of products and he would get the price for a single unit . Okay. And with us some product function, what we can basically do is multiply the order amounts, times the price and just some all of these up. Okay. For instance, if top in equals some product, OK? And then we select the order mount, and people would select a second array the price here, and then we close it. Who would get 11,004 and 23 we could also Oh, no, sorry. We could Ah, here. Just for minute. Okay. So this is the Ottoman Times. Surprise. And the sum of all of those, the total amount. Okay. Now, in this example, we also want to calculate the total amount, but only for Jack. Okay, so we have 1/3 column here, third ary, and when we need to find out, Jack okay. And we can do this with some product function as well. So just again, type in some product, OK? And that's a used the Saray its 1st 1 So it doesn't matter with what array you start. So you just use the old amount, the secondary again. It's the price. Okay. And now the third ary is this customer, um range. Okay, But we need to search for Jack and this a special kind of way. You do it in excel, and I'm gonna show you how just type in minus minus okay than open parenthesis. Then we select the range here and type equals and the cell we close support parenthesis and clothes, some product function press enter, and we get the total amount only vote check. Okay, so take a look at this. Where's Jack? Jack only appears one time in the whole list, OK? And four times 90 is 360. Okay, Because select an or others. So the important thing here is some product function itself, which is quite useful, but also, if you're dealing with text and you need to use it as a criteria, you have to put it in parentheses and you need to type a double minus four, OK? Because if Ugo here, if I click here on the function, then you see that using this minus minus year, What we can see is that the some product function is creating zeros and ones, so only if a condition Ismet. So if use if the function is finding Leo off Winston's, then it would type in a one and into some product function, as I've told you before, then it would calculate one times they're rate the valiant array two times the value Honore one. Okay, so always, if there's a zero than, of course, the value zero. Okay, so that's basically behind this kind of strange looking array three. But, um, you just have to use it a few times and I promise you get used it. Okay, so that's basically about some product fishing. And thank you for watching 18. Array introduction: Hi, guys. And welcome back in this video, we'll want to talk about a ray functions. Okay, Now, what is an array? Basically, it's for instance, this kind of range here is array. An array calculation would be if we would calculate here the product of these values and these values just in one formula. Okay, to calculate all these all these values in the range. Okay, so at the end here, nine times nine is 81. So there must be an 81. And one times one, for instance, is one. There must be a one. Okay, so how do we do this? We just select the range here, and then we just type in equals, and we'll use our first range here and times because we multiplied with second range here. Okay. And then we just talked. Press control shift. Enter. And we have calculated old valleys here, just with one command. Um, the problem here is that, um because it's in a reform earlier. You can see here if I click in the cells that there's always the same formula inside. And we cannot change single values. For instance, I cannot enter this cell and ah, type something else like I don't know. 1123 persons. I will get a narrow Okay, because, um, it's part of memory. And if we calculated it as an array, we cannot change single values in the array afterwards. Okay, so that's basically an introduction about a raise. And, as always, stay tuned. Keep on learning. Stay on the cores on the journey with me together and I'll see you in the next video by 19. Basic statistical formulas: hi, guys. And what? Come to short statistics introduction now And this pretty you will want to talk about the media. The mean so so basic statistical functions. Okay, So in order to calculate the media, for instance, for this sample which have created here, just stop in equals medium, okay? And then we just have to select okay? The sample and close the parenthesis, press, enter and with calculate the media. Now, in order to calculate the mean, we will use another function cold average, so equals average. Okay. And again, we select the sample like no, simple like this. Okay, close parenthesis, percenter. And for people who are familiar statistics, you will see that the mean really strongly the first from the medium and the reason. Of course, it's because we have this outlier here in our sample. So in order to calculate a batter mean, um for instance, we could use the average if function so that would mean, for instance, equals average. If here and as you've seen in ah, other tutorials like the count, if function, for instance, we have ah condition criteria. Okay, So the range is this one, and the criteria is and as you've seen in other tutorials, you have to put it in quote smaller and can captain ated with value For instance, 1000 OK, because, well, the outlier is far beyond 1000 and all other world is a smaller 1000 and the average range will be the sample range again. Okay, selected close parenthesis presenter. And now we get a better estimator here for the mean because we ah, just picked out the outer layer. Okay. And there's also an average ifs function as you can see down there. So if you have more than just one criteria um, condition. Okay, so there was some basic introduction. Ah, for excellence with statistics. Um, thank you for watching. And I see you in the next video by 20. Excel for finance - Future Value: Hi, guys. Welcome back. Now, in this video, we want to start about talking about financial functions and Microsoft Excel. And the first financial function is the future value function. Okay, Now, in this table, you can see that I have, um, were created a rate, which is 6% interest, which is currently well, the dream, because the interest environment is so low. I know. But here we get the 6% a number of periods here, which is 10 years at payments which we make, which is $100 I put in the a minus sign before the $100. And the reason is because excellent considers a minus as a payment we make, which so this in that way posit for us because it's a payment we made we put into our buying bank account. Okay, so if you put some money in the bank using minus in front of the money with some okay, and the present value of zero, So basically, in this example, we have a bank account which currently, well, the money zero in this account, and we will pay $100 each year for 10 years and we get a 6% interest rate, okay? And we want to calculate the well the future value. So the amount of dollars we get at the end of those 10 years, Okay. Ah, with the interest, we also get so just type in equals future value. FV open. And then you get the arguments. Just use the rate 6% the number of periods. 10 years, the payments minus 100. Because we put it in the bank account, the present value of zero and the type. This is just the type of interest. So Ah, do we When do we pay or no payment time at the end of appeared? So this is the default value. Okay. Zero. So that would mean that we pay on the last day of first year, okay? And if you type in a one, we will pay the beginning of period off course. Then we got one times mawr interest. Okay, so but let's just use the default value of zero close parenthesis. Press enter. And at the end of the 10 years, we get $1318.8 sands back. Okay, so that's the first financial function future value. Thank you for watching and is always keep on learning and stay tuned form or interesting videos. CIA guys. 21. Excel for finance - Pmt formula: Hi, guys. Now, in the second video off the financial function sections, we want to talk about the payments. Okay, function now in this example here, I have again set up a table. We have a rate off 3% this time, so interest rate came down Shit. At with a number of periods, which is five years now, we have a future value of 10,000 and a present value of zero. So that just means at the end of the five years, we want to get $10,000. Okay? And we know that the interest rate of 3%. So the question is, how much money do we have to pay in these five years? Um, to get the future well of 10 thousands at the end of this those five years. Okay. And here we can use the payment function. Should just type in equals pmt. Okay. Here use the rate which is given the number of periods which is five years the present value, which is zero, and the future Well, you be warned, which is 10,000 and again, the type just type in zero because we paying at the end of the period if we closed parenthesis, then we get the value of minus 1830 $83.55 which just means with a minus sign again that we have to pay this to the bank into our accounts to get 10,000 that yet of the period right off those five years. Now that's the pay empty PMT function on and thank you for watching by. 22. Excel for finance - Rate formula: the next financial function we want to talk about is the rate function. Okay, Now, in this example, we have a present value off $180,000 in our bank account. Okay? And what we want basically is we want to draw $25,000 each year for 10 years from our accounts, and the future value should be zero. Okay, so well, we want to draw all the money out of our account. 25,000 each year for 10 years. And what want calculate is the interest rate we need to achieve this. Okay, because right now we have 180,000. And just consider this example If we want to have 25,000 for 10 years, we effective were effectively We will draw 250,000 from this account. Right? So our account needs to grow, so we need a certain amount of interest rate for this 180,000 OK? And in order to calculate the rate, we just will use the rate function. Okay, So type in equals rate, the number of periods, 10 years, the payment, the money we draw from all Bangor Countess, This 25,000. The present value is 180,000 and the future value is zero. The type is we will draw now. Well, we will will draw at the beginning. OK, this time. And we just close parenthesis and just presenter. And that means we need an interest rate environment off 8%. So 8.1816% interest on our bank account in order to draw 25,000 each year for 10 years and have a value of the end off zero in our account. Okay, that's the rate function. Thank you for watching. 23. Excel for finance - Nper formula: Hi and welcome back. Now, in this video, we want to talk about the number of periods function, and in order to do this, I've created an example here. As you can see, we have a present value off $1 million. Okay? And we have a rate of 2%. So this is interest rate. We have payments of 60,000 and a future value of zero. So basically, what you want to calculate here is the following. Think about that. You get $1 million in in your back account, you got an interest rate environment of 2% and you need $60,000 each year. These are the cost off. Living for you. Okay, Andi, I guess you could also have some fun with so much money. Okay, on what we calculate is how many years can we do this? So draw 60,000 from our bank account of one million if we have an interest rate environment of 2%. Okay, so just type in equals number of periods and then just type of the parameters. So the rate is 2% The payment he is 60,000. The present value is one million. The future. Well, you is zero okay and type. We will draw the money at the beginning of the period. Okay, so here we just assume that we will put all 60,000 on the first day off the year out of our account. Okay? And we close parenthesis and presenter. And then we get in 1998. So almost 20 years we can draw each year. 160,000 from our bank account of one million, which starts with one million. Of course, in the second year, it's less because, of course, we draw 60,000 my account. Right? But we can do this for almost 20 years until we have no money left. Okay, So so much information for your retirement. Thanks for watching. See you in the next video. 24. Excel for finance - Net present value: Hi, guys. And welcome back. Now, in this video, we want to talk about the NPV function in Microsoft Excel as you can see, here we have Ah, the chance to do an investment off 50,000. OK, and here we have cash flows for the next three years. Okay, so the first year, we will receive 20 thousands in the second year. 10,000 and 1/3 year, 40,000 OK, from this investment and we have an interest rate so off 6%. So this out benchmark. Okay, so we want to earn more than 6%. Okay, now let's, um well calculate than the personnel. You Okay, So topping equals NPV NPV. Okay, so the rate is 6% and the values we get here Ah, these three okay. And we close parentheses. And of course, we have to subtract the investment. And here, in a NPV function, you cannot use the investment, the upfront investment directly. So we have to subtract it afterwards, Okay? And since is already a negative valley here, just type in plus this value, okay. And presenter. And as you can see here, we get a positive. Um, NPV. Okay. And that just basically means that it is a good investment, because considering the sex 6% a discount rate we use, um, we have a positive NPV, and so we should do this investment. Okay, so that's the NPV function. Thanks for watching. See you in the next video. 25. Excel for finance - Internal Rate of Return: Welcome back in this video. We want to talk about the i r r function. So the internal rate of return function. Okay, um, now, again, we have an investment off 150,000 and we have our cash flows, which we get in the future years. Okay? And now we want to calculate the i r r, which is basically the discount rate. Ah, which Ah, when If we would discount all our cash flows with this i r r. This discount rate, we get a present value off 150,000. So exactly our investment. Okay, that's the I R. So if you calculate this ir are typing equals I r r and here Ah, we could select all values. So it's not like the NPV function where the investment at the beginning, we have to subtract afterwards. Here we can select all the functions together and just close birth. This is don't use a guess and press entered, okay? And now I can see the I r. R is 6.53% which just means that if we discount our future castles with this 6.53% then we get exactly the investment off one 100 and 50,000 at the beginning. Okay, that's the I R. So, for financial professionals, that just means that if these this ir rain, this inflow rate of return is greater than what we expect from the investment. Oh, the percentage figure we we want. Then we would do the investment. Okay, that's it for this video. Thankful watching and, as always, stay tuned for more. Thanks. 26. Excel for finance - XIRR & XNPV Formulas: Hi, guys. Welcome back in this video we want to talk about the ex i r r and of the axe and PV function. Okay, now, as you could see here, I have, um, an original investment okay, off June and 50,000 and have future castles. And now I want to calculate the internal rate of return, and I want to calculate the NPV. Okay. But the problem here is with the normal functions I r r or NPV. I only calculate I can't calculate the right, um, present valley all the right internal rate of return if I receive the cash flows at the end of the period. But here, as you can see, I received the castle's not at the end off each period, but on certain dates between, like, the first of November or here, the 23rd off July. So, in order to calculate this, I have to use ax I r r and X and P V. OK, so for the ex i r function just type in equals ex I r r. And here in description. You see what it is? Okay. And I can use select all the values, including the original investment and I can select all the dates Animals just close the parenthesis press enter and I have calculated my internal rate of return. So if a discount these cash Nell's at these specific dates. Okay? Discounted with this. Ah, I received. Ah, the present earliest exactly 250,000. Ok, so for the x NPV function, just type in equals X and P V. The rate the discount rate is 20% in this example the values Ah, these ones. And remember when we used the NPV function, we could not include, um, the original investment. Okay, we could only calculate it for the cash flows. And then at the end, we have to subtract the original investment. Right? But here we can include it and the dates just select the dates, close the parenthesis, press enter, and we've calculated the NPV and off course. This valley here is negative because our discount rate is 20%. And as we've seen here, the internal rate of return is 17%. So since our internal rate of return here is smaller than our discount rate, um, the investment is not a good investment. Of course, because considering our discount rate, ah, for this investment, we would lose $16,827.78. All right, so so much for the ex i r r and the acts NPV function. Okay, So thank you for watching end stay tuned for more interesting videos. See you in the next by 27. Goal Seek: Hi, guys. Welcome back. Now, in this video we want to talk about gold seek, which is a special tool which is implement in excel. You can use on and it's really powerful. Okay, now let's take a look at the example here again. At first, we have to calculate the future value. Okay, Now, we have done this before, so just type in equals future value. Put in the parameters. A rate, a number of periods, the payments off six thousands each year. Present reality of zero. Okay, annals we pay at the end of each period. Okay, Close parenthesis for center, and we receive our future value. Now, what if we know today that we want to have a future value off exactly $100,000? Okay, but we want to know. OK, if in 10 years we want to have $100,000 as future value in our bank account, how much do we have to pay each year? Okay, so we want to change the payment to get exactly 100,000. How do we do this? We use the gold seek. So what you have to do is you go to data. OK? click. What if analysis and go to gold Seek. Okay. And well, set cell. Okay, well, what cell do we want to set? Where we want to set exactly this cell, which is already selected in case it is not, Of course, just like here. And go to the cell. Okay, Now we want the future sale, okay? And we want a value off. Exactly 100,000. Right? Okay. And what do you want to change? Well, the payments were made. Okay, So click here and click. OK? And it has calculated. Okay, take okay. And has he seen here? Now the payments has changed. Now, who makes a higher payment off? $7586. 80 cents each year for 10 years on a 6% annual interest. We get exactly a future value off 100,000. This is the power off the gold seek. Thanks for watching to see you in the next video. 28. Data Tables: Hi, guys. Welcome back in this video we want to talk about the data tables which are another powerful tool implemented than Microsoft Excel to calculate scenarios. All right, now let's start, shall we? Okay, now, let's take a look at this example. We have an investment of 50,000 a rate of 6% and we have our cash flows at the end of the years. 123 Okay. And with the NPV function, I've already calculated the NPV. You know how this works from the purpose video right here. Now, what I want to know here is OK now I've calculated did for the rate of 6%. But what if I have a ray change here, for instance, I have different kind of rates. Okay. What is the NPV for these kind of rates or here have different kinds off, um, origin investments. Okay, So what is that present value for those or I want to see both in one table. OK? And in order to do this, of course, we could just change it here for interest, if instance type in, um of five. And we calculate the nippers well for 5% right? But we could also do it with data tables and have it in a really nice former at once. Okay, So how do we do this? In the first scenario here, we want to calculate the NPV for different kinds of rates. Okay, so we just need to link this cell here to our Neporent Valley formula, So just type in equals. This never involved formula. Okay, percenter. And now what we do is we select our whole table. Go to what if analysis which you could find under data, OK, and go to data table here. And what we want to do is we want to put you can select the row input cell in column into itself, and here we want to put the values from the row. So these values here, okay. And we want to put those row values in this sell here, okay, and just press OK, and we're done. So we have calculated here the different kinds of net present values for different kinds off interest rates. And here exactly the 6%. It's exactly 11,352 like you see here. Okay, Now the same can be done like this. Then what we do here is again tot equals that at present Valley here. So if you kept this kind of table, for instance and again, we would select the table would go to what they've analysis go to data table. And this time off course, we have a column. Right? So the column e here are values column bilious. And we have to put those column values in this cell here press OK. And we've calculated the net present values here for different kinds off original investments. Okay, Now, if you want to have both Okay, so four different kinds off investments and different kinds interest rates who want to see , um, what NPV. Well, we get at the end again. We would have to link to our formula here. Okay. And then we have our table, and we need to select the table and again go to what if analysis data table the row input cell again. This is the road, OK? And we want to put the row in here and be too right. So just select me to and the column inputs ill column H In this case, we want to put those values in here and be one. Okay, click. OK. And as you can see here, we have calculated all the different kinds off. Well, net present values we get at the end Well, for these kinds of difference in areas. For instance, if we have only an investment of 40 thousands and an interest rate of 5% or a discount rate of 5% year, we would get a NPV of 22,000 and so on. Okay. And if you click inside, you can see that this Cody brackets here. So we have a ah Honore formula here, which we have talked about before, So you would not be able to just change single values year. You could get a narrow. Okay, you can't do this. But still, the data table here is a powerful tool in excel to create such kind of really nice overviews. And in case you didn't know, now you know it. You've learned something new. Thank you for for take a look at this video and, as always, stay tuned form or interesting and exciting videos. Thank you. See? Assume 29. Consolidate data: Hi, guys. Welcome back in this video. Want to show you the consolidate, which is also on implemented function Excel you could use, um, is going to be here of created two tables, one for January and 14 February. Um, for states people, their sales and the customer they sold their products to And, um, normally, in order to consolidate them, I could just some them up, of course. Raid. Then I would just type in in my third total table here to some off those for Jerry in February. But, um, in case you want to do it differently, you can also use the consolidate. So you just would have to go to data here and could consolidate. And here you can select which kind of functions you want again? I used to some here and the reference Well, the first ranges. This one here. Right? So select, um, and at it. And the second, um, yes, this one here. I select this and added to and, um well, basically, what I would have to do is just click. Ok, um, here. You could also create a links to source data, which you would have to do because um, later on, if you change something in the table, um, it only changes. Ah, in your consulted a table if you select this. But you can only select this if you do your consider the table on a different sheet. So if it would select it right now, here and click OK, I would get a narrow message because I cannot create the consolation link on the same sheet . Okay. Um, so ago, if I go back to consolidates and just be selected here in this example click. Ok, um then would calculate the sum. Right. Okay. But as I've told you, if it would change something here, um, for instance, to 60 than tail table would not update because, as I said, um, I have not created a link. OK, so that's the consolidate function. Um, basically, now you know about it. But normally you just would use to sum function to do this. Okay, Still, thanks for watching the video and see you next 30. Forget Filter use Dsum instead: Hi, guys. And welcome back in this video, I want to show you the d some function. Okay, Now, I've created a table again with a person, their sales, and the month when the sales appeared. Okay, And here have, ah criteria range. And he I will use a decent function. So, for instance, if I say I want only Dan and I only want sales, which ah, greater than 600. Okay, um and but the sales should not exceed, um 1000 OK, And the month, I only want to see them for february. Now, these are a lot of criterias, right? And for here can use d some function, so just type in equals de some press tap. Well, the database is this table here. Okay? The field which I want to sum up all the sales. So just select the sales in this table and the criteria when I've set them up here, right? So just select this criteria range, and I close parenthesis and press enter, and I'm done so for these criterias sales between greater than 600 but smaller than a thousands in the month of February for Dan, they don't exist. Okay, so If, for instance, I get rid of Dan, then I get a decent off 1800. So from the sales until say, it's 1800 dollars were sales between or greater than 600 smaller in the thousands and made in the month of February. Okay, so that's the D some function. And here, as can see, I've created two sales, Criterias could also in large dad to, for instance, um, for other persons or other criterias, you can watch it as big as you want. So just basically works this way that, um if you could have to that you select your table, you select your field, which you want to sum up in this case, our sales. And then you need to select, um Onley you criteria range, which you can set up on your own, and then you can use to descend function. That's basically it. Okay, so thanks for watching. And we'll see you in the next video by 31. Create a dynamic chart: Hi, guys. Welcome back in this video we want to create a dynamic chart in Microsoft Excel. All right, now let's start. Well, if you could see here, I have set up a table and, of course, I could select the table, go to insert. And I could go to charts, recommended charges, for instance, And I could select charge, which suits me for the three years. Right. But I only want to show one year, and I want to select with the data validation tool which year I want to show. Okay, so just get in here, go to data that a validation click here again, we will select the list. We've done this before several times. Just select the year. Okay? Press OK. And now we can select which you want to see, right? Cool. Now here we will use another function if function again. So we will check if okay, if this value here is equal to this value. Okay. So if this value is equal to this valley here and chris afford to lock it because we were copied across and send it down, Then we want to see the sales figure. Otherwise, we don't want to see anything. So type in a zero K close parenthesis presenter, and we have are still in a row. You'll see in a moment the copy it across and a copy down. I will get seriously as well. And the reason is you can see if you go in here. Impressive, too. You can see that now It is checking whether be nine is equal. So we had to lock here. The eight. Okay, so person to and select the B eight here and press it for two times. So now the aid is locked, but not the be presenter. And now again, drag it and double click to send it down. And now we have what we want. Okay? And now we can select a year, and depending on which year we year we selected, we can get our sales Abbott and income figures. All right. And all we have to do now is just again select this table here, go to insert recommended charts. And I personally like for instance, this one. Okay, press OK. And he would get out, start and now we can select okay for each year, and we will select each year and we see here? Um, the figures. Right now, we also can change its our title. Get rid of the legend if you want or not. Just going to design or informer designed. Help us, okay. And or use those ribbons here. Um, that's basically about it. But the main figure, the main purpose here of this video was just creating this dynamic chart in selecting different kinds off with the dental data tool, different kinds of years and create a nice, really nice switching chart. Right? Great. Thanks for watching. See in the next video.