0:00
hello and welcome to tutorial's point in
0:02
this video we will learn the five most
0:04
basic and important functions in
0:06
Microsoft Excel also this practice sheet
0:09
is available to download in the
0:11
description below so let's get started
0:14
starting with the first basic and most
0:17
important formula is the sum function
0:19
now to demonstrate the sum function we
0:21
have a example of monthly sales data
0:24
here as you can see these are the
0:26
month's column and these are the sales
0:29
column and we want to find the total
0:32
sales right so the first way is to
0:36
Simply click here where you want the
0:38
total sales to be calculated and then
0:40
here you can see we have this FX which
0:42
means insert function button so if we
0:45
click on it here is the insert function
0:48
button dialogue and here you can see we
0:50
have a function named as sum if we click
0:53
on okay it asks for numbers to be summed
0:57
so we can simply select this number and
0:59
then for the second number we can select
1:01
the second number for the third number
1:04
we can select the third number for the
1:06
fourth number we can select the fourth
1:08
number but it is too tiresome so we're
1:11
not going to do this one I'll just let
1:13
me just cancel it or close it now what
1:16
we need to do here is here inside this
1:19
sale I'll write equals and then the
1:22
formula that is suum sum and then
1:26
parentheses and then I will select all
1:28
these numbers now we have only 12 rows
1:32
here but what if we had thousands of
1:34
rows it'll be difficult to you know drag
1:37
down these rows something like this all
1:39
the way to, so another thing that we can
1:42
do here is we can understand that these
1:45
values start from this b 2 all the way
1:49
to this B 13 so here in the sum I can
1:55
B2 and then a colon which means all the
2:00
B13 which selects all these values now
2:03
if I close the parenthesis and hit enter
2:07
we have the total sales calculated
2:09
easily using the sum function now the
2:13
next important function is the average
2:15
function here we have some students with
2:18
different marks in maths English and
2:21
Science and we want to find the average
2:23
grade for Alice average grade for Bob
2:26
and the average grade for chlie now the
2:30
first step is very simple we'll use the
2:32
average formula so here in this cell we
2:35
can simply just click here in this
2:37
insert function and then we can look for
2:39
the average function which is right here
2:41
and then click on okay then it will ask
2:44
for the numbers so the number one the
2:47
number one is this one and then number
2:49
two is this one number three is this one
2:53
if we click on okay it Returns the
2:57
average sum but that is a bit time
3:00
taking so I'll just delete it and we'll
3:02
do it in a couple of seconds so here I'm
3:05
just going to write equals and then
3:10
average and then parenthesis and then we
3:13
will select all these numbers similarly
3:16
and then we are going to close the
3:17
parentheses and hit enter and we have
3:20
the average grade of Alice now we can do
3:24
the same for Bob writing equals average
3:27
but again this is a repetitive work we
3:29
don't want to do it so simply we'll just
3:33
put our cursor here and here as you can
3:35
see the cursor changes its color you
3:38
just drag it down and it is going to
3:41
calculate the average for the remaining
3:43
two students very simple next we have
3:48
the median formula so here we have an
3:51
example data set of employees of a
3:54
company and their salaries in column B
3:57
now we want to find the median salary of
4:00
this company right so here in the sale
4:04
we can just simply click here where we
4:06
want to find the Medan and then we can
4:09
just click here on this FX which means
4:11
insert function but that will be too
4:13
tiresome so to just simply use our
4:16
keyboard I'll write equals median and
4:20
then open parentheses then we will
4:22
select all these numbers now this is a
4:25
bit tedious task again to select all of
4:28
these numbers now these numbers are just
4:30
34 but what if we had more than 100,000
4:34
numbers it'll be difficult to select
4:36
them like this so instead of selecting
4:39
them like this we can simply see that
4:47
b34 so here in our formula I can simply
4:51
write it starts at B2 colon and ends at
4:57
b34 and now close the parentheses and
5:00
hit enter and we have the median salary
5:04
here next we have the Min and Max
5:08
functions in Excel now as the name says
5:11
the Min function is used to calculate
5:12
the minimum value and the max function
5:14
is used to calculate the maximum value
5:16
from a given data set here we have an
5:19
example of these products and their
5:21
prices and we want to find the minimum
5:24
price among these all so we can simply
5:26
write here equals min parentheses and
5:31
then we select all of these and then we
5:34
close the parentheses and hit enter and
5:39
$200 similarly for the maximum price we
5:42
can write equals Max parenthesis and
5:45
then select the prices column and then
5:48
close the parenthesis and hit enter and
5:51
the maximum price among these products
5:54
$1,200 now this may not seem useful
5:58
because this is a fairly small small
6:00
data set but if we have a very large
6:02
data set that has thousands and
6:04
thousands of rows then these formulas
6:07
are very very useful last is the most
6:12
important Excel function and it is the
6:14
IF function so here we have some sample
6:17
data set of salesperson their sales and
6:20
their target and based on their sales we
6:23
want to check if they are eligible for
6:26
bonus or not now here we can simply use
6:30
the IF function we can just click here
6:32
in the insert function or to be more
6:35
quick we can simply use our keyboard so
6:37
here I'm going to double click and I'll
6:39
write equals and then if and then
6:43
parenthesis and as you can see it has
6:45
three values it has The Logical test and
6:48
then it has the value if true and then
6:51
the value if false so for The Logical
6:54
test we would like to check if the sales
6:59
so right here if this sales is greater
7:04
than or equal to this target all right
7:09
and if it is then comma we are going to
7:12
write that this salesperson is eligible
7:15
for the bonus and if it is not if this
7:21
logical test is not true then comma we
7:27
not eligible and then let me just close
7:31
the parenthesis and hit enter and as you
7:34
can see this Alice salesperson is
7:37
eligible because she has completed her
7:39
sales Target now we can write again for
7:42
the others but again it is a repetitive
7:45
work we would like to avoid that so here
7:48
we are going to drag this here as you
7:50
can see the color changes of the cursor
7:52
and we will drag this all the way down
7:55
and it will automatically replicate the
7:57
formulas for other person
8:00
so Alice is eligible Bob is not Charlie
8:03
is eligible David is not and Eva is
8:06
eligible so that's it that will be
8:10
enough for this video now you can find
8:12
the link in the description to download
8:14
this Excel sheet so that you can
8:15
practice it yourself now make sure to
8:18
like this video subscribe to the channel
8:20
and I will see you in the next Excel
