Top Excel Formulas and Functions

Subscribe and click the Bell icon to turn on notifications in the last lesson we started to take a look at the sum function and as I said the sum function is by far the most popular function in Excel and one you'll find yourself using all the time and all the sum function does is it takes a range of numbers and it will add them together and as you get into more complex formulas you can use The sum function for many different things and combine it with other functions to make it really powerful but let's not

get too far ahead of ourselves at this stage but let's delve into the sum function in a little bit more detail now once again we have a very basic spreadsheet here we have some years running across the top and some months of the year and then we just have some random numbers here possibly sales figures something along those lines and What we want to do is we basically want to complete the total for each of the different years now as we've already seen we can simply click in the cell type in equals and as we

start to type in the word sum it's going to come up in the list below we can press tab to put that first bracket in and then all we need to do is select the list of numbers that we want to add up in this case C3 to c14 close off the bracket and hit enter To get our result once again we have that little green triangle in the corner and I'll explain what that is in a couple of moments now there are other ways that we can perform this sum function now it might be

that when you're first starting to learn formulas you don't particularly like typing functions directly into the cell and I know that a lot of people who are new to excel prefer to use the functions dialogue box to construct their formulas So let me just show you what that is and then you can decide for yourself which method you prefer so instead of typing equals sum into this cell what I could do is jump up to this little FX button and you'll notice here it says insert function it's also worth noting that we have this button

on the formulas tab as well it's this one just here with the keyboard shortcut of shift F3 so when we click this it's going to open up the insert function dialogue box and This is a really nice method to use if you're not entirely sure what the function name is that you need to use you can see at the top here you can search for any function available in Excel or you can browse through the different categories of functions now I'm going to select all just here because what I could do is I could type

a brief description of what I want to do so maybe if I just type in add numbers and click on go it's going to pull up All of the functions that will help me do that and I can see that one of them in there is the sum function now I can doubleclick to select this and it's basically going to open my function arguments now remember when we're typing the function into the cell these function arguments appear in that little screen tip underneath where we're typing and this is basically exactly the same it's just saying

provide the numbers that you want me to add together and you Can see here it's made a good guess based on where I'm clicked as to the numbers that I want to add up D2 to D14 and if I check my spreadsheet that's not quite correct because D2 is actually a date so what I'm going to do is I'm just going to remove those numbers and make my selection again which is D3 to D14 notice it puts the formula in the cell form for me click on okay and then I get my result so that's

another method you can use not only with the sum Function but with any function that you're using in Excel the functions dialogue box now another way that we can very quickly add up numbers is to use the Autos sum button now Auto sum you're going to find on the formulas tab in the functions liary group and this is one of those double-sided buttons so I can click the top half or I can click the lower half now if I click the lower half it's going to show me the different functions that I can use Auto

sum width So I can use it for other functions aside from sum however the default action for the auto sum button is to use the sum function so that means I can just click the top half of this button so if I do that notice what happens again it tries to select the data that it thinks I want to add up and most of the time this selection is fairly accurate but again if I look at my data I can see that within this range that it's selected it's also including the Column heading because effectively

the column heading is also a number now what I could do in this scenario is I can simply adjust my range notice I have these marching ants around the outside and in the corner there I have these little blue squares as I hve my mouse over it allows me to adjust the selection so I'm going to move it down one cell and then I can simply hit enter to complete my sum formula and an even quicker way of invoking Auto sum is to Use the keyboard shortcut which you can see there is alt plus equals

now again if I do alt plus equals it's going to make the cell selection but for this example I need to readjust that cell selection so it doesn't include the date and hit enter so a few different methods that we can use there to quickly utt a sum formula now the final thing I want to talk about here are these little green triangles in the corner of each of these total cells what are they and why Are they there if you see a green triangle this is some kind of warning and warnings are always worth

checking into further so you can determine if this is actually an error that needs to be fixed or if it's something inconsequential now when I click on a cell that has one of these green triangles notice that I get a little icon next to it which is a little warning icon and if we click this this is kind of going to give you an idea as To what the problem is so I can see here at the top of the list it says formula emits adjacent cells so what this is basically telling me is that

in this sum formula and if we look in the formula bar C3 to c14 it says it's found other data nearby that I haven't included in my formula and that would be correct because I have these dates at the top here which are effectively numbers to excel but to me they're dates and I don't want them Included in the formula so that is what this error is referring to it's basically saying hey you've missed out a number in cell C2 do you want to correct this before you go on now I know that these years

are not supposed to be included in these formulas so what I can safely do here is select all of the cells where I have this little warning click the drop- down and just say ignore error to get rid of them so far in this course we've looked At three of excel's big five basic functions that everybody needs to know so it's now time to finish off this little list with the final two and the final two functions that everybody needs to know are the Min and the max functions now what the Min function does is

that it will return the lowest value in a range of cells and as you would expect the max function does the exact opposite it will return the maximum value in a range of cells so these are Pretty simple to construct let's start with Min I'm going to type in equals Min I'm going to press the Tab Key to select it from the list and put in that first bracket and once again we have some very simple arguments we're just required to select the numbers or the cell range that we want to find the minimum value

of so I'm going to select this cell range just here C3 to F3 and what this is going to do is tell me which value is the lowest so when I hit Enter it's telling me that 6247 is the lowest value in that range so I can see at a glance that for January we had the lowest sales in 2019 I can do exactly the same for Max let's type it in let's select our cell Range close the bracket and hit enter and now I have the maximum value which was in 2021 now once again

I'm going to want to copy all of these form formulas down both of these columns so let me show you Another way that you can very efficiently do this I'm going to select the two formulas that I've already done and all of the other blank cells and if I press the keyboard shortcut contrl D that's going to copy that formula down each column and if we just double check one of these so let's double click somewhere down here just to double check that it's actually calculating correctly so C9 to F9 the lowest value there is

5538 and I can see that yes that is Correct so contr D is another really useful little shortcut when it comes to quickly copying a formula into other cells another one of the big five functions that all Excel users need to know is the average function and the average function is pretty much what you would expect it to be it's a function that helps you find the average of the values in the selected cell range and much like the other functions that we've looked at in the last few lessons the Sum function and the count

function we can either type this function directly into the cell or we can use the functions dialogue box so let's take a look at how the average function works so on this worksheet I have some years running across the top and then again some months running down column B and then we have some values in this table and what I want to do is at the end here in column G I want to find the average amount of sales for January across all Four of these years 2018 to 2021 so for this we can simply

type in average into the cell once again as soon as you can see it in excel's intellisense menu you can just press the Tab Key to select it and we have very similar arguments to the sum and count functions here Excel just requires us to select the cell range that we want to find the average of so I want to find the average of these four cells C3 to F3 I'm going to close my bracket and I'm going to do Controll enter so that I stay in the same cell and there I have the average

sales figure so really nice and straightforward now of course we've seen how we can do this using the functions dialogue box it's a simple case of searching for the average command double clicking and then we can go in and select the cell range that we want to find the average of so pretty much the same as what we've done in the last two examples so just to finish off This lesson let's jump up to the formulas Tab and we're going to go to the autosum dropdown because notice in there we can also use Auto sum

to find the average so if we click average again notice it's made an incorrect selection so all I need to do here is select the cells that I actually want to work with contrl enter to stay in the same cell now if you have a scenario like this where you need to complete the averages for a number of different values like we Have here we need to complete these average totals you don't need to go to every single cell and complete an average formula and this is going to lead us on nicely to a lesson

that we're going to do a bit later on because what we have in Excel is the ability to autofill or copy formulas down and this is such a useful timesaving utility now if I hover my mouse over cell G4 where I have my last average calculation notice in the bottom right hand corner I have That little green square that is my autofill handle so if I want to copy this formula down to fill the rest of these cells I can simply drag all the way down like so or alternatively and I'm just very quickly going

to contrl zed to undo that I can doubleclick and it's also going to copy that formula down and notice if we pick any of these so let's say let's go to this cell just here G9 notice that the references C9 to F9 are highlighted in Blue in the formula and it's also showing me the cells that that formula refers to in the table highlighted in blue as well and I can see that as this formula has moved down Excel has automatically adjusted the cell references that it needs to use for this formula and that is

what we call relative referencing when we drag a formula down Excel will adjust the cell references accordingly and again we're going to talk more about that a bit Later on because there are two other types of referencing aside from relative and that is absolute and mixed but for the time being just be aware of this concept of relative referencing when you copy formulas down another function that belongs in the category of the big five functions that everybody needs to know in Excel is the count function and what this function allows you to do is basically

count the number of items in a range or A list so let's take take a look at a basic example because there is a very important Point here that you need to understand now in this worksheet I just have a list of student names and I have the score that they've achieved in a test and basically all I'm trying to do here is count the number of students that took a test so I just want to count the number of items that I have in this list now bear in mind that this list is a

very short list and this is probably Something I could just do visually but again remember with all of these examples imagine if you have a list of thousands and thousands of students that is where the count function is going to be really useful so what we can do here is we can type into the cell equals and this function is simply called count and notice again as I start to type it in excel's intellisense is pulling up a list of all of the functions that it finds in its Library related to count And if you

find the one that you want to use and if the one that you want isn't at the top of the list you can use your arrow keys to move further down and select the one that you want also notice that with count highlighted I get a little bit of informational text to the side which tells me what that function does so it says counts the number of cells in a Range that contains numbers and that is the really important part in a Range that contains numbers so let's Press the Tab Key to make our selection

and put in that first bracket now our arguments here are basically the values that we want to count so I want to count the number of students so I'm going to go across to my little table I'm going to select all of the student names so that I have my range B3 to B15 I'm going to close off my bracket and hit enter I get a result of zero now why is is that well it's down to that very important part of instructional Text the count function will count numbers in a list so because I

selected these student names which are effectively text for my formula that's why I'm getting an answer of zero if I double click to edit this formula and instead of cells B3 to be 15 I'm going to count using the test scores instead so this time my range contains numbers I'm going to close off my bracket hit enter and now it's working it's telling me that I have 13 items in the range That I selected so what do I do if I want to count anything in the cell regardless of whether it's text or if it's

numbers well for that I need to use a slightly different function so let's double click on our cell and I'm going to delete everything out so if I want to count basically all I would use the count a formula notice this time it says counts the number of cells in a Range that are not empty so it's not going to count empty cells either so now that I'm Using count a which will count all or count everything if I open a bracket I can use either the student range or the test score range and this

