WEBVTT

00:00.240 --> 00:02.679
hello and welcome to tutorial's point in

00:02.679 --> 00:04.960
this video we will learn the five most

00:04.960 --> 00:06.839
basic and important functions in

00:06.839 --> 00:09.639
Microsoft Excel also this practice sheet

00:09.639 --> 00:11.240
is available to download in the

00:11.240 --> 00:14.280
description below so let's get started

00:14.280 --> 00:17.119
starting with the first basic and most

00:17.119 --> 00:19.600
important formula is the sum function

00:19.600 --> 00:21.480
now to demonstrate the sum function we

00:21.480 --> 00:24.599
have a example of monthly sales data

00:24.599 --> 00:26.560
here as you can see these are the

00:26.560 --> 00:29.279
month's column and these are the sales

00:29.279 --> 00:32.238
column and we want to find the total

00:32.239 --> 00:36.360
sales right so the first way is to

00:36.360 --> 00:38.160
Simply click here where you want the

00:38.160 --> 00:40.520
total sales to be calculated and then

00:40.520 --> 00:42.680
here you can see we have this FX which

00:42.680 --> 00:45.520
means insert function button so if we

00:45.520 --> 00:48.440
click on it here is the insert function

00:48.440 --> 00:50.960
button dialogue and here you can see we

00:50.960 --> 00:53.840
have a function named as sum if we click

00:53.840 --> 00:57.440
on okay it asks for numbers to be summed

00:57.440 --> 00:59.760
so we can simply select this number and

00:59.760 --> 01:01.680
then for the second number we can select

01:01.680 --> 01:04.119
the second number for the third number

01:04.119 --> 01:06.439
we can select the third number for the

01:06.439 --> 01:08.520
fourth number we can select the fourth

01:08.520 --> 01:11.400
number but it is too tiresome so we're

01:11.400 --> 01:13.200
not going to do this one I'll just let

01:13.200 --> 01:16.759
me just cancel it or close it now what

01:16.759 --> 01:19.479
we need to do here is here inside this

01:19.479 --> 01:22.159
sale I'll write equals and then the

01:22.159 --> 01:26.000
formula that is suum sum and then

01:26.000 --> 01:28.720
parentheses and then I will select all

01:28.720 --> 01:32.159
these numbers now we have only 12 rows

01:32.159 --> 01:34.240
here but what if we had thousands of

01:34.240 --> 01:37.000
rows it'll be difficult to you know drag

01:37.000 --> 01:39.159
down these rows something like this all

01:39.159 --> 01:42.560
the way to, so another thing that we can

01:42.560 --> 01:45.119
do here is we can understand that these

01:45.119 --> 01:49.960
values start from this b 2 all the way

01:49.960 --> 01:53.920
to this B 13 so here in the sum I can

01:53.920 --> 01:55.479
write

01:55.479 --> 01:58.719
B2 and then a colon which means all the

01:58.719 --> 02:00.560
way to B

02:00.560 --> 02:03.960
B13 which selects all these values now

02:03.960 --> 02:07.200
if I close the parenthesis and hit enter

02:07.200 --> 02:09.759
we have the total sales calculated

02:09.759 --> 02:13.280
easily using the sum function now the

02:13.280 --> 02:15.760
next important function is the average

02:15.760 --> 02:18.599
function here we have some students with

02:18.599 --> 02:21.120
different marks in maths English and

02:21.120 --> 02:23.680
Science and we want to find the average

02:23.680 --> 02:26.800
grade for Alice average grade for Bob

02:26.800 --> 02:30.160
and the average grade for chlie now the

02:30.160 --> 02:32.239
first step is very simple we'll use the

02:32.239 --> 02:35.040
average formula so here in this cell we

02:35.040 --> 02:37.360
can simply just click here in this

02:37.360 --> 02:39.800
insert function and then we can look for

02:39.800 --> 02:41.640
the average function which is right here

02:41.640 --> 02:44.400
and then click on okay then it will ask

02:44.400 --> 02:47.040
for the numbers so the number one the

02:47.040 --> 02:49.480
number one is this one and then number

02:49.480 --> 02:53.480
two is this one number three is this one

02:53.480 --> 02:57.040
if we click on okay it Returns the

02:57.040 --> 03:00.000
average sum but that is a bit time

03:00.000 --> 03:02.400
taking so I'll just delete it and we'll

03:02.400 --> 03:05.720
do it in a couple of seconds so here I'm

03:05.720 --> 03:10.400
just going to write equals and then

03:10.400 --> 03:13.080
average and then parenthesis and then we

03:13.080 --> 03:16.360
will select all these numbers similarly

03:16.360 --> 03:17.959
and then we are going to close the

03:17.959 --> 03:20.920
parentheses and hit enter and we have

03:20.920 --> 03:24.080
the average grade of Alice now we can do

03:24.080 --> 03:27.080
the same for Bob writing equals average

03:27.080 --> 03:29.480
but again this is a repetitive work we

03:29.480 --> 03:33.080
don't want to do it so simply we'll just

03:33.080 --> 03:35.400
put our cursor here and here as you can

03:35.400 --> 03:38.239
see the cursor changes its color you

03:38.239 --> 03:41.280
just drag it down and it is going to

03:41.280 --> 03:43.799
calculate the average for the remaining

03:43.799 --> 03:48.239
two students very simple next we have

03:48.239 --> 03:51.599
the median formula so here we have an

03:51.599 --> 03:54.200
example data set of employees of a

03:54.200 --> 03:57.599
company and their salaries in column B

03:57.599 --> 04:00.640
now we want to find the median salary of

04:00.640 --> 04:04.239
this company right so here in the sale

04:04.239 --> 04:06.040
we can just simply click here where we

04:06.040 --> 04:09.120
want to find the Medan and then we can

