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
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. and let's
adjust the number format
let's click more counted formats
and then let's put currency with zero
decimal places
so her revenue is seventy four thousand
so if we change the name from lucy
johnson
to let's say erin clark
we're going to get all the information
for erin clark her email address phone
number and her annual revenue so that's
how you can use the vlookup function
the next one that we're going to talk
about number 10
is the use of a drop down list
now the reason why this is useful is
because let's say if you don't want to
type in the information if you want to
just pull the name from a drop down list
you can do that
and so a quick and simple way to make a
drop down list
is to click data
and then go to data validation
and then under the section validation
criteria
allow a list
instead of any value and then you can
type in the names that you want or you
can click in this button and then
selects these names
and then press enter
okay and now we have a drop down list
so if i select john smith it
automatically updates
if i select lucy johnson as you can see
it updates or
david wilson
i can get his email phone
and revenue or finally ronald james
and so that's the 10th useful feature
in excel
and so that's it for this video
hopefully you found it to be helpful
and if you want to find
more detailed videos that i have on
vlookup drop down lists
if functions and other stuff like that
check out the description section of
this video
and
you can find my excel video tutorial
playlist
you can also check out my channel
if you want to
find help in other topics let's say if
you're going back to school and you want
to learn algebra geometry trig precal
calculus chemistry physics i do have
playlists on those topics as well so
thanks again for watching
you