formula should work so let's use the student name because this is the one that didn't work when we were just using count let's close the bracket hit enter and now I'm getting the correct result of 13 now how does this work if we have blank cells well let's go in and delete out Olivia from this list so now effectively I have A blank cell in this cell range notice immediately as soon as I did that the number of students decreased to 12 so count a doesn't count blank cells what about count well let's try that

let's double click let's type in our formula again let's do count I'm going to count the test scores because with count we can only count numbers close the bracket and hit enter and again that also gives me 12 so it also doesn't count the blank cells now What about if I just wanted to count the blank cells in a Range well we have another count formula that we can use for that as well and that is Count blank I can see it's the third one in my list so I'm going to use my arrow keys

to go down Tab Key to select it and this time I just need to select my range of cells so if I select these student names cells B3 to be 15 and hit enter it's returning the result of one because in this range it's found one Blank cell So there's three formulas there related to Counting items count which will count numeric values in cells count a which will count anything and count blank which will count just the blank cells now I'm going to control Zed a few times just to put Olivia back into my student

list because remember with these formulas you can also use other methods as well so we look at previously the functions dialog box we can use that for count as well so if I click the FX icon I could go in here and search for count there it is at the top of the list and basically I can then go in and select the values that I want to count so I'm going to select the test scores click on okay and the formula works I could also use that auto sum button so if we go back

to the formulas tab click the lower half of Auto sum we can also use this to count numbers now notice a problem as soon as we do this because the cell where I want my result To be returned is far away from the actual data it doesn't know which values I want to count so it's just put the count formula in there for me and now it's asking for me to select these values so I would need to go in select the range and hit enter if I was doing this formula directly underneath where I

have these numbers if I choose Auto sum and count numbers because the result cell is right next to the numbers that I want to count it's recognized that those Numbers are there and it's automatically input the cell range for me so I just need to hit enter so just be aware of those little things when you're using things like autosum but that in its Essence is how you can construct three different types of count formula in this section of the course we're going to dive into the magical world of using logical functions in Excel because

logical functions are some of the most important functions that you Can know or have in your toolkit when you're working in Excel particularly if you're an intermediate user and what logical functions help us do is basically make decisions now there are quite a few different logical functions you'll find them on the formulas Tab In The Logical group of the function library and you can see all of them sitting in there and we're going to use quite a few of these throughout the balance of this section But what I wanted to make sure that we do

in this first lesson is just do a quick recap on some of those Basics just to make sure that we're all on the same page and we're starting from the same Baseline because it's really important to understand the concept behind how logical functions work before we can move on to do a more complex calculations using things like ifs sum ifs countifs and nested if statements so let's talk about logical statements in Their most basic form first of all and we're going to do this using an example because it's always the best way to visualize this

so on this first worksheet I have a very small table and this lists out some expenses so we have the person's name in here we have the total of their expenses and what I want to do is work out if an approval is required now we have some additional information next to this that says expenses over the following amount must Be approved so if the expense is over $1,000 then it needs to be approved by a manager so I can work this out by using a very simple logical statement now what exactly are l logical

statements well it's basically like performing a test so in this particular example if we take the first expense just here I would want to construct a logical statement that says if this value in cell B5 is greater than or equal to this value in cell G5 then it needs to be Approved and I want to perform that same test on all of these expense invoices now when we're doing things like this we use what we call operators and you can see I've just pasted in a little picture of a table that shows different operators that

you might be using in your logical statements so things like equal to greater than less than greater than or equal to so on and so forth so let's do this first one just here we're going to type in equals to let Excel Know we're about to do a formula and this is very straightforward we perform our test so is the value in cell B5 greater than or equal to we're going to say that because if it's a th dead on it needs to be approved is it greater than or equal to the value in cell

G5 now remember if you want to copy this formula down we don't want that figure in cell G5 moving so we need to lock it by pressing the F4 key and making it absolute Now if I hit enter just here it's given me a result of false and what I can do is I can now copy that formula down and I can see here that all of the results showing as true are the invoices that are greater than or equal to a th000 and need to be approved now notice here that all of my answers

are either true or false and if you perform a logical calculation in this particular way using these operators the output is always going to be true or false now that might Be absolutely fine for whatever it is that you're doing but sometimes it might be that you want to make this more meaningful so instead of it saying false just here I might want it to say okay instead of saying true just here I might want it to say approval so effectively what I might want to do is attribute meaning to the true and false values

and I'm going to show you how you can kind of move on from this and do that in the next lesson but for now let's just get This concept of logical formulas straight in our head we perform a test now because of the way that I've set this up if this threshold in cell G5 was now to change so maybe this now changes to 1200 notice that all of my results update if I was to change this to 500 and hit enter everything is now true okay so a really nice effective way of putting this

formula in and making it it Dynamic and easy to update now just to go back to changing these values and Adding meaning to them in order to do that instead of just having our basic logical function we would need to turn this into an if statement and that's basically what an if statement allows us to do it's basically this same logical formula but it allows us to attribute more meaning to the results and we're going to cover if in a lot more detail in the next lesson but let just show you how we would change

these values now I'm just going to delete these out now what We're going to do is we're going to wrap basically our logical statement inside an if statement now notice here it says check whether a condition is met and returns one value if true and another value if false so the first argument here is our logical test so our logical test is pretty much what we just did is this value greater than or equal to this value F4 to lock press comma to move on to the next argument this is where we Can attribute meaning

to the true or false result so it says value if true I can now Define what I want it to say if the result is true so if the result is true effectively the number is greater than a th and it has to be approved so in quote marks because text in formulas must always be in quote marks I want it to say approval comma if the value in the cell is false I don't want it to do anything I just want it to say okay let's close The bracket so I've attributed meaning to the

true and false results let's hit enter and I can now double click to copy that down and it's a lot clearer for me to see which invoices need to be approved so simply by adding in that if statement we've changed the rather generic true or false output to something that's more meaningful and easier to understand and that's basically what an if does for us let's look at a few more examples of if in Action now in this first table again very small table we have some test scores so got some student names and we've got

the score they achieved in a test and the pass Mark is 85 so I want to create an if statement that says if the value in this cell is greater than or equal to the value in this cell 85 I want a result of pass if it's not I want a result of fail so again we're going to use if our logical test is if the value in B4 is greater than or equal to the Value in H3 we're going to copy this down so F4 to lock if that is true then they have passed

hooray if it's false then unfortunately they have failed close the bracket I'm going to do a controll enter to stay in the same cell copy that down and I get my results so much more meaningful than simply true or false let's take a look at another example of if and we're going to add in a little bit more complexity just here so in this second table we have some Products we have the weight in kilos and then we have the price for those products and I'm actually just going to change these two dollars just to

keep everything this spreadsheet consistent now what we're going to say here is that if the weight of the product is greater than or equal to 30 kilos then there's going to be a 20% shipping fee and that shipping fee is going to be 20% of the price so let's construct our if what is our logical test well if the weight is Greater than or equal to 30 kilos again F4 to lock now what do we want it to do if that is true so if the product is greater than or equal to 30 kilos well

there's going to be a 20% shipping fee so our True Result is going to be a calculation because we want it to work out that shipping fee so if it's true we want to do the price multiplied by 20% F4 to lock comma if it's false There's going to be no shipping fee so we're going to put a zero on the end there close our bracket contrl enter to stay in the same cell and if I copy this down I should find that the only two products that have a shipping fee are the ones that

are greater than or equal to 30 kilos and this amount should be 20% of the actual price so we can incorporate formulas into our logical if statements as well now I just want to finish off this lesson by running Through through a couple of other basic logical statements and that is and and or now what and allows you to do is basically perform two logical tests so we're taking this example of test results again so we have our students and we have the score they achieved in test one and the score they achieved in test

two and what we're going to say is that they need to have achieved above 75 in test one and above 65 in Test 2 in order to get a result of Pass so we're effectively performing two logical tests here so if we want to do more than one and we need both of these to be true to get a positive result we use the and formula notice the arguments logical one logical 2 so our first logical test is if this score is greater than or equal to the pass Mark for test one F4 to lock

and the second test score is also greater than or equal to the pass Mark for test two F4 to lock both of those Have to be true in order to pass now I'm going to hit enter it's going to give me a result of true or false if I want to add meaning to this and have pass or fail I would need to edit this formula and wrap it in an if statement remember that is how we add meaning the first argument for our if statement is The Logical test well those are being generated by

at and formula so I can go straight to the end and just Define what I want it to say so if both of those are True it's going to be a pass if both or one of those are false it's going to be a fail close the bracket hit enter and now I've combined two functions together to perform two logical tests and I've given them meaning using if all works in a very similar way except what we're saying here is that they need to have achieved the pass Mark in test one or in test two

in order to achieve a result of pass so we're going to go straight in and type In our if formula our logical test well we want to generate this using our or calculation logical one logical 2 so if score one is greater there not equal to the pass Mark F4 to lock or score two is greater than or equal to the pass Mark for test two F4 to lock close off our or we're now back into our if statement we can now set up the value if those are true so if that's true we have

a pass if it's false we have a fail contrl enter and And then I can copy that down so notice here that the only one that comes up as fail is where both of these scores are less than the P marks so we've looked at basic logical functions there we've seen and we've seen or and we've seen how we can add meaning by using IF hello everyone welcome to a new tutorial from Simon says it in this video I'll show you how to use the match function in Excel Excel consists of a variety of functions

that help in perform in a Variety of operations at ease if you want to find the position of a particular value when using Excel tables or pivot tables you can use the match function the main purpose of the match function is that this function is used to search any data in an array of cells and Returns the relative position of that particular data the syntax of the match function takes three arguments the lookup value the lookup array and the match type the lookup Value is a mandatory field this argument denotes the value you want to

search in the array the lookup array is also a mandatory field this denotes the data array or the cell range from which you want the function to look for the lookup value match type is an optional field and can take only the values -1 0 and 1 this field defines how you want the function to search the lookup value in the lookup array when the match type argument is Zero the match function searches for and finds the first occurring value in the array exactly equal to the lookup value when the match type value is -1

