Top 10 Most Important Excel Formulas - Made Easy!

In this video we're going to talk about 10 functions that are typically used in Excel, so the first one we'll go over is the average function. 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, write average equals in parentheses and then highlight the column of data you want to average, so the average of these numbers is 53,125 and that is a very simple way to use the average function in Excel now, let's say if you want to average two numbers quickly, you can write equal average and then let's say we want to average 100 and 200.

So write 100 point 200 near the parenthesis and you will get 150. If you want to average three numbers, you can write the three numbers, let's say one hundred two hundred three hundred, the average of those three numbers is two hundred and that is another way we can use the average function in Excel. Now, the next function that we're going to talk about is the addition function, so let's say if we want to add some numbers, if we want to find the sum of the numbers in this list, we can write equal sum parentheses and then highlight the column you want. we want to find the sum of and then the sum of those numbers is 429 and just like before, if we want, we can find the sum of two numbers, let's say 50 and 125. 50 plus 125 is 175.

You can also write equal 50 plus 125. and as you can see you will get the same answer now let's say if you want to find the sum of three numbers you can write sum equals 100 point 200 point 300 and that will give you 600 or you can write This way it is equal to 100 plus 200 plus 300, so that there are different ways that you can add numbers using the sum function and 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 basically find the sum of all these vehicles, we can 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 just the Mazda vehicles, so we don't want to find the sum of all these numbers, but basically a selected sum, we want to find the sum of a certain element in this list, so what we need to do is write sum equals if in parentheses and then we need to select a range. so the range will be the car type and then the comma the criteria will be based on what's in this cell cell b14 and then the comma the sum range so these are the numbers we want to add in parentheses enter , this should be the card type actually instead of mazda, now let's put the word mazda in this cell and notice that we get the total sum of all the mazda vehicles, so it's 125 plus 142 plus 425 and you can check it if you type sum equals 125 point 142 point 425 it will give you 692.

Now you can change it, let's say if we want to find the sum of Honda vehicles, it will adjust automatically. There is only one type of Honda vehicle in this list, so it is 174. If we want to find the sum of all toyota vehicles, just type it and it will automatically give us a sum, so we have 326 for toyota 89 and 274. So, If we write equal 326 plus 89 plus the other one which is 274, we get 689, so The sum if function helps us find the sum of a certain element in this list instead of the sum of all these numbers in that list.

Now let's move on to our function four, which will be the count function, so how can we use the function? count function the count function allows us to count if you read it it says it counts the number of cells in a range containing numbers so we can use it to count this column so we have a total of ten cells containing numbers , so this is the first one second third fourth fifth sixth seventh eight nine ten so there are ten ten cells that contain numbers now if you try to use it with a column of data that does not contain numbers you will get a value of zero so that is the function of count four functions, it helps you to count the number of cells with numbers, next is the count function or count a function, so this function basically allows you to count the number of cells that are not empty, so they could be cells. with a text or a number so if we highlight these cells we will 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 in this it will only count two numbers because it counts 25 and 37 it does not count this one which contains the letters and numbers so that is the count function if we use the count function or count a function will count all the functions that are not empty and therefore counts all of these functions.

If we try to use it in this entire range, it still gives a six, it doesn't count the empty cells now there. is a function that counts the empty cells and that is the count blank function, so notice that we have three empty cells, one, two, three, and that is the count blank function, so now you know how to use the count function and post a function in Excel. The number six will be the count if function, so the count if function is very similar to the sum if function that allows us to count a certain selection in this entire column, so let's change this to count if, so it will be the same count if and then we need to select the range so this is the range and then the criteria which is a cell will be whatever is in a cell b14, so notice it tells me how many times Toyota appears in this list, so we have one two. three now let's choose moz actually let's choose something different let's choose honda honda should appear in the list once now I'm going to add another mazda to the list so we have one two three four so let's replace toyota with mazda because the selection stops here so I have a total of four types of Mazda cars on this list, so if I change it to Mazda it will count as four.

Now I only have two Toyota vehicles on this list, so if I trade it in to Toyota I'll get two, and that's the count. works so instead of counting all the card types that we have here, which should be about 10, if we use the count function, the count function if it allows us to count a certain selection in that list, it can allow us to count only the Toyota vehicles or just Mazda vehicles and this is how you can use the count if function in this example, next we have our next function, which is number seven, tonight, so let's increase the width of that column and convert it back to a cell white, then 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, then let's say we have the name john smith, let's say kelly williams and then jackie garcia. and then we will say lisa clark and let's say david johnson now let's use the concatenate function, so I will write like can can tonate and then text in parentheses one and then text with a comma to close the parentheses and thus notice that it puts the information in the column or in the cell b2 and connects it to the information in cell c2 and if I want I can extend this information now instead of making that notice that we need a space in the middle, so to put that space, go ahead and type concatenate equal again and then we will type in cell b2, you can just highlight it and then comma in quotes, type your quote, space, quotes and then comma in cell c2, close the parenthesis, so now we will put a space between columns b and c.

