in this video we're going to talk about 10 functions that are typically used in excel so the first one that we're going to go over is the average function so let's say we have a group of numbers 15 27 42 93 56 84 75 33 and 49 if you want to find the average of these numbers type in equal average parentheses and then highlight the column of data that you want to average and so the average of these numbers is 53. 125 and so that's a very simple way in which you can use the average function in excel now let's say if you want to average two numbers quickly you can type in equal average and then let's say we want to average 100 and 200. so type in 100 comma 200 close parenthesis and you get 150.
if you want to average three numbers you can type in the three numbers let's say a hundred two hundred and three hundred the average of those three numbers is two hundred and so that's another way in which we can use the average function in excel now the next function that we're going to talk about is the sum function so let's say if we want to add up some numbers so if we wish to find a sum of the numbers in this list we can type in equal sum parentheses and then highlight the column that you want to find the sum of and so the sum of those numbers is 429 and just like before if we want to we can just find the sum of two numbers let's say 50 and 125. 50 plus 125 is 175. you can also type in equal 50 plus 125.
and as you can see you'll get the same answer now let's say if you want to find the sum of three numbers you can type in equal sum 100 comma 200 comma 300 and that will give you 600 or you can write it this way equal 100 plus 200 plus 300 and so there's different ways in which you can add up numbers using the sum function and so that's the second function that we're going to talk about in this video now the third function is going to be the sum if function so let's compare that with the sum function the sum function allows us to calculate the sum of a range of values let's say if we want to find basically the sum of all of these vehicles we can just use the sum formula but let's say if we want to use the sum if formula how will that help us let's highlight this particular cell so i'm going to give it a yellow color so let's say if we want to find the sum of only the mazda vehicles so we don't want to find the sum of all these numbers but just basically a selected sum we want to find the sum of a certain element in this list so what we need to do is type in equal sum if parenthesis and then we need to select a range so the range will be the car type and then comma the criteria will be based on whatever is in this cell cell b14 and then comma the sum range so these are the numbers that we wish to add close parenthesis enter this should be the card type actually instead of mazda now we're going to put the word mazda in this cell and so notice that we get the total sum of all the mazda vehicles so that's 125 plus 142 plus 425 and you can check it if you type in equal sum 125 comma 142 comma 425 it will give you 692. now you could change it let's say if we want to find the sum of the honda vehicles it's automatically going to adjust there's only one type of honda vehicle in this list so it's 174. if we want to find the sum of all the toyota vehicles just type it in and it automatically gives us a sum so we have 326 for toyota 89 and 274.
so if we type in equal 326 plus 89 plus the other one which is 274 we get 689 so the sum if function helps us to find the sum of a certain element in this list rather than the sum of all of these numbers in that list now let's move on to our four function which is going to be the count function so how can we use the count function the count function allows us to count if you read it it says it counts the number of cells in a range that contains numbers so we can use it to count uh this column and so we have a total of ten cells that contains numbers so this is the first one second third fourth fifth sixth seventh eight nine ten so there's ten ten cells that contain numbers now if you try to use it with a column of data that don't contain numbers you're gonna get a value of zero so that's the four function account function it helps you to count the number of cells with numbers the next one is the count function or count a function and so this function allows you to basically count the number of cells that are not empty so these could be cells with a text or a number so if we highlight these cells we're going to get a count of 10. now let's say if we have mazda if we have a number 25 let's say we have toyota and then 37 and then let's say it jim let's say carla 12. if we try to use the count function on this it's only going to count two numbers because it counts 25 and 37 it doesn't count this one which contains the letters and numbers so that's the count function if we use the count function or the count a function it'll count every function that is not empty and so it counts all of these functions if we try to use it on this entire range it still gives a six it doesn't count the empty cells now there is a function that does count the empty cells and that is the count blank function so notice that we have three empty cells one two three and so that's the count blank function so now you know how to use the count and account a function in excel so number six will be the count if function so the countif function is very similar to the sum if function it allows us to count a certain a selection in this entire column so let's change this to countif and so this is going to be equal count if and then we need to select the range so this will be the range and then the criteria which is a cell will be whatever is in a cell b14 so notice that it tells me how many times toyota is listed in this list and so we have one two three now let's choose moz actually let's choose something different let's choose honda honda should be listed once now i'm going to add another mazda to list so we have one two three four so actually let's replace toyota with mazda because the selection stops here so i have a total of four mazda car types in this list so if i change it to mazda it will count four now i only have two toyota vehicles in this list so if i change it to toyota i will get two and so that is the count in function so instead of counting all the card types that we have here which should be about 10 if we use the count function the countif function allows us to count a certain selection in that list it can allow us to count only the toyota vehicles or only the mazda vehicles and so that's how you can use the count if function in this example next up we have our next function which is number seven can can tonight so let's uh increase the width of that column and let's turn this back into a white cell so how can we use the concatenate function let's say if we have a list of names the first name in column b and the last name in column c and let's write some names so let's say we have the name john smith let's say kelly williams and then jackie garcia and then we'll say lisa clark and let's say david johnson now let's use the concatenate function so i'm going to type in equal can can tonate and then parentheses text one and then comma text to close parentheses and so notice that it puts the information in column or in cell b2 and it connects it with the information in cell c2 and if i want to i can extend this information now instead of doing that notice that we need a space in between so to put that space go ahead and type in equal concatenate again and then we'll type in cell b2 you can just highlight it and then comma in quotations type in your quotation mark space quotation mark and then comma cell c2 close parenthesis so now it's going to put a space between columns b and c and so the concatenate function allows you to basically combine the information in two columns and connect it in one column and you could put anything in between so you can put a space or any other uh data that you want to put in between those two columns so that's number seven the concatenate function now number eight the if function how can we use that so let's say if we have the name of a fruit let's say apple orange lemon or let's say banana so equal if so the logical tests will be based on uh cell b3 so if b3 is let's say an orange i want excel to tell me that it's true if it's not an orange i want it to tell me that it's false so here this is false because it's not orange and i'm going to extend it notice that i do get a true statement for this being orange if i change this to orange it becomes true if i change that to an apple it becomes false and so that's one way in which you could use the if statement you can also use it to say something other than true and false so let's say if cell b3 is a lemon so for words you need to put in quotation marks comma now instead of typing true i want to type in yes so i have to put quotations yes and the next one i can type in no or i could say it is not a lemon whatever i decide to write here it's going to replace the word false with that statement if it's not true so here i don't have a lemon so it is not a lemon and now i can extend this this one is a lemon so i get an output of yes now there's some other ways in which we can use the if function so let's say if we have some numbers 15 25 50 and 84.
so let's say if equals if cell b9 let's say if that is greater than 30 if that statement is true i want excel to basically perform a calculation rather than saying yes or no and so that calculation will be let's see if it's true i want excel to multiply b9 by 10. if it's false i want it to return a value of zero and so in this case b9 is not greater than 30 15 is less than 30 so i got a value of zero and now let's extend it here 50 is greater than 30 so this cell it performed the calculation that i wanted to do that is 50 times 10 i got 500 and here 84 is above 30 so it multiplied 84 by 10 giving me 840. now let's do something else so let's do if and let's start with b9 again so let's say b9 is less than 30.
so this time i'm gonna want excel to basically take b9 and multiply it by or rather subtract it by 10. let's do something different and if it's false i want it to divide b9 by 10. so here we know that 15 is less than 30 so we have the true statement and thus we're going to subtract the 9 by 10 giving us 5 and this is less than 30 so we do 25 minus 10 giving us 15.
now for this statement is false so we get the calculation that's involved with the false statement 50 is not less than 30. so for the false statement i wanted cell b11 to be divided by 10. so 50 divided by 10 is 5 and the same is true for this one 84 divided by 10 is 8.
4 and so you can make excel perform certain calculations if the information in this cell is true or false based on your parameters so let's say if we're analyzing this cell and let's say it has two options yes or no so we can write if cell b14 let's say if it's yes so let's put that in quotations so if we have a yes entry in cell b14 what we're going to do is we're going to take the sum of this column and if the statement is false meaning let's say if we don't have a yes such as let's say if we have a no then instead of taking the sum i'm going to take the average of this column so this is a yes which means i get the sum of those numbers so if i type in equal 15 plus 25 plus 50 plus 84 i get 174. now if i type in no i'm going to get the average instead so if we type in equal average 15 25 50 and 84 you'll get 43. 5 and so by changing yes or no i can basically cause excel to perform a certain calculation the calculation if the statement is true in this case sum or the calculation if the statement is false in this case average and so that's how you can use the if function in excel now number nine is the vlookup function so that's the next one we're going to talk about so how can we use the vlookup function well first let me fill in some information so we're going to write the name of the individual the email address let's say a phone number and also their annual revenue i'm gonna have to make this column bigger and this one smaller so let's say once again we have john smith let's say lucy johnson aaron clark david wilson and ronald james let's say the email for this person john.
s at msn. com let's say lucy well i don't want to do that let's say lucy j at live. com and then erin.
c gmail. com david w at hotmail. com and finally ronald dot j ads let's say yahoo.
com and let's fill in some numbers so let's say this is 2 4 3 and then 1 125-7463 you could fast forward this if you like i'll be done shortly and let's say the revenue for john is 56 000 per year and lucy's revenue is 74 000 per year and aaron's revenue is 82 000 per year let's say david is 93 000 per year and ronald is let's say 47 000 per year now let's talk about how we can use the vlookup function to look up information for these individuals and so here we're going to have the person's name email phone number and revenue so let's say we want to look up john smith now let's look up his email address using the vlookup function so if you type in equal vlookup parentheses notice that you have this lookup value and it's going to be whatever we look up in cell c8 next we have the table array so let's select this entire table and then the column index number so column a is not the first column because we only the first column is column b because that's the first selection in our table array now the email address is in column c which is our second column that is highlighted and the range we have two options true or false but we're going to choose false for an exact match and so we have john's email john. s msn. com so if we change his name to let's say lucy johnson automatically the email address will be updated now let's adjust the vlookup function for the next cell so the lookup value will still be cell c8 the table array will be the same and the column index number will now be column three for the phone number that's column d that's the third column that is highlighted and then let's choose false for an exact match and so we get lucy's number three five two four five two nine seven two one now the last thing we're going to look up is the revenue so our lookup value is still cell c8s this same table array column index number the fourth column that is highlighted and then let's choose false again so her revenue is 74 000.