the function finds the exact or the smallest value greater than or equal to the lookup value however when using this match type make sure the array is sorted in descending order when the match type is value one the match function finds the exact or large value lesser than or equal to the Lookup value however the values in the lookup array must be arranged in ascending order since this is an optional field the default value will be one even if you mention it in the formula or not let us now see how to use the match

function in Excel with the match type zero to find the exact match for the particular Value First choose a destination cell enter the formula equals match and pass the arguments as the value cell range and the match type Press enter this gives you the position of the search data that is all everyone the match function can be a very helpful tool that helps you find the relative position of the value this can help find replace or make any changes to the data in this video we saw how to use the match function in Excel along

with its use cases thank you in the last three lessons we've spoken mainly about how we can split up Text strings and we've seen lots of different methods that we can use to do that but what about if we want to do the opposite well we saw how we can use flashfill to combine values together but we can also use a text function called concat and you would use concat whenever you want to join values in different cells together and make them one string now before we get on to using the concap function let me

show you another way that you can join text strings together Because we can simply use the Ampersand symbol to join cells together so for example if I want to join together the first name and the last name what I could do over here is Type in equals select the first cell and then I could use the Ampersand sign so if I do Ampersand and select the second cell so this time the last name that's going to join these two text strings together now if I press enter let's take a look at it well yes it

has joined them together but I don't have a space in the middle so I also need to account for the space so let's double click to edit the cell what I need to add in here is after the first Ampersand I need to specify that I want a space between the names now space counts as text so it needs to be enclosed in quote marks so quote space quote and then another Ampersand to join the space with the text in cell B4 so now when I hit enter I get exactly what I'm looking for and

I could copy That down and it's going to do the same for all of those names so you can use just an ampan symbol if you want to join text strings together but you need to make sure that you do a count for things like spaces dashes commas things like that so very straightforward now concat allows us to do something very similar but this time we're using an Excel function so let's do the same thing but this time we're going to use concat now when you start Typing concat you'll see that you get two things

come up in the list concat and concat now concatenate is what this function used to be called and they changed it a few years ago to concat but concatenate does still exist in the Excel database but know that it has a warning symbol next to it letting you know that that is kind of an old function and concat is the newest version so concat Works in a similar way our argument is the first Piece of text so we want the first name comma text two now again if I was just to select the last name

and close the bracket it's going to give me that with no space so what I need to do in here is my text two needs to be the space so again quote mark space quote Mark comma and then when I hit enter I'm going to get the format that I need so slightly different using amazan and I'll leave it up to you which of those methods you find easiest to remember now just to Finish off this exercise let's take a look at a slightly more complex example now this time I want to concatenate the name

and the job title and this is the format I want to use so I want a space separating the first and last name and then I want a space- space and then the job title so let's do this both ways if I was just using the ampan symbols what I could do here is I could say I want A4 aazan we need a space in there so quote marks space quote marks ersan then we Want the last name Ampersand and then we want space- space so quote marks space- space close quotes and Pand and then

we want the job title so we can select the cell let's hit enter and we get exactly what we're looking for I can then copy that down for all of the others so that is how we would use the amazan to concatenate slightly more complex text strings what about if we were to use the concap Function for this is it any simpler well let's see text one is Mary comma remember our space is our next piece of text so quote mark space quote Mark comma we then want add text three which is the last name

comma then we want space- space so quot mark space- space close quot Mark comma and then finally we want the job title close off the concap function hit enter and again we get exactly what we're looking for so that is how Ambersand and concat work so now we've seen how we can join text together let's take a look at doing that on our sales worksheet so just for this example we're going to concatenate the country and the product name again so let's add in a column control shift plus and let's use concat to do this

so I'm going to type in concat text number one so our first piece of text is going to be the country now I want this to have space and then I Want the product name but I want the product name to be in Brackets so my text two is going to be a space quote mark space quote Mark comma what is my text three going to be well I actually want a bracket in there so again in quote marks because a bracket is considered text I'm going to do an opening bracket close the quote Mark

comma then I want the word Kensington comma and then I want a closing bracket so quote Mark close Quote Mark and then close off the concat function now that looks like a really weird function when you just look at it with all of these quote marks in there but hopefully when we hit enter we're going to get exactly what we want and we do and we can double click to copy that down now just for fun let's see what that would look like if we were using aans so equals Japan then we want an ampan

then we want a space quote space quote Ampan then we want an opening bracket so quote bracket quote absen then we want the product soell B2 ampan and then we want our closing bracket quote closing bracket quote and that is it hit enter and we get exactly the same thing so once again I will leave it to you as to which of those methods you prefer now we don't actually want to combine these like that so I'm just simply going to delete out That column in this section of the course we're going to start taking

a look at some of the most useful intermediate level functions and formulas in Excel and the idea behind this section is really to give you a taster of the types of things that you can do with more advanced formulas to move your knowledge on a little bit from from those basic formulas that we looked at earlier in the course and really set you up for Success if you plan to do the Excel intermediate course and we're going to start out this section by taking a look at one of the most popular functions and that is

the vlookup function now vlookup is classified as a lookup and reference formula in Excel and if we jump across to the formulas ribbon you're going to find it underneath the lookup and reference section all the way down at the bottom there it is vlookup now if we take a look at this Screen tip because this is going to tell us exactly what vlookup does so it's going to look for a value in the leftmost column of a table and then it returns a value in the same Row from a column you specify so what exactly

does that mean in terms of data well let's take a look at the example that we're going to use now in this worksheet I have a reasonably large data set and this is related to movies so in column A we have the title of the movie in column B we have the year that it was released in column C we have the certificate it's been assigned in column D the date that it was released in column e we have the runtime or the duration of the movie in column F we have the genre that that

movie belongs to and then finally in column G we have the user rating and what we want to be able to do here is we want to be able to type in the name of a film and have it return the year the certificate the genre and the rating From the table and it's this kind of situation where we would use the V lookup function now the thing that a vlookup relies on is a lookup value so whatever it is that you're using to look up this information in this case the film name that we

have in J2 that must exist in the table so let's pull back the year using the vlookup function now I'm going to type equals into the cell and then I'm going to start to type vlookup notice it's come up in Intellisense so I can just press the Tab Key now let's take a look at the different arguments remember the arguments are what we need to complete in order for this formula to work now the first thing that this formula requires is the lookup value so this is the piece of information that you're using to look

up other pieces of information in the table so for us the lookup value is going to be the film so whatever we have in cell J2 now remember this can change I can type in all different kinds of movie names in here but regardless of what I type in because we're using a cell reference it's always going to look up based on the value in cell J2 comma to move on to the next next argument we then need to provide the table array so effectively this argument is asking us where is our data what are

we looking up this information in well for us our table array is this data over here so I'm going to select the entire table now let's jump up to the formula bar to carry on editing comma now it's asking us for the column index number and this is a really important part of vlookup the column index number is where you specify the column of information you want to return so what are we looking up here what is the result that we're expecting well we're trying to return the year of the film moonlight so we want

to return the year now we look Up numbers columns from left to right so the title column is column one the year column column 2 certificate column column 3 so on and so forth so our third argument here is going to be two because the year is column two in our table array comma Now our final argument here is an optional argument how do I know it's an optional argument well if I take a look at the argument you can see that it has square brackets around it now any argument that you see In square

brackets means that it's optional so you don't have to input anything for this but if I do press comma you can see the arguments that we have true or false so what am I doing here am I doing an exact match or an approximate match and this is related to the lookup value the film that we're using to look up the data well I want to exactly match the name Moonlight in the table so this argument is going to be false And I would say that the false argument on the end of a vlookup is

the most common argument now if you don't put anything on the end here remember this is optional it's going to do the default which is to do a false so I'm just going to type false on the end and close off my bracket that is my vlookup formula let's hit enter and see if this works there we go so it's pulled back the year 2016 so if we look for moonlight in the table and it's this one just here you Can see that yes the year that that movie was released was 2016 so we've used

the film name as the lookup value in this table and we've returned column two which is the year let's do this again equals V lookup Tab Key lookup value again is what we have in J2 the table array is the same thing so I'm going to control shift down to select everything let's click back up in the formula bar comma the next thing we need Here is the column index number so what piece of information do we want to return this time we want to return the certificate so counting from left to right the certificate

column is column number three and we want to do an exact match of the movie name in the table so our final argument here is going to be false but also remember in Excel you can type a zero to represent false and a one to represent true so I could put a zero on the end here or leave it Blank close the bracket hit enter certificate is R let's double check that yes it is now one way I could make this a little bit easier on myself so that I'm not having to go in and

select this cell range each time for the table array is to turn this data into a table or a named range so I'm going to create a table contrl T click on okay now my data is in a table and on the table design ribbon I'm going to give my table a name so let's just call this movies and hit Enter now that I have this table array named I can use it in my vlookup calculation so this time we're going to pull back the genre and I can see that the genre is column number

1 2 3 4 5 column number six so equals vlookup the lookup value is the movie the table array this time I can use the table name so if I start to type it in notice it comes up underneath in intellisense tab to select Comma the column index number I'm looking for the genre which we've established is column number six and I want to do an exact match so I'm just going to accept the default here I'm not even going to add that final argument hit enter and yes that movie is in the genre of

drama and just to show you another way that you can do this you could also utilize the functions dialogue box so if we go up to the formulas Tab and click on insert Function I'm going to search for vlookup let's click on go doubleclick to select and now I can complete my arguments in this way so the lookup value is J2 the table array is movies the column index number well this time we want to return the rating which is column number seven and we want to do an an exact match so we need a

false argument on the end there let's click on okay and there we go 7.5 so now what this means is that every time I change the film or type a new film into here all of these results are going to update so if I type in Jackie and hit enter you can see that those results have changed to reflect this movie in the table what about if I type in let's go for La La Land h enter again at the top those results have now changed so that is how you do a basic vlookup using

exact matching hello everyone and welcome back To this course on Excel for business analysts we finally made it all the way down to the final module of section four and in this module we've been taking a look at if functions so I want to finish off this section by just rounding it out with how you can utilize if functions along with the count and some functions now before we jump into count if I'm just going to briefly remind you of what the count function does so we're going back to our spreadsheet where we have Our

Tech sales for 2018 and 2019 for the company microw world and you can see that just to the right of our table of data I have count count if and some if so the first thing I'm going to do is show you what count actually does now the count function in Excel again is one of those big five functions it's one of the functions that everybody needs to know and one that you'll probably find you use on a fairly regular basis and what it does if we take a look at the Arguments for this function