04:09.120 --> 04:11.560
just click here on this FX which means

04:11.560 --> 04:13.760
insert function but that will be too

04:13.760 --> 04:16.919
tiresome so to just simply use our

04:16.919 --> 04:20.519
keyboard I'll write equals median and

04:20.519 --> 04:22.840
then open parentheses then we will

04:22.840 --> 04:25.759
select all these numbers now this is a

04:25.759 --> 04:28.440
bit tedious task again to select all of

04:28.440 --> 04:30.720
these numbers now these numbers are just

04:30.720 --> 04:34.000
34 but what if we had more than 100,000

04:34.000 --> 04:36.639
numbers it'll be difficult to select

04:36.639 --> 04:39.080
them like this so instead of selecting

04:39.080 --> 04:41.360
them like this we can simply see that

04:41.360 --> 04:44.240
the salary starts at

04:44.240 --> 04:47.759
B2 and ends at

04:47.759 --> 04:51.320
b34 so here in our formula I can simply

04:51.320 --> 04:57.039
write it starts at B2 colon and ends at

04:57.039 --> 05:00.280
b34 and now close the parentheses and

05:00.280 --> 05:04.520
hit enter and we have the median salary

05:04.520 --> 05:08.000
here next we have the Min and Max

05:08.000 --> 05:11.080
functions in Excel now as the name says

05:11.080 --> 05:12.919
the Min function is used to calculate

05:12.919 --> 05:14.880
the minimum value and the max function

05:14.880 --> 05:16.759
is used to calculate the maximum value

05:16.759 --> 05:19.080
from a given data set here we have an

05:19.080 --> 05:21.240
example of these products and their

05:21.240 --> 05:24.039
prices and we want to find the minimum

05:24.039 --> 05:26.479
price among these all so we can simply

05:26.479 --> 05:31.039
write here equals min parentheses and

05:31.039 --> 05:34.639
then we select all of these and then we

05:34.639 --> 05:37.360
close the parentheses and hit enter and

05:37.360 --> 05:39.280
the minimum value is

05:39.280 --> 05:42.360
$200 similarly for the maximum price we

05:42.360 --> 05:45.840
can write equals Max parenthesis and

05:45.840 --> 05:48.840
then select the prices column and then

05:48.840 --> 05:51.360
close the parenthesis and hit enter and

05:51.360 --> 05:53.400
the maximum price among these products

05:53.400 --> 05:54.280
is

05:54.280 --> 05:58.080
$1,200 now this may not seem useful

05:58.080 --> 06:00.039
because this is a fairly small small

06:00.039 --> 06:02.919
data set but if we have a very large

06:02.919 --> 06:04.720
data set that has thousands and

06:04.720 --> 06:07.680
thousands of rows then these formulas

06:07.680 --> 06:12.080
are very very useful last is the most

06:12.080 --> 06:14.360
important Excel function and it is the

06:14.360 --> 06:17.120
IF function so here we have some sample

06:17.120 --> 06:20.680
data set of salesperson their sales and

06:20.680 --> 06:23.680
their target and based on their sales we

06:23.680 --> 06:26.199
want to check if they are eligible for

06:26.199 --> 06:30.360
bonus or not now here we can simply use

06:30.360 --> 06:32.800
the IF function we can just click here

06:32.800 --> 06:35.319
in the insert function or to be more

06:35.319 --> 06:37.680
quick we can simply use our keyboard so

06:37.680 --> 06:39.880
here I'm going to double click and I'll

06:39.880 --> 06:43.160
write equals and then if and then

06:43.160 --> 06:45.720
parenthesis and as you can see it has

06:45.720 --> 06:48.400
three values it has The Logical test and

06:48.400 --> 06:51.240
then it has the value if true and then

06:51.240 --> 06:54.680
the value if false so for The Logical

06:54.680 --> 06:59.840
test we would like to check if the sales

06:59.840 --> 07:04.560
so right here if this sales is greater

07:04.560 --> 07:09.080
than or equal to this target all right

07:09.080 --> 07:12.160
and if it is then comma we are going to

07:12.160 --> 07:15.960
write that this salesperson is eligible

07:15.960 --> 07:21.160
for the bonus and if it is not if this

07:21.160 --> 07:25.120
logical test is not true then comma we

07:25.120 --> 07:27.240
will say

07:27.240 --> 07:31.199
not eligible and then let me just close

07:31.199 --> 07:34.199
the parenthesis and hit enter and as you

07:34.199 --> 07:37.479
can see this Alice salesperson is

07:37.479 --> 07:39.720
eligible because she has completed her

07:39.720 --> 07:42.879
sales Target now we can write again for

07:42.879 --> 07:45.720
the others but again it is a repetitive

07:45.720 --> 07:48.800
work we would like to avoid that so here

07:48.800 --> 07:50.919
we are going to drag this here as you

07:50.919 --> 07:52.879
can see the color changes of the cursor

07:52.879 --> 07:55.479
and we will drag this all the way down

07:55.479 --> 07:57.879
and it will automatically replicate the

07:57.879 --> 08:00.039
formulas for other person

08:00.039 --> 08:03.479
so Alice is eligible Bob is not Charlie

08:03.479 --> 08:06.879
is eligible David is not and Eva is

08:06.879 --> 08:10.240
eligible so that's it that will be

08:10.240 --> 08:12.280
enough for this video now you can find

08:12.280 --> 08:14.319
the link in the description to download

08:14.319 --> 08:15.840
this Excel sheet so that you can

08:15.840 --> 08:18.120
practice it yourself now make sure to

08:18.120 --> 08:20.240
like this video subscribe to the channel

08:20.240 --> 08:22.560
and I will see you in the next Excel

08:22.560 --> 08:25.240
video