0:00
hello and welcome to tutorials point in
0:02
this video we will understand what is a
0:05
function in Excel with the help of three
0:07
basic examples so let's get started with
0:10
the first example here we have a data
0:13
set of student name their scores and
0:16
based on their scores we want to
0:19
calculate their grades or basically we
0:21
want to grade these students right now
0:24
here I have written a couple of
0:25
assumptions the first is if the score is
0:29
greater than then or equal to 40 then
0:31
the student has passed the exam all
0:34
right and the second is if score is less
0:38
than 40 then the student has failed the
0:42
exam so we will use these assumptions in
0:45
our F Formula so here in the grade cell
0:49
where we want to write this formula I
0:51
have selected this cell now here we have
0:54
this FX button which means insert
0:55
function I will click on this button and
0:58
then select the function which is IF
1:00
function and then click on
1:03
okay once we click on okay we have this
1:05
function arguments dialog box which
1:08
basically wants us to enter three values
1:11
or three arguments the first being The
1:14
Logical test so what is this logical
1:17
test now this logical test is to judge
1:21
if a student is passed or failed so
1:23
basically we want to write in this
1:25
logical test that if this score so I'll
1:28
click on this as you can see it selects
1:30
the cell which is B2 so if this score is
1:36
greater than or equal to 40 all right so
1:40
this will be our logical test now what
1:43
is it if this B2 that means our student
1:47
score is greater than or equal to 40
1:49
then we have this assumption which means
1:50
the student has passed the exam so here
1:53
if this value is true we will write
1:57
pass right and if this logical test is
2:01
false then we will write
2:05
fail all right so I will press okay and
2:09
it says pass because the students score
2:12
here is greater than 40 which is 85 now
2:16
I can write the same function again here
2:18
but instead of rewriting this formula we
2:21
can simply just select the cursor here
2:23
as you can see it changes the shape and
2:26
color now we will just dra drag it down
2:29
and it automatically fills the formula
2:32
for other students so we can see all of
2:34
these students are passed except for
2:35
Michael because Michael's score is less
2:39
than 40 that is why it has written as
2:45
right now we have the next example as
2:48
you can see in this data set we have the
2:51
expenditure then we have the allotted
2:53
budget then we have the spent column and
2:57
then we need to check the status right
3:00
so basically let's say you are on office
3:02
trip and you have been to a hotel now
3:04
you have been allotted $4,500 for that
3:10
$4,000 that means you have saved $500
3:13
here for your organization so in the
3:16
status we can write that in
3:20
budget similarly here in the lunch as
3:23
you can see the allotted budget to you
3:26
$3,500 but you have spent
3:28
$3,600 which is a $100 extra so you have
3:33
crossed the budget so we can write here
3:37
budget right now we are not going to
3:40
hardcode these values we are going to
3:42
write these values with the help of IF
3:44
function so let me just delete
3:48
them so to use the IF function here we
3:52
will click on this cell right and then
3:54
we can just press here and then this
3:56
will help us insert the IF function but
4:00
we are not going to use this method this
4:02
time so let me just close it we are
4:04
going to use our keyboard so here I will
4:07
write equals and then I will write if
4:11
and then I'll say open parenthesis as
4:13
you can see it is pretty much the same
4:15
thing first we need to write The Logical
4:17
test so what is going to be the logical
4:21
test in this case understand carefully
4:24
this is our allotted budget and we have
4:26
spent this much so basically we are
4:31
spent we have spent this much if this
4:34
spent is greater than the allotted
4:37
budget then a comma and then we have to
4:41
write value if true so if the spent is
4:45
greater than the allotted budget we have
4:47
to write over budget so I'll write here
4:51
over budget and then comma and then what
4:55
if the value is false what if this
4:57
logical test is false if the pent
5:00
greater than allotted budget is false in
5:03
that case we are going to write in
5:07
budget and then I will just close this
5:10
parenthesis and hit enter and as you can
5:13
see it says in budget Now to repeat the
5:15
formula for others no we are not going
5:17
to do that instead here we'll take our
5:20
cursor and we will just drag it down and
5:23
as you can see it automatically
5:24
calculates for the hotel we are in
5:27
budget but for the lunch we have spent
5:28
$100 exra as you can see so we are over
5:31
the budget for dinner we are in budget
5:33
and for the traveling we are over budget
5:38
right now we have the last example now
5:42
in this example we have this small data
5:44
set of project name and then we have the
5:47
project deadlines and we want to check
5:49
the status now I don't know on which
5:52
date you are exactly watching this video
5:54
so for the purpose of simplification we
5:57
will assume that today is first of
6:01
2025 right so here as you can see the
6:05
project name website has a deadline of
6:08
23rd December 2024 all right so here I
6:13
want the status to be time is over all
6:16
right because this date is gone because
6:19
today is again 1st of January 2025
6:22
similarly for this one I again want this
6:25
time is over but here for the database
6:29
project the date is 23rd of January 2025
6:32
which means we still have time so I will
6:35
write here we have time now I'm not
6:38
going to write this for everyone but we
6:40
will calculate these values using the F
6:43
function very simple so let me just
6:45
delete this and here to write the IF
6:48
function I'll just click on this cell
6:52
equals if and then open parenthesis now
6:56
first is The Logical test now we need to
6:59
check if this deadline is basically less
7:03
than or equal to our today's date which
7:06
is 1 January 2025 so here I'll select
7:10
this deadline and then I'll say if this
7:13
deadline is less than or equal to now to
7:17
write the date we cannot just simply
7:20
write 01 01 we need to use another small
7:23
function which is very simple I'll write
7:26
that function it is date value so I'll
7:30
date value and inside after the date
7:34
value function I'll write parenthesis
7:36
now inside we have to write the date so
7:39
I'll write the date something like this
7:41
it is going to be 1st January
7:45
2025 all right and then I will close the
7:48
parenthesis for this date value formula
7:51
after this comma so this is our logical
7:54
test if this deadline is less than or
7:57
equal to this date of first January
8:00
2025 then what if the value is true what
8:04
if this logical test is true in that
8:09
time is over that means the deadline is
8:13
gone comma and if this logical test is
8:17
false in that case I want to write we
8:23
time so all right we have time and I'll
8:26
close the parenthesis and hit enter and
8:29
as you can see for this one the time is
8:31
over now instead of writing the function
8:33
again we can simply just place our
8:36
cursor here and drag it down for other
8:38
cells and it automatically calculates so
8:41
for the website time is over for mobile
8:44
app time is over but for these three we
8:46
still have time all right so this was
8:49
another use case of IF function in
8:54
Excel so that will be it for this video
8:57
I hope that this video has given you a
8:59
clear understanding of IF function in
9:01
Excel if you have any questions feel
9:03
free to comment down below share this
9:05
video with your friends and I will see