is that we need to provide it with some values and it's going to to count how many values we have so for example if each row of this data represents a single sale I might want to find out how many individual sales I have I could use the count function for that and because I'm just counting the number of items I could really utilize any column so I'm going to click in here I'm going to do control shift down to select all of my Rows of data and now if you look up in the formula

bar if I close off my bracket and hit enter it's going to tell me that I essentially have 909 individual orders in my data so count just counts the number of items so now we understand what count does we're going to move on to count if now this will essentially do the same thing but it allows you to add some criteria into the count for example I might want to count the number of products that are Called HP Spectre 360 that would essentially be my criteria I might want to count all of the orders we've

had for people who work at computech alternatively I might want to count the number of orders I've had where the number of items is greater than 10 and that's exactly what we're going to do so I'm going to jump into my cell and type in count if and open my bracket and you can see I have two arguments That I need to provide and the first one is the range so it really depends what you are counting in this particular example so let's use that last example that I mentioned we're going to count the number

of orders where the amount of items ordered is over 10 so my range is going to be the number of items so I'm going to click in my first cell contrl shift down to select the entire column so we're now working up in the formula bar I'm going to press comma and it's Asking me for my criteria now your criteria always needs to go in quotation marks and what is my criteria well I'm only counting the order if the number of items in that order is greater than 10 close my quote marks and close my

bracket so I've provided my criteria range and also so my criteria hit enter and I can see there that out of all of these orders there are 344 orders where the number of items is Greater than 10 so it's a count but it allows you to add in criteria now some if Works in a very similar way but instead of counting we're actually summing so it might be that I want to sum all of the orders where the shipping is greater than $20 or maybe I want to sum all of the items where the cost

is less than $150 but in this example what we're going to do is we're going to say equals sum if Open Bracket we're going to sum All of the totals that are greater than or equal to $4,000 so our first argument is the range so we want to select our totals range contrl shift down to select again we're now working up in that formula bar comma we need to provide our criteria so I'm only summing totals that are greater than or equal to $4,000 and we need our quote marks and we're going to say greater

than or Equal to 4,000 close quotes and close brackets hit enter and there we go I now have my total it's summing together all of the items that are greater than 4,000 and again I'm just going to apply some accounting format to that and take those decimal places down so pretty simple to use both countif and Su if let's move on to another example now this time we're going to utilize count ifs and some ifs in order To acheve achieve our result now countifs and some ifs differ from countif and some if because it allows

us to use multiple pieces of criteria so very similar to when we were looking at that ifs function so what we have over here are a few different tables this first table in the top leftand corner it's showing us the names of some salespeople the region that they work within the number of orders they've generated and the total amount of sales This time in UK pounds what I essentially want to do is in this first table I want to count the number of orders that are greater than or equal to 100 for each of the

regions so essentially what I have here are two pieces of criteria for my count which means I need to use count ifs so let's type that in and take a look at our arguments so the first argument we have here is criteria range one so what exactly are We looking for well we're counting the number of items in each region so our first criteria range is the region so I'm going to select that entire range now because I'm going to drag this formula down I don't want these cell references to move so I'm going to

need to lock that in place by pressing the F4 key to make those references absolute comma it's now saying okay within that range what do you want me to look for and I want it to look for the east Region F2 I'm now being asked for my second criteria range so my second piece of criteria is I'm looking for the number of orders so I'm going to select the orders range and again I'm going to lock that in place F4 comma what is my criteria well I'm looking for orders that are greater than or equal

to 100 now I could directly type that into the formula by putting it in quote marks and saying greater than or equal to 100 I could do it that way In this example I actually have it listed out in the cell so I can just select the cell for my criteria remember that makes it a lot easier to change all of your formulas in one go if this figure changes it's going to update across the board now if I had more more pieces of criteria I could carry on going just by doing comma selecting my

third criteria range and my third piece of criteria but in this case I only have two so I'm going to close off my bracket Hit enter and I'm then going to copy that down and if we do a quick visual check and look at the east region first of all which is these top two I can see that we have zero orders so nobody has made any orders which are greater than or equal to 100 if I look at the west region there is just one entry for that so we can see that we have

one order that's greater than or equal to 100 the North Region both of these match that criteria so my result should be two Which it is and the south region that doesn't match the criteria So my answer is zero so we can essentially do a very similar thing if we want to sum these sales figures and for this I'm going to use the sum ifs function so our first argument is the sum range so I want to sum the total sales that meet my two pieces of criteria so my sum range is going to be

My sales range and I'm going to press F4 to lock those in place CU I intend to copy this down and I don't want them moving comma what is my criteria range range number one well my first piece of criteria is the region so my criteria range is the region F4 to lock those in place comma I now need to specify what I'm looking for in that criteria range well I'm looking for the region in this case F12 comma I now move on to my second Piece of criteria so I'm looking for all orders that

are greater than than or equal to 100 so my criteria range is my orders range F4 to lock those in place comma what is my second piece of criteria so again I could select cell g12 alternatively I could manually type that in greater than or equal to 100 making sure it's in quote marks and then closing off my bracket let's hit enter And let's just send that down and there I have my results so again this is a small data set we can do a very quick visual check if I look at sales for East

none of those match my criteria hence why I have nothing in my total we only have one for West so I would expect to see the same thing 41,100 for the North Region again we only have one that meets that criteria and also for the South Region as well and of course because of the way we've Set this up if I was to change one of these so let's make one of these figures let's make the other North figure above 100 let's say 101 if I hit enter you'll see that that will now update accordingly

hopefully that gives you more of an idea of how count count if some if and then count ifs and some ifs work whilst vlookup is an amazing function to know to perform powerful lookups after you've been using it for a while you're Going to start to come across some of its limitations so in this lesson I want to just briefly talk about the limitations of vlookup and then show you a better more flexible way of Performing lookups so let's just review now when it comes to vlookup if we take a look at this first example

that we were using we're looking up the part number in the parts catalog and we're returning either the description or the price and the way that we construct the vlookup is we say Look up the part number and then return me either column 2 or column 3 now the big limitation with vlookup is that your lookup value must always be to the left of what it is that you're looking up because we can't count back columns for example I couldn't use the price to look up the part number using vook up because when it comes

to the part of the formula where we have to enter in a column number I can't say count back minus 2 Columns to get the part number it always has to be a positive going from left to right so the biggest limitation here when using vlookup is that your lookup value either has to be in the First Column or to the left of whatever it is that you want to return now that might be absolutely fine in 90% of scenarios that you're going to come across but you're definitely going to come across some situations where

you're going to need a More powerful and flexible way to perform lookups and that is where index and match come in so let's take a look at these two functions now index and match are two separate functions in their own right but you often hear them used together in the same sentence because they are used together so often to perform complex and Powerful lookups and the beauty with using index and match to perform your lookup as opposed to something like V lookup is that it Doesn't matter where your lookup value is in the table and

what column you want to return it's always going to work so let's take a look at this example first of all I have a table over here which shows me some categories some apps the revenue those apps have generated and the profit and what I want to do is be able able to select an app from this drop-down list and have it return the category the profit and the revenue for that app now I'm going to be using the App as the lookup value in this table now the app is in column number two so

when it comes to returning the category I'm effectively having to look up a column going from right to left so this is a perfect example of where vlookup wouldn't work because we can't have the lookup value in column 2 and return a result from column one so we need a better way of doing this now before we actually combine index and match together to perform this look up I want To show you what each one does in its own right which will help you understand why they work so well when used together so I'm going

to click in cell H6 and let's take a look at what index does first of all now index has a couple of different sets of arguments that you can use here and I would say that the first group of arguments is the one that you're going to use most of the time now the first argument here is array now the way that I like to remember this is what is it that I'm looking to return here I'm looking to return the category so my array is going to be the category and an array is effectively

another word for range so our first argument is going to be the category range the category array comma I now need to provide the row number of the item I want to return now I want to return the category for the application one drive so if I take a look at my table and find one drive it's Just here what is the row number well if we start at the top 1 2 3 4 5 6 it's row number seven so if I type row seven in here close my bracket and hit enter it's going

to pull back the category of productivity and if I take a look at that I can see that yes that is correct now I know exactly what you're thinking at this stage you're probably thinking well that's a bit tedious having to manually count down to find the row number and you would be correct we have A very small data set here so it's not too much of a bother for me to count down but what if I had a data set that had hundreds or thousands of rows I'm really not going to want to spend

time counting down the rows to find the value that I need so we need to find a way of automating the finding of this row number so with that in mind let's take a look at what the match function does on its own we're going to type in equals match now for this one we have three Arguments the last argument is an optional argument the first argument is lookup value well our lookup value is one drive what is the lookup array so where will we find the value One Drive which range well we're going to

find it in the app range so that is our lookup array do we want to do an exact match here yes we do I want to exactly match the word one drive in the table so I want a zero argument on the end let's Close the bracket and hit enter and take a look at that it Returns the row number that one drive sits in so the match function will do what we need it to do it's going to automate the finding of the row number so now we know what each of those functions do

we can combine them together to perform this lookup so let's start from the top we're going to type in index what is our array well our array is what we're looking to return the Category comma now we need to provide the row number so this is where we're going to use our match function so we're going going to go straight into match our lookup value is one drive cell H5 where are we going to find that well it exists in the app range so this is our lookup array and we want to do an exact

match of the word one drive in the table we need to close off our match close off Our index hit enter and now we get our cat and I should find that if I change this to something else that's the same category let's choose something else it's going to work correctly let's do it again equals index what is our array well this time we're looking to return the profit so this is our array just here row number well we want to use match to automate that for us the lookup Value is H5 we're going to

find that value in the apps list that is our lookup array and we want to exactly match the word Instagram so we want a zero on the end there close off our match close off our index and hit enter let's double check so let's find Instagram in this list yes it is in the category social media and the profit is 78680 now we're going to do this again for the revenue I'm going to do this in A slightly different way so you can see a couple of other techniques now what I'm going to do here

is I'm going to take a look at my data and I'm going to create named ranges for each of these columns and then I can use those named ranges in my formula now a quick way to do this is to select everything in the table up to the formulas Tab and then in the defined names group I'm going to say create from selection and I want to use the top row As the name names for my ranges let's click on okay so now what I should find if I click the name box drop down is

