Most Useful Excel Formulas for Business and Finance Roles
what's up everyone kenji here and in this video i thought i'd share 15 excel formulas that i found most useful while working in business as well as finance oriented roles and basically for this we're going to be looking at apple's income statement as well as some of their product lines and we'll be applying all of the formulas to that and i'll be going from very easy formulas all the way to harder ones so feel free to skip ahead i'll leave them all in timestamps as well and i'll be attaching the excel file i'll be working on so feel free to follow along you'll just need to click on the link i'll probably upload it as a google doc so you can see it then you'd go to file download and you can get it in your excel on your computer as well all right so in this excel file here we'll have apple simplified income statement here and then on the tabs you can see we'll have different levels as we get progressively higher formulas right so let's start off with a simple count formula let's say that our boss wants to know for how many years this income statement is four obviously here you can just tell that it's five years but just for the purposes of this video we'll actually go through the formula for this so easiest one to use is probably the count formula where we'll go equals then type count and once you see it there you can just press the tab to get it all right once you're there you can just go with your mouse and drag all of those five years in and then press enter and that will give you your five years and let's say you tell your boss that is for a five year period then he wants to know all right what's the average net income for those five years so you'll press equals then type the average formula once you see it we'll press tab again and then we'll just get the first year and drag it through just like that and there you go that's for a five-year period let's say we want to get the median as well just to free different comparisons so we'll go equals median and then we'll drag the median as well let's say we also want to get the max and the minimum so for that you'll just press equals max press the tab key and then again you'll just drag these five press enter same thing for them for the minimum so equals then you type min once you find it you press the tab and then you just drag these with your mouse there you go and then lastly let's say we want to find the sum of all of the net incomes for the five year period so for that you're just gonna get some formula so equals sum tab there and then you'll get those five years as well now those are some relatively simple formulas but you really need this foundation in order to expand onto some harder ones later on right so let's move on to if statements which are slightly harder they basically say that if a certain thing is true then you should do this and if it isn't true then you should do something else right now let's say that your boss wants to know the net income for the years that we're actually above the 55 000 mark now this is actually 55 billion because the figures are in us dollar millions but nonetheless just for simplicity's sake we'll just say it's 55 000 right so let's put the 55 000 here for reference and the if statement that we want to do is basically gonna say that if this net income here is greater than the 55 000 then we want the year to come up and if it isn't then we just want it blank right so for that you'll press equals if press the top key so in this case if if this number here is greater than the 55 000 then we'll press the comma and then you'll see that the value if true is applied so if it is true then we want the year and then we'll press comma again and then you see that what if it's false in this case if it is false then we don't want anything right so for that you're just going to press the quotation mark twice there you go and then you'll cross close the brackets and you press enter and obviously you're not going to find anything because the condition actually doesn't apply right this isn't greater than this one and so let's try to copy that formula and then we'll just paste it down to all these other ones by pressing the shift and the arrow key to the side and then ctrl v but you'll notice that these are above 55 000 but they're still not showing up so let's look at what's going on here and you can see that actually what was supposed to be in here has actually dragged on ray and if we look at this other one the same thing has happened this one's fine because it was the initial one but over here it's already dragged on so we need something that's going to keep this red cell locked on this one so for that we'll actually highlight the red cell once we have it there you're going to press the f4 key which is going to show you these dollar signs and they're basically the way to lock so once you press f4 once you'll see that it's locked if you press it again only the rows are going to be locked and if you press it again only the columns are going to be locked so you can keep pressing if you miss it once in this case we're fine with both being locked so we'll just press that press enter and then we're going to copy that same formula we're going to paste it all along and this time around the years do fill up because this time around it's locked right now as you can imagine this if statement is quite versatile right you can use it for greater than smaller than or equal to as well so let's say that we want to say yes if it is equal to this exact number and we'll say no if it isn't right so we can just use another if statement here so in this case we're looking for 48 351 and so we'll press equals if again press the tab the logic test is that we want this number here to equal this other number here and if that is true then we want it to say yes now when you write when you write letters in there you actually have to put them in quotation marks so we'll just put a yes in there in quotations close brackets and then we'll press comma and if it is false we want it to say no right so quotations again press n o close and then we'll cross the brackets and then we'll press enter so obviously this one doesn't equal to this one so that's a no and one more thing we forgot here is to actually lock the cell right in this case it's blue this one here we need to lock so that when we move on to these other ones over here we're not going to have any issues so for that again we'll press f4 so once we get f4 we'll press enter this case it's an o so we'll drag on drag it on by pressing shift and arrow all the way to the end and you can see that this one here is saying yes as it should be right so that's a basic rundown of if statements obviously there's many more applications to it which will actually get on to in the next level so let's head on to level two in the second tab and here in level two we've been provided with the name of the product as well as the sales for the products and let's say that our boss is asking for iphone sales so he wants to know how many iphone sales there were but this time around you can't just do a normal sum because you actually have macbooks you have airpods and so it's kind of messy you can't really go about it like that so instead what we're gonna use is a sum if so a combination of a sum in an if statement so press equals sum if press the tab key the range in this case because we're looking for iphones is the iphone range then we'll press comma the criteria is that we want it to equal to this guy right to the iphone only so we'll press the iphone then we'll press comma again and the sum range is if it this if this here is an iphone then we want it to sum right so the sum ranges are over here and then once we have that we'll just close the brackets and press enter and there you go you have 114 iphones and what's cool here is that once you have the formula well set up like this you can just go here and let's say your boss is now asking for macbooks you just type mac and there you go same thing with ipad and so on and so forth right now to go back here just press ctrl z ctrl z again and you'll get two iphones again so let's say that now we get this other data set and now our boss just wants to know the iphone 11 so we're just gonna type iphone iphone 11 here and then he wants to know the number of sales again so we'll just press equals and as i showed you earlier some if tab the range in this case is a product range then we'll press comma the criteria is the iphone 11 only and then the sum range is all of the numbers over here you don't have to close the brackets if you don't want to just press the enter and that's going to do the same thing but because you told him that in this data set the iphone 11 is actually repeated a couple times he also wants to know how many times it's repeated to tell the tech people so in this case we're gonna type iphone 11 again but instead of it being a sumif we're gonna be using a countif right so we count count only if it is an iphone 11 right so we'll press equals then type count count if the range here is this one here again and then the criteria is just the iphone obviously this time the formula is easier because there's no sum part right so it's three times and that's exactly right one two and three right and so you give your boss the account number and he says he wants to see the table but he doesn't quite like that the iphones are split like this by number of series because he feels like there's also different types of macbooks which aren't split so he just wants the iphones only as iphones and do the sales based on that so how can you go about that we can use this formula called a left which is basically going to give you the number of figures to the left so let me show you we'll go equals type left tab and then it's going to ask for text so we'll ask for this text here and the iphone has six letters right so we'll go comma then the number of characters in this case we want six characters then we'll close that and it's only going to give us the iphone now if you go back into it let's say we want 10 characters now then that's going to give us the whole iphone 11 right so we'll go back by pressing ctrl z and there you go back to the iphone and then we'll just copy and then paste it all the way down so there you go and in this case we've used the left formula but you can also use a right formula say for instance so equals right press the tab key the text is the phone here and let's say we want to isolate the phone without the eyes so we're going to press comma and in this case it's just going to be five characters from the right close the parenthesis and there you go just the phone and the same thing applies with the mid you can also do equals mid just to get the numbers in the middle right so the text let's say we use this one and we actually wanted to start we only only want to get the phone again so we can actually press comma and start it at character number two and we do that for and press com again and we do that for five characters so we get the whole phone close the brackets and there you go you get phone again right press ctrl b if you wanted to make it bold as well and then lastly let's say we're giving the data set but in this case it's actually split by product here and then the series is over here but our boss wants to know the just the iphone 11 sales right so that's a bit messy how can you go about that that's when the concatenate formula comes in which basically what it does is kind of merge these two together and give you one thing so you can just do a simple sum if after that right so for that you'll press equals go on concatenate press tab the text here the first one is this one here then comma and the text second one is the 11 here and then you'll press enter so there you go iphone 11 in this case you can see that there's actually no space in between which is not very nice so let's try jump into it click on it and then let's put actually put two brackets like we said earlier and within the brackets you're just gonna put a space in it and then comma and then you'll continue on so once you enter here you'll just get the iphone 11 and obviously within these brackets you can actually just write whatever you want right just like that obviously you don't have to it doesn't look good here but just so you get the idea that in the brackets you can actually put anything you want so once we get the iphone 11 here we'll press ctrl c drag it all the way down by pressing shift and arrow down press enter and now you got it all solidified together and again we're looking for iphone 11 so we'll go equals sum if the range in this case the product range over here comma the criteria is we want iphone 11 and then the sum range is this range over here so we'll press enter and there you go 59 just like we had 59 here just to make sure it's correct all right so hopefully no problems until there and let's now move on to level three all right so we're jumping into some slightly harder formulas and in this case we have this data set over here with the product the warehouse the quantity and the price and let's say that our good old boss wants to know how many iphone 11s we have left right so we're just gonna type iphone 11 and obviously this you can just find it here it's this one but um if this were a bigger data set then obviously this formula would come handy right now when you think about it you can't really use an if statement anymore because this is getting just slightly too complex for that so we'll use a vlookup this is probably the most convenient formula for this data set so you'll press equals vlookup so the lookup value in this case we're looking for the iphone 11 right then we'll press comma the table array essentially means the whole table here so we'll select all of this press comma again then the column index number so basically within this area that we've selected we've actually got four different columns right in this case because we want the quantity we'll press the column number three right one two and three so press the three then press comma again and then do we want an approximate match for an exact match in this case we want an exact match so you'll go to false then you can press tab here close the brackets and there you go now the cool thing here again is that it's dynamic right so if instead of iphone 11's our boss who's constantly changing his mind decides to go for a macbook so we can quickly find that out like that and then similarly if instead of quantity he actually wants to know the price of macbooks you can just go in here remember the three here stands for the third column now we want the price so we want to go to the fourth column so we're just gonna change that to a four here just like that now let's see that we've hired a new intern and he's been living in the moon so he has no clue of apple's lineup and instead he's typing all sorts of things here like samsung and once if you press enter he's actually starting to break the model which is quite a nuance for us so what we want to do here is try to protect this range so that they can't write any sort of nonsense right so what you want to do is go to data here then you are press data validation and then what we're going to do is do a list here and the source well we want to have only these products here here so the intern can't put samsung or anything crazy like that so press enter once you have the whole set selected and then you'll press ok and now what you're gonna see is that you can actually only select from the ones here ipad mac and so on and so forth which is quite a neat way to go about it right and then let's say that the intern just doesn't care still tries to put samsung in here but this time around he can't he gets an alert sign very nice so with the interim protection security in place and now that you have a decent understanding of vlookups let's now try move on to the income statement and try to apply there so this time around let's say that our boss wants to find out the interest expense specifically for 2018 so if you feel comfortable with vlookups do give this one a shot on your own if not i'll guide you through it now so we're gonna go equals vlookup again this time around the lookup value is the interest expense then we'll press comma the table array is this whole table over here right then you'll press comma again the column in this case we're looking not the first one not the second not the third but the fourth fourth column and then we want an exact match right so we'll press false close the brackets and yeah the number is exactly right just like that and then let's say that throughout the meeting he's asking for 2019 so we just press a five here instead of a four and then we can change that same thing applies to 2020 right you go press a six instead of a five and instead of having to go go in here by double clicking or going up here all the time you can also just press the f2 button which is just gonna get you in there as well so f2 all right so with that out of the way let's now move on to level four so now we're in level four if you go over to the column u up here and let's say that our boss wants us to find the imac price so if you were to do a vlookup like we were like we did earlier you would actually type the vlookup obviously you look the lookup value is just an imac the table array is this whole table over here then we got the column index but the problem is that the products actually starting at the very last column so you can't actually go the other way you can't go from right to left instead with vlookups you can only go from left to right which is quite a bit of a bit of a problem right so that's when something like an index match comes comes in handy which we'll actually get into a bit later just because it's actually a combination of two formulas one's an index and the other one's a match so firstly let's just start off with the index so you can understand that so starting off with the index formula and the index formula is basically going to tell you the value at the given location within a range so for example over here we've got the prices filtered in descending order right let's say that our boss this time around wants to know what's the third most expensive product on our list so for the row this time it's the third most expensive product so we're just gonna press a three here now for the column the price column is the fourth column and then the item we're gonna do the index formula so we'll type index the array is this whole range over here then you'll press comma the row number is this one here and the column number is this other one here and just like that you've reached the 1100 right and not gonna lie i don't think this is such a useful formula i think it only becomes useful when it's linked with the match which we'll look at now all right now looking at the match function and this function is particularly helpful for finding out the ranking or the position of an item right so suppose our boss wants to find out how pricey the iwatch is relative to other products in the apple lineup so for that we'll type the iwatch here as the product and for the rank we'll go equals much the lookup value is this value over here press the comma the local query is just this array over here press comma again and this time around we want an exact match so we'll press enter and there you go that basically means that the iwatch is the sixth most expensive product right so in short the index is actually returning a value while the match is actually returning a row or a column number all right so now that we have a basic understanding of index and match let's try to combine them together and for the index match we'll use this upgraded table which has the spanish models the uk models and the french models for instance that those are for different types of keywords other things like that and like i mentioned earlier the product names are at the very end so you can't actually use a vlookup here anymore let's say that our boss here is asking for the quantity of ipads that we have so we're just going to type ipad so we'll start off with an index formula here we'll press tab the array is basically the final output that we want in this case it's the quantity so we'll press comma and then within the index we're going to start typing the match function once you find it you can't really see here but once you find it you're going to press the tab button then the lookup value like before we're looking for ipads we'll press comma and then the lookup array we're looking for the product range over here and then we're going to press comma again this time we want an exact match we'll cross close those brackets and then you can see here that there's actually a bracketed column number part when it's in brackets like this it basically means that it's not compulsory to do so it's not necessary so in this case we're only looking for this so we're just going to close it by going to brackets again and then press enter and the quantity is 25 and yeah that's just right so we tell our boss that it's 25 but he actually says that he only wants the es model so the spain models so for that we're gonna type ipod again but this time we just want the es model right so we're gonna go equals index the array actually this time around isn't just the quantity because we want the the es model as well so we're just gonna highlight all of this area here then you'll press comma then you'll go to the match right so lookup value the first lookup value here is the ipads so we'll press ipad comma then the lookup array is this whole product range over here comma again and now you're gonna press exact match close brackets comma and now you've gone into the column range that i mentioned earlier but this time we want to use it because we actually want to find the the model number right so we're gonna go match again lookup value this time we're looking for the es model comma the lookup array is this whole thing up here so once you get that you're gonna press comma again and we want an exact match so we're gonna press zero close the brackets and you're gonna close these brackets again so this one's for the index and then press enter and that's 15 for the 15 here let's say we we want to make sure that again the internet doesn't touch this at all so we're just going to go to data data validation we're going to make a list again and for the range that we want to select we're going to select these three here the speed model the uk model and the france model then we're going to press ok so we got the list over here just in case the internet decides to touch something so now that we have a good understanding of the index match let's now go on to level 5 where we'll be applying it to apple's income statement all right so moving on to level five and here we've got apple's income statement like we mentioned earlier and now we actually want to try to find the revenue gross profit and net income only for the 2016 and 2020 period let's say we want a brief summary for an email or something like that so for that we're actually going to use an index match again but this time around we got to keep in mind that we need to lock some of the cells mainly because once we get it in this cell here we want to try to copy paste it across to all these other ones right so let's try to go about that we're gonna press equals index the array here is actually this whole thing because we want both the years as well as the the revenues and the other accounts then we're gonna lock it fully so we'll press f4 once press the comma key then we're gonna go match tab the lookup value first one is the revenue and this one we wanna lock but we don't wanna lock fully right so we'll press the f4 another time and another time because we just want the b look the b locked and not not the 25 then you press the comma the lock up array is this whole range here where the revenue sits right this one we do want to lock fully so f4 once then you press the comma we want an exact match tab close the brackets comma again and now we'll look into the second match function once you find it you press the tab the lookup value in this case it's the year right so 2016. then this one we want to lock but we just want to lock the 24 this time around so i'll press f4 once twice and there you go comma the lookup array in this case it's these numbers up here which we actually want to look fully so we're just gonna we're just gonna press f4 once comma much type we want an exact match so tab there close the brackets for the for the match and then close the brackets again for the index then you press enter and that should do it if we've done it correctly once we drag it through all the way to this guy there shouldn't be any issues so let's go ctrl c then you go shift arrow to the side and then down and down control v and let's just check this last one 57 000 for net income 2020 57 000 income 2020. so yeah that's looking all correct press the escape so this one's not selected anymore and there you go one thing that i will mention is that you usually do need the the columns or the rows to be the same length as the array right so this array the blue thing here is actually the same length as the purple thing here and this red one is the same length as the blue one as well right so that's something that you need to keep in mind all right so those are the formulas overall i will say that for formulas you don't ever actually have to use them right you can do things by hand it's obviously it's obviously going to take you a bit longer but you don't necessarily have to do them the good thing about them is that one they're speedy and two they're dynamic in that if you have any changes later down the line like i mentioned earlier your boss changes his mind from an imac to an iphone for instance you can quickly make that change instead of having to recalculate the whole thing again right also more often than not there's more than one right formula to get to a certain number in fact when i interviewed for amazon for their business team one of the questions that i got was try to get to this exact number using three different formulas so that's something quite common that they might ask as well so yeah that's all for this video i hope you found it helpful i am considering making some sort of a shortcuts video or maybe a part two to the formulas do let me know in the comments if you'd be interested in that and if you enjoyed this video feel free to like and subscribe it really helps out this small channel a lot and that's all for this one and i'll catch you in the next one
2021-07-30 14:01