So the concatenate function allows you to basically combine the information in two columns and connect it in one column and you can put anything in between so you can put a space or any other data that you want to put between those two columns. 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 banana, then still, if so, the logic tests will be based on uh cell. b3 so if b3 is let's say an orange I want

excel

to tell me it's true if it's not an orange I want it to tell me it's false so here this is false because it's not orange and I'm going to extend it notice that I get a true statement because it is orange, if I change this to orange it becomes true, if I change it to an apple it becomes false and that's one way 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 then for the words you have to put quotes comma now instead of writing true I want to write yes so I have to put quotes yes and next I can write no or I could say no is a lemon.

Whatever I decide to write here, I'll replace the word false with that statement, if it's not true then I don't have a lemon here so it's not a lemon and now I can extend this, this is a lemon so I get a result of himself. Now there are other ways in which we can use the if function, so let's say if we have some numbers 15 25 50 and 84. then let's say if is equal to if cell b9 let's say if that is greater than 30, if that statement is true , I want Excel to basically do a calculation instead of saying yes or no, and 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 in this case b9 doesn't is 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 did the calculation what I wanted to do is 50 times 10, I got 500 and here 84 is above 30 , so I multiplied 84 by 10, which gives me 840. Now let's do something else, so let's do if and start with b9 again, so let's say b9 is less. than 30. So this time I want Excel to basically take b9 and multiply it 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 therefore we are going to subtract 9 times 10, which gives us 5 and this is less than 30, so we do 25 minus 10, which gives us 15. Now, this statement is false, so we get the calculation 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 goes 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 according to your parameters then let's say if we are analyzing this cell and let's say it has two options yes or no so we can write yes cell b14 let's say yes it is yes , then let's put it in quotes, so if we have a yes entry in cell b14, what we're going to do is take the sum of this column and if the statement is false, let's say if we don't have a yes, like let's say if we have a no, so 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 write equals 15 plus 25 plus 50 plus 84 I get 174 .now if I type no, I will get the average, so if we write the average equals 15 25 50 and 84 you will get 43.5 and so by changing yes or no, I can basically make Excel perform a certain calculation, the calculation if the statement is true in this case the sum or the calculation if the statement is false in this case the average and this is how you can use the if function in Excel now The number nine is the vlookup function, so that is the next one that we are going to talk, then how can we use vlookup function?

Well, first let me fill out some information, so we'll write the person's name, email address. Let's say a phone number and also your annual income. I'm going to have to make this column bigger and this one smaller, so let's say once again that we have john smith, let's say lucy johnson, aaron clark, david wilson and ronald james, let's say this person's email. john.s on msn.com let's say lucy well I don't want to do that let's say lucy j on live.com and then erin.c gmail.com david w on hotmail.com and finally ronald dot j ads let's say yahoo .com and fill in some numbers , so let's say this is 2 4 3 and then 1 125-7463.

You can advance this if you wish. I'll finish shortly and let's say John's income is 56,000 per year and Lucy's. income is 74,000 per year and Aaron's income 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 search for information for these people and so here we are going to have the person's name, email, phone number and income, so let's say we want to search for John Smith. Now let's find his email address using the vlookup function, so if you type in vlookup equal parentheses, notice that you have this. search value and it will be what we look for in cell c8.

Then we have the table array, so let's select this entire table and then the column index number so that column a is not the first column because only the firstcolumn is column b because that is the first selection in our table array, now the email address is in column c which is our second highlighted column and in the range we have two options true or false but we will choose false for an exact match so we have john email john.s msn.com so if we change her name to say lucy johnson automatically the email address will be updated now let's adjust the vlookup function for the next cell so that the lookup value will still be cell c8, the table matrix will be the same and the column index number will now be column three for the phone number which is column d which is the third highlighted column 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 for is the income, so our lookup value is still cell c8s, this same index number of table matrix column, the fourth column that is highlighted and then choose false again so that your income is 74 000. and let's adjust the number format, click on more counted formats and then put currency with zero decimals so that your income is seventy and four thousand, so if we change the name of lucy johnson to let's say erin clark we are going to get all the information of erin clark, her email address, phone number and her annual income, this is how you can use the vlookup function.

The next one we're going to talk about is number 10, which is the use of a drop. dropdown list now the reason this is useful is because let's say if you don't want to type the information, if you just want to extract the name from a dropdown list, you can do that and so it's a quick and

easy

way to create a drop down list is click on data and then go to data validation and then in validation criteria section allow a list instead of any value and then you can type the names you want or you can click this button and then select them. names and then press enter okay and now we have a drop down list so if I select john smith it updates automatically if I select lucy johnson as you can see the updates or david wilson can I get his email phone and income or finally ronald james and This is the tenth useful feature in Excel and that's it for this video.

I hope you found it useful and if you want to find more detailed videos that I have in the vlookup dropdown lists, if there are features and other things like that, check the description. section of this video and you can find my Excel tutorial video playlist. You can also visit my channel if you want to find help on other topics, say if you are going back to school and want to learn algebra, geometry, trigonometry, precalculus, chemistry. Physical. I have playlists on those topics too, so thanks again for checking out.