that I have four different named ranges here and they've all been named according to the column heading if I select category from here that is the range that it refers to if I select Revenue that is the range now you don't have to do this I'm just showing you a different way that you could construct this formula I could then say Index this time time I'm looking up the revenue so instead of selecting this range of sales I could use the named range so we can type it in or we can press the F3 key

and select it from here we then straight into our match to find that row number lookup value is whatever we have in cell H5 our lookup array where are we going to find this where we're going to find it in the apps list now because I also have this as a named range I can press Press F3 again and choose app and I want to exactly match the name Instagram in the table so we've got a zero on the end so let's just take a moment to review what we have there because we've used two

named ranges in this formula and the reason why I like to use named ranges is because it does make it a lot easier particularly if you have a much larger data set than I have here because it means you're not having to keep selecting sell ranges you can just use The name but it also makes formulas a lot easier for anybody who's looking at this spreadsheet to understand now I need to add another bracket on the end here hit enter and there I get my result let's check to make sure this is working let's choose

something else Twitter let's double check in the table that is in the social media category revenue is 17760 and profit is $800 now that I've shown you how you can combine the index and match functions to Perform a powerful complex lookup we're going to see how we can do exactly the same thing in a different way and when I say in a different way I mean using two brand new functions in Excel 2021 and that is X lookup and x match now as I said these two functions are brand new to excel 2021 and xook

up is really another way of performing a lookup very similar to index and match so it has all of the same flexibility that we love about index and match but it's a lot Easier to construct so let's use the same examples but this time instead of index and match we're going to use x lookup so let's click in our cell I'm going to type in equals x lookup now the first difference you'll notice here is that we do have a lot more arguments and it's only the first three that are mandatory all of the others

the last three are all optional arguments now we're going to go through all of them just so you can see exactly what they do But if you just wanted to do a basic lookup you could just use the first three arguments so the first argument is lookup value so our lookup value is going to be whatever we have in cell H5 comma what is our lookup array so where are we going to find Google Docs well we're going to find it in the apps list so this is our lookup array and then we simply need

to specify the return array so what do we want to return we want to return the category so all we need to do Is select the return array that is basically it I could close off my formula hit enter and I'm going to get my answer now how much easier is that than doing the same thing using index and match now let's double click to go back back into this formula because I did say I wanted to run through all of these arguments so the next argument is if not found so we have some additional

things we can specify here so if the lookup Value is not found in the table we can choose to return a value that we specify so that might be a piece of text remember text needs to go in quote marks so I might just want to say here not found comma I can choose what type of match I'm doing do I want to do an exact match do I want to do an exact match or next smaller item or next larger item well I'm going to do an exact match I want it to exactly match

Google Docs and then The final optional argument that we have here is the search mode so with this particular argument we can choose which way or how we're searching through this table so I can can choose to search from first to last or last to first now if I do first to last let's type one as our final argument it's going to return the result of productivity now we're going to do XL up again to return the profit and the revenue but we're going to do this in a slightly different way again Now so far

we've seen how we can create named ranges and use those in our formulas but what about if I want to put this data in an Excel table well what I'm going to do is click in my data and press contrl T yes my table has headers let's click on okay now I don't particularly like this formatting so let's change that that looks a little bit better and I'm going to give my table a name so let's call this apps uncore list and hit enter so now what I Could do when I'm constructing this xook up

let's do it again so X lookup our lookup value is what we have in cell 8 H 5 my lookup array is a table this time now I've called my table apps list so I'm just going to start to type it double click to select that table now it selected the entire table but my lookup array is just the app column so what I can do here is press the square bracket and it's going to show me all of the different colums of that table so I Can then go through and just select the app

column press the Tab Key to select it and close that bracket so now I've isolated that column comma what is my return array well my return array this time I want to return the profit so again I could use my table columns let's choose the table square bracket and I want to choose the profit column this time Tab Key to select and close that square bracket now if I want to essentially skip over an argument so I Don't particularly want to have any if not found text I can just press two commas to move on

to the next argument I want to do an exact match and I want to search First to Last close the bracket hit enter and it's going to return my result let's double check to make sure that this is correct so let's find Google Docs and I can see that yes the profit is 1 166 now for our final X lookup just here I want to focus in on that search mode so which Way we're searching through our data so let's do our X lookup one more time our lookup value is H5 our look up array

well I'm going to use my table range again I'm looking up the app in the app column the return array this time is going to be the revenue so let's select a table and the column [Music] I'm not going to put in any if not found text let's press another comma to go on To the next argument I want to do an exact match and I'm going to search First to Last close the bracket hit enter and I get my result now with that search mode searching first to last that works perfectly fine for the

data that I'm using but what if I have Google Docs listed twice in this table so what I'm going to do is on the bottom here I'm just going to add another row for Google Docs and we're going to assign it to a different category so let's say Utility it's going to be Google Docs again and let's just give it a value of uh 50,000 with a profit of let's say 5,000 now I'm just going to expand my table out to accommodate that new data so now effectively I have Google Docs listed twice in this

apps list now currently if we use the revenue as an example it show me the revenue of the first Google Docs that it finds in this list because I chose to search from first to last but if we double click to Edit this formula what I could do is I could change this to something different so let's do search last to First minus one and hit enter and we get a completely different result this time it's searching from the bottom upwards and the first Google Docs entry it comes to is this one just here so

it's returning a result of 50,000 for the revenue so that's how that search mode option works but as I said in its most basic form if you don't want to use all Of those optional arguments you just have three arguments look up value look up array and return array super simple so now we know what xlookup does what does x match do well x match is fairly similar in many ways we have a lot more arguments than we do when we're using just the basic match so what I could do up here is if I

want to go back to using index a match I could do an index with an x match instead our array is the category so let's select it and notice That because I have this data in a table it's automatically going to put the table name and the column name in for me row number now if you recall previously we were using the match function to automate the finding of that row number but what I could do is use the new x match function instead again the last two arguments here are optional arguments now my lookup

value is what we have in H5 my lookup array is where I'm going to find that so that's in this Apps list I can then choose a match mode so I want to do an exact match and then I also get that search mode option when using x match as well so I can choose if I want to search from first to last last to first or start doing a binary search and sorting in ascending order so let's do last to first again minus one close the bracket hit enter I can see that I've forgotten

to add an additional bracket on the end so I'm going to accept the correction and it Pulls back the category of utility because it's finding this entry first because it's searching last to first now you can also use xmatch on its own when not combined with index and it works very similar to the match function on its own it's basically going to tell you what the row number is so the arguments are pretty similar look up value look up array is where we're going to find that which is just here my match mode is exact

and I'm Going to search let's do first to last this time close the bracket hit enter and it's going to tell me what row Google Docs resides in from the top so pretty much the same results that you get with match but it's just a little a little bit more flexible you have a few more options in there so that is your introduction to two brand new functions in Excel 2021 hello everyone welcome to a new tutorial from Simon says it in this Video I will show you how to count cells with text in Excel

in a spreadsheet consisting of different data types you might sometimes have to pinpoint the cells with the text and count them to perform any function or operation there are three cases that occur when you count cells with text in Excel let us see them one after the other to count all the text values in a given Excel sheet you can use the C IF function along with a wild card Character this function with a wild card counts all the text values in a given range to count the cells with text in Excel choose a destination

cell and enter the formula the asteris symbol spe specifies anything other than numbers to be counted including blank spaces and special characters however this method does not count logical values you can use the same ctif Function and wild cards to count the number of cells with text in them this method only counts cells that hold any text value and does not count any blank cell in the given range to count the cells which have text values in them enter the formula in the destination cell this is the same as the previous case but adding the

question mark Wild Card together with the star wild card only counts the cells which have text Values there is another case that occurs when you count cells with text in Excel and it is used when you have to count specific words in Excel to count the cells with the specific value you can just enter the text to count within quotations in the formula suppose you want to count the occurrences of a particular word in a range of cells you can just enter the formula to count the occurrences of the Particular word in the given range

of cells knowing how to count cells with text in Excel is essential for using some functions in Excel hope you learned how to convert text to numbers in Excel thank you hello everyone and welcome back to this course on Excel for business analysts we've made it all the way down to section four and in this section we're going to be talking about how to Make better decisions using logical functions and more specifically in this first module we're going to concentrate on one of the most popular logical functions and that is the if function now if

you're looking for the IF function on the ribbon you'll find it under the formulas tab in the functions Library underneath logical this is where you have a list of all of the functions that belong to that logical functions group now if you're not entirely sure What a logical function is don't worry too much I'm going to explain it from the very beginning so what I have on the screen here is a very basic spreadsheet it is a list of Tex sales for a text technology company called microw world and you can see in column A

we have the product that they sell we have the customer we have the date we have the item cost the number of items and the total cost and what you'll see is that the total cost if we look up in that Formula bar is basically D7 the item cost multiplied by E7 the number of items that is how we get to our total to cost figure and what we want to do here is we need to work out the shipping and the total and in order to do that we're going to use the IF function

now when it comes to working out the shipping we have some useful information just above so you can see here it says that there is no shipping charge for orders over $1,500 but for everything else that is a 2% of the total cost shipping charge now if we just jump in here and type in if and open a bracket you'll see the arguments that we have the first thing that's highlighted is The Logical test and then essentially depending on if that logical test is true you get one value if the result of the logical test

is false you get another one so taking a look at our example here what would be our logical test well we know that there is no shipping charge for orders over, 1500 but there is for anything under 1500 so our logical test is going to be if the total cost F7 is greater than or equal to 1500 and you'll find that with a lot of logical functions you're going to use those operators the greater than less than equal to so on and so forth so that is our test is the total cost greater than or

equal to, 1500 let's press comma and move on to our next argument so now we want to say What do we want Excel to do if that is true so if the total cost is greater than or equal to, 1500 what do we want to do well I can see here that there is no shipping charge for orders over, 1500 so I'm going to say I want Excel to do nothing comma what do we do if that logical test is false so if if the total cost is less than 1,500 what do we need Excel

to do well if it is less than I want Excel to work out what 2% of that total cost is and use that as the Shipping charge so we're going to say F7 multiplied by 2% and close our bracket let's hit enter and see what we get so I've got the first number inputed there it's showing me $29 and what I'm actually going to do is just apply some accounting format to this column and I can doubleclick the autofill handle to copy that formula Down and what we should see and it's a very nice quick

way to be able to do a visual check is that anything that's equal to or greater than 1500 should have no shipping charge so I can see here where we have a dash that that one is over 1500 and I can see as I scroll down that that appears to be true everything else that's under 1500 we've worked out what 2% of the total cost is and we've got that as our shipping fee so just to recap again we have logical Test value if True Value if false and our final calculation in the totals column

is a very straightforward sum calculation so we're simply going to add the total cost F7 to the shipping cost G7 close the bracket and hit enter once again I'm going to apply some accounting formatting to this column and I'm going to doubleclick to copy that down and now we have all of our figures so very quickly we've been able to work out everything we need for this Particular data set hopefully that explains how if Works a little bit more we're going to move on to some more examples of ifs so please join me in the

next module for that hello everyone and welcome back to this course on Excel for business analysts we're down in section four where we're taking a look at logical functions and in the previous module we took our first look at how to utilize that if function in this module we're Going to do another example of if on slightly different data so in front of me here I have some employee data so this might be for somebody who works in HR and what we have in column A is a list of employee names we have the building

that they work in and their Department we have the hire date the years that they've been employed their salary and then they've been given a job rating so this job rating might be something that's been generated from a Yearly appraisal and each staff member has been ranked from one to five and depending on what they've been ranked determines if they get a bonus or not and we're going to utilize the IF function in a couple of different ways to work out who is going to receive a bonus so what we're going to say here is

that anybody who has achieved a job rating of five is going to get a bonus of $3,000 so I'm going to click in cell H2 And we're going to fire up our IF function and if you remember from previously we need to First State our logical test so in this case our logical test is if the job rating G2 is equal to five comma what happens or what do we want Excel to do if that is in fact true well if the employee has a job rating of five they're going to get a bonus of

$3,000 if they have anything that isn't five They're going to get absolutely nothing which is a little bit unfair but there we go close off the bracket hit enter and now if I copy this down by double clicking the autofill handle we'll see that we have 3,000 listed wherever there is a job rating of five so it's very easy for us to then see which staff members have received that bonus and once again I'm going to highlight this column and I'm just going to apply some currency formatting so now let's take This on a stage

further I'm going to double click to jump back into my IF function and what we're going to do here is instead of having just a zero or a dash we're going to replace that with some text so I'm going to remove the zero and what I'm going to say is that if the job rating is equal to five I just want it to say in the cell bonus and when you're dealing with text within a formula you must always put it in quote Marks comma what do we want it to say if the logical test

is false well I want it to say no [Music] bonus and hit enter and now if I double click to copy that down you can see we get those results so essentially what I could do if if I wanted to quickly analyze this data and just get a list of everybody who's going to receive a bonus I could do that very quickly by combining this with some Filters so I'm going to jump up to my data ribbon make sure that I'm clicked somewhere in this heading row and I'm going to click on the filter button

this applies these little filters to each of the heading rows and what I can then do is filter just for bonus click on okay and it's going to show me all of the people who are going to receive a bonus now so far in the two examples that I've shown you I've been hardcoding values Into this particular formula so I hardcoded in $3,000 into that if formula which means if the bonus amount changes I'm going to have to go into my formula and I'm going to have to change it what I could do is if

I wanted to have my bonus listed somewhere else so maybe I could have bonus just here and then underneath it can say 3,000 and I'm going to apply some formatting to That and instead of actually hardcoding 3,000 into the formula I can then utilize cell references so let's do our formula again equals if Open Bracket our logical test is if the job rating is equal to 5 comma if that's true then they're going to get a bonus of 3,00 th000 but we're going to utilize the cell reference instead j4 now remember because we're going to

copy this formula down we want to make sure that we fix this cell j4 so Excel Doesn't try to move down one cell so we're going to do that by pressing our F4 key to add dollar symbols in front of the row and the column comma if they don't have a job rating of five then they're going to get zero and then of course we can copy that down and we get our result of 3,000 but the difference being if I want to very quickly update this bonus to 3,500 I just need to change it

in one Place and all of my figures are going to update the final example I'm going to show you in this module is if we want to maybe set the bonus as a percentage of their salary again if we jump into cell H2 and say equals if Open Bracket our logical test is exactly the same if the job rating is equal to five comma if that's true then they're going to get a bonus of 15% of their salary so I want to say salary which is F2 multiplied by 15% and as I was saying previously

I've Hardcoded the 15% into the formula but you could very simply have the 15% listed over at the side in a different cell and utilize the cell reference comma if they don't have a job rating of five they are going to get nothing once more and we can go in and copy this down and there we can see our figures so a few different ways there that you can utilize the IF function in various different formats utilize text Utilize cell ref references Etc in order to extract the results that you need in the next module

we're going to move on to something called nested if statements so please join me for that hello everyone and welcome back to this course on Excel for business analysts we're down in section four where we've been looking at logical functions and in particular the IF function one of the most popular functions in Excel and something that You really need to wrap your head around if you work in business analysis now in the previous couple of modules we've taken a look at some basic examples of if and how you can utilize it in different ways in

this module I want to move on from that idea and just show you how you can construct what we call a nested if statement now when we say a nested if statement what we essentially mean is an if statement or multiple if statements within an if Statement now that might sound a little bit confusing at this stage but hopefully this very simple example using the data we used in the previous module will make it all a lot clearer so once again just a reminder of our data we have employee names and we have the building

department that they work in we have their hire dates we have the years that they've been employed their salary and their job rating and in the previous example we were giving out bonuses for All of the employees that had a job rating of five now it seems a little bit stingy to only give employees that have a job rating of five an annual bonus so what we're going to do is we're going to dish out some more money using nested ifs so I'm going to jump into cell H2 and we're going to start off with

our equals IF function again now our logical test is going to be the same so if the the job rating is equal to 5 comma and I'm going to start out by hardcoding in What the bonus is going to be if that logical test is true and just to keep things consistent we're going to stick with 3,000 comma now this is where the nested part comes in because what I want to do now is say if the job rating is four the employee is going to receive $1,500 so instead of providing a false argument at

this stage I'm going to add in another if and open the bracket and I'm going to do exactly the same thing If the job rating is equal to four this time then that employee is going to get $1,500 comma let's add in another one if Open Bracket the job rating is equal to three then the employee is going to get $900 bonus and we can carry on going so let's do one more comma if the job rating is equal to two then they're going to get $500 bonus comma and what I'm going to say is

that if somebody's got a job rate Of one then they're not going to get anything so we're going to say comma zero on the end now with these longer nested if statements what you need to remember is that for each bracket that you've opened you need to close off as many at the end so I can see that I have 1 2 3 four Open brackets so I need to close off four 1 2 3 4 and hit enter now now the first one is showing me nothing and that is a job rating of one

so that appears to be Correct I'm going to copy this formula down and what I should see is that I have figures filled in for anyone with a job rating of 2 3 4 or five and whenever the job rating is one there is no bonus so that appears to be working very nicely using those nested if statements and as you can imagine if you have a lot of data these can get pretty pretty long and in the next module I'm going to show you a way that you can kind of shorten this up a

little bit but it's really Important that you understand this concept of nesting functions inside other functions now of course as we saw previously we could also utilize cell references to construct our nested if statements so what I've done here is I've just added a little table over at the side that shows me the ratings 1 to five and the bonus for each of those ratings so instead of instead of hardcoding these numbers into the formula which makes them a little bit More difficult to change I can just reference the cell from this little table instead

so let's take a look at how that would look we're just going to say equals if Open Bracket an logical test is the job rating if that's equal to five then they're going to get a bonus of 3,000 which is cell K8 and remember we need to lock that in place by pressing the f four key to make it absolute because we're going to copy this down comma if Open Bracket the job Rating is equal to four then they're going to get a bonus of 1,500 and lock that in place with the F4 key and

I could carry on going as I did before but in this case I'm just going to leave it there I'm going to do my final argument for anybody else who hasn't got a job rating of four or five they're going to get get nothing in this case and I need to close off two brackets because I've opened two Enter and then double click to autofill that down and of course as we've seen before if these numbers now change everything is going to automatically update so let's change the bonus for a job rating of four to

1,700 hit enter and all my numbers have updated very simply hopefully those examples of demystified nested if statements a little bit more for you in the next module I'm going to talk to you about the newer if s function which Really makes nested if statements a lot more concise and a lot shorter so please join me in the next module for that hello everyone welcome back to this course on Excel for business analysts we're down in section four where we've been taking a look at how to make better decisions using the IF function and in

the previous modules we've seen a couple of basic examples of how to utilize if and we've also looked at how to utilize nested ifs that is if statements within If statements and if you remember we had a very small example but when working with nested ifs the formula can become quite long and quite cumbersome imagine if you had a lot more data and a lot more if statements that you need to add in that formula would become extremely long so what we're going to do in this module is we're going to take a look at

excel's new alternative to working with nested ifs and that is the ifs or the ifs function I like to call it ifs to Differentiate from regular ifs now one thing to note here is that the new ifs function is not available in all versions of excel so if you have Excel 2016 2019 for Office 365 then you should find that you have access to the ifs function if you're not sure which version you're using a very quick way of checking if you have access to the function it's just to click in a cell type it

in and if you can see it listed underneath then you are good to Go if you don't have this function then you will need to use ifs as I demonstrated in the last module until you upgrade and have access to this new function so let's take a look at how the ifs function works so I have an example on my screen here and this is just some test results for an English exam and I have these student names I have their score and what I want to determine here is whether they passed failed or if

they need to Retake and just above I have the threshold the pass Mark they need to achieve so essentially if they score above 75% they have passed if they score below 75% they have failed and what we're going to say is if they have achieved exactly 75% they're going to need to retake the exam so I'm going to use the ifs function in order to populate these empty cells in column C with the result so let's jump into our first cell C6 and I'm going to type in equals ifs and open my bracket now the

difference with ifs as opposed to nested ifs if you look at the arguments underneath we're only required to provide a logical test and a value if true we don't need to provide a value if false and that essentially makes this formula a lot more concise to read and a lot more concise to construct so let's type in our first logical test we're going to say if the score which is in cell B6 is greater than 75 and we're going to lock that cell because we're going to copy this down by pressing the F4 key comma

if that is true then the student is going to pass comma you'll see now it moves straight on to log iCal test two we haven't have to provide what we want Excel to do if it's false so let's do the next logical Test if the score is less than 75 and we're going to lock that by pressing F4 comma we're going to say fail now for the time being I'm just going to do those two I'm going to close off my bracket I'm going to hit enter and I'm going to double click my autofill handle

to copy that down so we can see here we have anything under 75 is listed as a fail and anything over is Listed as a pass but take a look at this score here which is dead on 75 currently that's telling us na it's giving us an error and that is because we haven't told Excel what we want it to do if the score is 75 or we've said is if it's above 75 it's a pass if it's below it's a fail now this is fairly easy to solve what I could do is just jump

back into my formula click on the end and provide another logical test so I could say if the score is Equal to 75 and lock that in place then I want it to say retake and hit enter and now if I send that down I get exactly what I'm looking for now there is another way that we could deal with this now it's perfectly fine to do it that way and because I have a fairly small data set it's very easy just to add in that third logical test however there is a slightly different way

that we could do this I'm just going to remove that last logical test that we Added in and instead of actually providing that logical test I'm just going to type the word true comma and I'm going to say retake hit enter and if I copy that down I should find that my results don't change so why is this working what is this true argument and what exactly is it doing well the way that this formula works is that Excel is looking for the first true condition in order to produce a result so if we take

this first score For example 67 essentially Excel starts to run through this this formula looking for the first True Result and then it returns whatever text that corresponds to that so if I just highlight this first logical test and what we can do here is just calculate this formula so we can see the results you can either press the F9 key or on the formulas ribbon you can choose the calculate now option you can see that this first logical test calculates to false for This first score so what x does it says okay that's a

false it moves on to the next calculation so if we highlight the next logical test and do F9 or calculate now it finds true and so Returns the result of fail and it basically goes through all of these and does exactly the same process it's looking for the first true when it finds it it will produce the result that relates to it now with this one here where it's dead on 75 let's double click Let's highlight and do F9 or calculate now we have a false it moves across to the next one we still have

a false and so because we've hardcoded true as our third logical test it's going to produce true so this is a nice kind of catchall option to add into your formula now just to finish off this module on if S one little thing you might want to do to make these stand out a little bit more is combine this with conditional form matting in order to maybe highlight the Results which are showing as fail or maybe highlight the results that are showing as pass so let me very quickly show you how you can do that

I'm going to start out by highlighting all of my results I'm going to go up to the home ribbon and across to conditional formatting and I'm going to create a new rule I'm going to select a rule type I'm going to say format only cells that contain and then because my cells contain text I'm going to make sure I Say specific text containing and let's say the word pass I want all of those results formatted in a green color click on Okay click on okay again and now I can very easily see all of the

people that have passed this test you could of course go through and create another rule to highlight the results that are fail or maybe the ones that are retake as well but that's just a nice way of combin in conditional Formatting with ifs in order to analyze your data a little bit more effectively I just want to finish off this section on logical functions and if statements by talking a little bit about how you can use ifs to handle errors in your spreadsheets now if we take a look at this example just here we're fairly

familiar with this data this is that bonus spreadsheet we were looking at previously now in this bonus column if we take a look at the first Formula I've Basically set up a formula to work out the bonus only for people who have a job rating of five or four and so you can see that reflected in this column so where they have a job rating of four or five we have the bonus amount anything under four so 1 2 or three we have an NA error in the cell and that's because I haven't accounted for

job ratings 1 2 and 3 in this particular formula now it might be that you are happy to leave your spreadsheet looking like this what I will say is that if you do have a spreadsheet that has lots of Errors contained within cells even if those errors are correctly there which they are in this case it makes your spreadsheet not only look a little bit messy if you were to send this to somebody else who's maybe not familiar with the data they might think that these are actual errors that need to be fixed what would

be much better here would be to add some meaning to these na Errors so maybe instead of Na I want it to say no bonus which makes it a lot clearer now we can do this by using error handling formulas and if we jump up to the formulas tab in this logical group you're going to find the two that we're going to use in this lesson in here we're going to take a look at if error and if na now let's let start out with if na now what if na will do is it will

help you deal with any na errors that you have in your spreadsheet now na Isn't the only type of error that you might see in sales you might see a div error or a value error or a name error and all of these different errors mean different things but if you specifically have an NA error in a Cell you can use the if na function to assign meaning to that now as we do have an NA error we're going to use that function now what I want to do here is let's select the first cell

that contains a formula let's jump up and edit this in the formula bar Now what we're going to do is we're going to type in if na that's the formula we're going to use now notice underneath this particular function has two arguments value and value if na so in other words value if we find an NA in the cell now the first argument the value well the value in this cell is generated by that ifs formula so effectively the ifs formula is our first argument it is our value so we can click on the end

press comma and now all we Need to do is simply specify what we want to say in the cell if it comes across an NA error well I want it to say no bonus we need to add this in quote marks and close the bracket so we've wrapped an ifna formula around our ifs formula hit enter and take a look at that now if I copy this down I've replaced all of those na errors with something that is a lot more meaningful a text string that says no bonus now the ifna formula can only be

used when you Have na errors in your spreadsheet and as I said there are lots of different types of errors that you can get in a cell and for any other error you can't use ifna to assign any meaning that is where we would use the second error handling formula if error if error Works in a very similar way to if na but it will handle all types of Errors not just na errors so let's take a look at this in action as well if we jump across to the if error spreadsheet if you recall

We put together a little formula here and again it's an ifs formula which gave us the day of the week when we enter in a day number and what I said was that if somebody enters a day that isn't 1 to 7 so for example 8 it's going to produce an error because the formula doesn't account for that now in this case I am getting an NA error again so I could effectively use the if na error handling but we're going to do this in a different way and we're going to use if Error instead

so if error we can use for any type of error and we edit the formula in exactly the same way so we're going to type in if error at the beginning we have the same two arguments value and value if error the value again is going to be generated by our ifs formula so we can go right to the end press comma and then we can enter in what we want it to say now in this case I wanted to say please enter a number between 1 and 7 so what I'm doing Here is basically

giving the user a little bit of guidance if they enter an incorrect value so let's hit enter and there we have that piece of text and if I choose a different day it doesn't come up but if I put in day number 10 I'm going to get that piece of text so using error handling in this way can really help you keep your spreadsheets consistent meaningful and easy to understand for anybody who might be us using them the only takeaways here are If you have na errors in the cell you can use if na or

if error if you have any other type of error you can only use if error the new let function is part of the logical group of functions in Excel and what let basically allows you to do is assign names to calculation results inside a formula so if you find yourself writing the same expression multiple times in a formula let basically allows you to name the Expression and then Excel will calculate the result using the name so not only is this a great time-saving function but it also makes your formulas easier to read and understand as

you don't have to remember what a specific cell reference or range refers to because it's right there in the formula so let's start out taking a look at the let syntax and also let's run through a couple of basic examples so in cell A4 I'm just going to type in Equals let and you can see here the different arguments that we have for the let function we have name one name value one calculation or name two so name one and name one value are where we Define the name and the value and then the last

thing we do is the calculation and we could have numerous pairs of names and values in our formula in fact the let function supports up to 126 pairs now you might be listening To this thinking okay I kind of understand what you're saying there but it doesn't really make sense and that is completely understandable because this is one of the those formulas that only make sense when you see it in action so let's take a look at a very basic example so the first thing I'm going to do here is I'm going to assign a

name to a variable and for argument sake and because I'm not very good at thinking up variable names I'm simply going to call This first one V comma Now I can assign a value to the word VAR so if I assign the value 20 to this word a itively the word VAR now represents 20 now I could carry on going adding other names and values or I could perform a calculation using the variable that I've just set up so if I now do a calculation it might look something like this I could say VAR plus

5 now what do you think the result of that calculation is going to be we've Stated that VAR equal 20 that is our variable those are our parameters and now my calculation is VAR + 5 so as we would expect the result is 25 because we're just adding 20 to five so that's an example of a simple let just using one variable let's add another variable so if V is equal to 20 let's add another variable I'm going to say comma and remember our next argument is calculation or name two so this time we're going

to add another name and Another name value so let's say that X is attributed to the value 10 so now what I could do is I could modify my calculation at the end here and I could do VAR divided by x what result do you think I'm going to get well I'm going to get a result of two because we're effectively doing 20 divided by 10 so hopefully you're getting the idea here we set up a name we attribute a number to that name and we can set up 126 different pairs of Names and variables

in here if we want to the last thing we do is we perform calculations using the variables that we've created so everything is defined within one formula let's define a third variable so let's double click to edit the formula we're going to click after the second variable and this variable we're going to name Y and this is going to be five I'm now going to change my calculation to do VAR Divided by X and I wanted to do this part of the formula first so we're going to put it in Brackets and then I want

it to plus y let's hit enter we get a result of seven so we're doing 20 / 10 which gives us 2 and then we're adding five let's take a look at another example of using the let formula so if we scroll down again we just have a basic table that shows some employee names and we have their salary and each of these employees is going to Get a bonus of 20% so this is a fairly simple calculation we can just simply do a sum and we multiply the salary by the bonus and we need

to make that absolute and then we add the salary onto the bonus let's close the bracket and hit enter and we get our result of 54,000 so how could we use let to perform this calculation well let's click in the cell underneath and I'm going to do this up In the formula bar and just show you a slightly different way to put together your calculations now this formula bar isn't a set size if you notice if I hover my mouse over the bottom part of the formula bar we get a double-headed Arrow so if I

click and drag this down I get a much larger formula bar and sometimes when you're putting together more complex formulas it's really helpful to be able to lay them out in the formula Bar and have different parts of the calculation on different lines it just adds to the readability of your formulas so let's go into let open the bracket now I'm going to declare my variables on the next line so we need to press all enter to move our cursor down so let's declare our first variable well what do I select first when I'm doing

the sum calculation I select the salary cell so I'm just going to call this first variable Salary and salary is going to reference sell B17 what's the next part of my sum calculation well we multiply the salary by the Bonus so the next variable that I'm going to declare and let's put it on a new line or enter is going to be the bonus and the bonus is cell F15 and we need to press F4 to lock that so now we've declared our two variables we can perform our calculation so Alt Enter to move on

to the next line our calculation Is going to be sum Open Bracket we're doing the salary and notice as I start to type it in it comes up in excel's intellisense so I can simply press the Tab Key to select it we want to multiply by the Bonus again it's come up so Tab Key to select and then we want to plus the salary again close the bracket and close off ourl so that is our calculation on the last line now take a look at the way that we've Structured that it makes it a lot

easier to read we have our function name on the first line we then have our variables declared on lines 2 and three and then at the bottom we have our calculation let's hit enter and see if this works and in fact yes it does so now all I need to do is double click and I can copy that formula down so hopefully now you're getting the idea as to how this left function Works let's take a look at one last more complex formula so let's Move to our final worksheet now this data may look familiar

to you we did use this when we were looking at the filter function I have some block names we have the subject and we have the pass Mark so this might be information for students who took a test and basically what I want to do is use the filter function to return the block the subject and the pass Mark based on the criteria that we have in the middle here so currently if I was using filter I'd only want to see Results where the block equals West and the subject equals English so let's first just

do this using the filter function and then we can see how we can convert that into a let so let's type in equals filter our array well our array are the results that we want returned which is pretty pry much everything in this table comma now we need to specify what we want to include so let's open a bracket I want to include all results where the Block equals West so we need to select the block range and that needs to equal what we have in cell E3 F4 to lock let's close the bracket we

also want to return results where the subject is English and because this is an and calculation we want to return both results we're going to separate with an asteris open the second bracket select the subject range and that needs to equal what we have in cell E7 F4 to lock let's close off our bracket close off our filter hit enter and would you take a look at that there are our results so we're going to do pretty much the same thing but we're going to set this up as a let instead and this is where

lets can be particularly useful because if you take a look at this filter formula notice lots of cell references in here so we can effectively replace these cell references with more Meaningful names using let and this is particularly useful if you have repeating cell references in your formula because it means that you can declare the name for a particular cell reference and then just replace every time that cell reference occurs with the name and not only does it make the formula a lot easier to read it's a lot more efficient for Excel to calculate so

let's jump down to cell G9 and we're going to construct this as a let now I Might need a bit more room here so let's drag our formula bar down so we're going to do this in the same format we're going to start with our equals let we're going to open a bracket and Alt Enter to move on to the first line so now we want to declare our variables now the first thing we selected was our array which is basically everything that we have in this table so I'm going to call this array let's

just call it data and then we can select the cell References comma Alt Enter to move to the next line what do we then need to do well we need to specify what we want to include in our filter and the first thing is we want to include the block so we're going to name the block range so let's give it a name we'll call it block comma and that means we're referring to this range just here comma Alt Enter now you don't necessarily have to name absolutely everything that you select in your calculations as

a variable for Example I might not want to name this cell just here where we have West now I am going to do this just so you can really see what this formula looks like when we name everything and I think what I'm going to do is I'm actually going to rename the block range I'm going to call this block RNG just to make that very specific and then the next variable we're going to call block and that's going to refer to this cell just here and we're going to press F4 to lock That comma

let's move on to the next line Alt Enter the next thing we need to declare is the subject just here so let's say subject RNG is this range just here comma Alt Enter and again if you need to you can just drag this down to give yourself a little bit more room and then our final variable to declare is just going to be the subject and that is going to be this cell just here and I'm going to press F4 to lock that so those are all of our Declared variables we can now perform the

calculation on the last line so comma Alt Enter so now it's time to put together our calculation so we want to do our filter our array well we've declared that as the data range comma what do we now want to include well the first thing to include we want to select the block range and that needs to equal whatever we have in the cell that we've called block asteris our second piece of Criteria is the subject range and that needs to equal the subject close our bracket close off our filter so let's just take a

moment to review what we have in this calculation we have the let function name on the first line we then declare all of our variables and what they refer to and then we do our calculation on the last line so let's hit enter and see what we get now it's telling me it's found a Typo in my formula and I think I've missed off a trailing bracket on the end here so I'll say yes to accept the correction and take a look at that we're getting exactly the same results so our let is working really

well now let's just check if we change this to East let's see what happens it still works so hopefully that gives you an idea as to how you can use let and where it might be useful I will say let is particular useful if you have a large data set if You have a complicated calculation and particularly if you're recalling the same information over and over again in a formula it makes it a lot more efficient for Excel to calculate and it makes the formulas a lot easier to read and understand in this lesson we're

going to to blast through some of the most useful date and time functions in Excel and we're pretty much going to cover most of the date and time functions that you're Going to come across so on this worksheet we have a column column A that contains a date and this date is currently in short date format and we then have an invoice total and what I want to do is I want to extract certain parts of this date into separate column and we're going to do this using date functions now the majority of these that

I'm going to show you rely on the fact that we have the full date in column A because we're going to use this column As our reference point to extract the various different parts of this date so effectively we're breaking up this date into its different parts and I'm going to show you a function in a moment that kind of does the opposite so if you have things like the day number the month number and the year how you can combine them all together into a short date format but let's not get too far ahead

of ourselves let's run through all of these different functions so in column C We want to extract the day number from the date in column A so we have a function called day now notice there is only one argument for this and that is serial number and this is slightly misleading because what Excel means by a serial number is just the C that contains the date so in this case A4 close the bracket hit enter it's extracting the day part of this date so in this case 10 I can then double click which is going

to copy that formula down And extract the days for the rest of the date super easy now what if I want to extract the day name from this date and what I mean by the day name is Monday Tuesday Wednesday Thursday so on and so forth now this works in a slightly different way because if the answer that you want to extract from a date is a text based answer such as the day name or maybe the month name you need to use the text formula this function has two arguments value and then format text

now The value again is just the cell that we want to extract from and then we need to provide the format for this day so this this is where those mmms and DD DS come in again so if we're extracting the day we want to use the D but how many D's we use will determine if I get m m o n or Monday and this needs to go in quote marks because it is essentially text now if I type in 3DS first of all and close my quotes and hit enter I'm going to get

the short version of the day name let's Double click to edit if I was to add another D in here and hit enter I get the long day name and then I can double click to copy that down so just remember that when you're trying to extract things from dates if it's text you need to use the text function let's move across to the next column because this time we want to extract the month number and for this we use the month function again one argument which is the serial number A4 close the bracket hit

enter and we get the month number part of this date now once again the next thing that we're extracting is the month name so this is going to be January February March and this is text so we need to use the text function again let's select our value which is A4 and then we can choose our format which needs to go in quote marks so this time we're dealing with the months so so we want M's in here if I do three M's I'm going to get the short Version of the month so Jan Feb

ma if I put four M's I'm going to get the long version of the month let's hit enter and then of course I can double click to copy that down really straightforward the next one is super easy we're extracting the year and we have a year function you guessed it serial number close the bracket hit enter and it's going to extract the year part of that date and then finally I can extract the weekday number and we can do that using The weekday function now the first argument here is the serial number so we need

the day to gain and now we can specify how we number our weekdays now I tend to number mine as in Monday is one all the way through to Sunday being s but some people in different parts of the world number their weekdays differently some people might start their week on a Sunday and so that is number one to them some people might start on a Thursday so on and so forth And this is the argument where you can specify exactly that so if I choose to put a two in here as this argument it's

going to number Monday as 1 through to Sunday 7 and that is exactly what I want close the bracket hit enter and now I get the weekday number double click so if I just take a quick look at this this first one is four and yes Thursday according to me is day number four and if I wanted to get super fancy and this will even give us a chance to practice a Quick if statement I could type in an if statement here which tells me if this date effectively in column A is a weekend or

a weekday so what I could say here is equals if my logical test well what I can say is if the weekday is greater than 5 if that is true then yes it's the weekend if it's not then no it isn't because Saturday and Sunday are effectively numbered six and seven so if I hit enter and then copy this down I Should find that most of these are no except when we get to anything that is a six or a seven which will be yes because these represent Saturday and Sunday so some really useful functions

in there now I mentioned earlier that we can in fact do the reverse of that and combine values together to create a date so if we take a look at this table at the top here you can see I have the year in one column the month in another column and the day in another column so what I can Do here is use the date function and all I need to do is specify y the cell so the year is cell K4 the month is L4 and the day is M4 close the bracket hit enter

and I get that date which I can double click and copy down I could do exactly the same for time so equals time we need to specify the hour the minute and the seconds close bracket hit enter and there I get my times super useful Functions now let's jump across to the time functions worksheet so I just want to finish off by showing you how we can do pretty much exactly the same but this time using time as our serial number so I have some times in this First Column and we can break those down

using more functions in Excel so the first one is the our function again it just requires a serial number and it's going to pull out of that the hour double click to copy down we have a minute Function again serial number is required and we can double click to copy that down and then finally we can add the seconds by using the second function serial number close the bracket hit enter and then we can double click to copy that down now because of the way that I have these t stimes these last two are just

showing zeros and that is actually correct it's really only this First Column that has different values in It hello everyone welcome to a new tutorial from Simon says it in this video let us see how to randomize a list in Excel in some lists or tables there might be the need to shuffle the entries randomly we randomize the entries of a list or table while picking any random criteria in an unbiased way now let us see two ways to randomize a list in Excel with an example one way to randomize a list is to use

a combination of formulas in Excel you can use the sort by formula with a combination of Rand array and count a functions with a few criteria to randomize the list first select any destination cell to create the random list enter the formula in the destination cell and press enter this creates a random list of entries from the selected range of cells onto the destination cell the second method just employs the Rand function to Create random values and randomly sort the data however it employs a couple of steps to arrive at the randomly sorted data first

select a destination cell enter the formula and press ENT enter when you press enter a random value populates in the cell use the drag handle to apply the formula to the remaining cells as of now it makes no sense and the values are not random yet now with the selected cells navigate to Data under the sort and filter section click on sort you can sort the random values either ascending or descending Excel throws a warning suggesting you select the data adjacent to the random values select the expand the selection and click sort once you click

on sort the random values will be sorted out along with the values you can delete the random values and just keep the list that has been randomized that is all everyone in this Video we saw how to randomize a list in Excel in two easy ways thank you if you're not a subscriber click down below to subscribe so you get notified about similar videos we upload to see the full course that this video came from click over there and click over there to see more videos from Simon says it