00:00:00
hello and welcome to the ninth lesson of
00:00:01
this excel course I am Smith Bunsen and
00:00:03
in this video we will learn about Excel
00:00:05
formula basics before I take you to the
00:00:08
beautiful world of Excel formulas it is
00:00:10
important to understand how formula
00:00:12
works and what are its building blocks
00:00:13
let's get started with what constitutes
00:00:16
an excel formula Excel formulas are what
00:00:20
make a spreadsheet a spreadsheet this
00:00:23
forms the backbone of all the analysis
00:00:25
that can be done in Excel so to start
00:00:28
learning formulas it is imperative that
00:00:30
we understand how it is built and what
00:00:32
are its constituents so let me type
00:00:34
these three numbers here 10 20 30 now
00:00:38
let me show you very basic things that
00:00:40
can be used within a formula a formula
00:00:42
would always start with an equal to sign
00:00:44
so whenever you have an equal to sign it
00:00:46
would indicate that now you would be
00:00:48
using a formula if you do not have it
00:00:51
Excel would not recognize it as a
00:00:53
formula a very simple thing that can be
00:00:56
a part of formula is a value so for
00:00:57
example here if I type equal to 10 and
00:01:00
hit enter this becomes the value of c1
00:01:04
so now it is again you would say why do
00:01:06
I need to put an equal to before a 10 I
00:01:09
can simply do it but I am showing you
00:01:10
that this is one of the constituents
00:01:12
that can be used in a formula similarly
00:01:15
another constituent could be text so for
00:01:17
example if I type my name in double
00:01:20
quotes and hit enter then this name is
00:01:23
entered so you can see that text can
00:01:25
also be used as a constituent the third
00:01:28
constituent could be a cell reference
00:01:30
and a very simple example of it would be
00:01:32
equal to a-1 and as soon as I hit enter
00:01:35
you can see that in c1 I have the value
00:01:38
in a1 if I change this value if I write
00:01:41
my name here this value would change as
00:01:43
well so you can use a cell reference and
00:01:45
the last thing that you can use is an
00:01:48
operator so for example here are type
00:01:51
equal to a1 plus a2 and I hit enter and
00:01:57
Excel has done this calculation for me
00:01:59
and here this plus sign is the operator
00:02:03
and there are multiple operators in
00:02:05
excel in excel formulas so I can for
00:02:08
example use equal to a-1 minus a-2
00:02:13
similarly I can use other operators so
00:02:15
let's see what are the operators that
00:02:17
can be used in Excel formulas
00:02:20
here I have a list of operators that can
00:02:23
be used in Excel formulas along with
00:02:25
their description some are really simple
00:02:27
that you use in daily life some such as
00:02:29
add addition subtraction multiplication
00:02:31
division exponentiation is something
00:02:35
where you can see 10 to the power 2 or 2
00:02:38
to the power 4 concatenation is
00:02:40
something that would join two numbers or
00:02:43
text equal to greater than less than
00:02:45
greater than equal to less than equal to
00:02:47
and not equal to so these are the
00:02:49
operators now let me show you how each
00:02:51
of these works so for example I have
00:02:52
these numbers 0 10 20 and 30 now let me
00:02:57
show you how each of these works so in
00:03:00
addition it is really simple 10 plus 20
00:03:03
and I'm using these cell references here
00:03:04
10 plus 20 is equal to 30
00:03:07
similarly subtraction again 10 minus 20
00:03:10
is minus 10 in the same fashion
00:03:13
multiplication and division if you
00:03:14
multiply these two numbers then you get
00:03:16
200 and if you divide 10 by 20 then you
00:03:21
get 0.5 now in case of exponentiation
00:03:25
let me write a smaller number here I
00:03:27
have written 2 and I would say 10 raised
00:03:32
to the power 2 and as soon as I hit
00:03:35
enter you can see it says 100 similarly
00:03:38
I can also use values instead of cell
00:03:40
references this is an important which is
00:03:42
concatenation in concatenation you use
00:03:45
this sign ampersand so you use your
00:03:48
keyboard to type ampersand and you use
00:03:51
and you choose the other cell and when
00:03:54
you do this what it will do it will join
00:03:56
these two values or these could be text
00:04:00
so it would become 1 0 to 0 and if I hit
00:04:03
enter you can see it has become 1 0 to 0
00:04:06
similarly you can join two names as well
00:04:08
so for example I have my name and
00:04:10
surname here and if I want to join these
00:04:13
two cells then I would say this
00:04:15
ampersand this and now when I hit enter
00:04:18
it joins my name you can see that I have
00:04:21
somet bun cell without any space if you
00:04:23
want to introduce a space a good trick
00:04:25
for me just use a space here and again
00:04:28
use ampersand so what I'm saying is
00:04:30
submit ampersand a space AMP
00:04:33
and Bunsen now when I hit enter it gives
00:04:36
me my name with a proper space in
00:04:38
between the name and surname so this is
00:04:40
how you can use concatenate equal to is
00:04:42
a function that would return a logical
00:04:44
value so for example if I say 10 is
00:04:48
equal to 20 which I know is false and
00:04:51
now when I hit enter it gives me false
00:04:53
because this is not correct but in case
00:04:55
I have 10 here then this would give me
00:04:58
true let me control Z and go back
00:05:00
similarly I have this option greater
00:05:02
than again I would go here and I would
00:05:04
say 10 is greater than 20 which is again
00:05:07
false so I get a false similarly it has
00:05:10
less than 10 is less than 20 which is
00:05:14
true so it returns a true now I also
00:05:17
have this option of greater than equal
00:05:19
to so for example let me have this value
00:05:24
10.1 here now if I use this formula this
00:05:29
value is or maybe not d1 this value
00:05:34
d5 is greater than equal to N and I hit
00:05:40
enter and it says true but if I change
00:05:43
this value to 10.1 again this remains
00:05:47
true because this is not greater than
00:05:49
but it is equal to so it assesses these
00:05:51
two conditions it could either be
00:05:53
greater than or it could be equal to if
00:05:55
any of this condition matches then it
00:05:57
returns a true let me control Z to go
00:06:00
back similarly we have less than equal
00:06:02
to so I would hit D 1 is less than or
00:06:05
equal to this value and again this is
00:06:08
true if I change this to nine point nine
00:06:10
then it becomes false because now 10 is
00:06:13
greater than and not less than and in
00:06:16
the end we have not equal to not equal
00:06:18
to as the name suggests would just
00:06:19
compare two values if they are not equal
00:06:21
then it would give you a true else it
00:06:25
would give you a false so for example if
00:06:27
I compare 10 is not equal to nine point
00:06:30
nine and I hit enter you can see that
00:06:32
this gives me true
00:06:36
suppose if I go to the salon I create a
00:06:38
formula then multiplied by two plus 20
00:06:49
divided by 2 multiplied by 10 now can
00:06:57
you predict what would be the answer
00:06:59
so how would Excel execute this function
00:07:01
would it start executing from left to
00:07:03
right right to left or there are certain
00:07:06
operators which are more important and
00:07:07
are executed first let me show you what
00:07:10
the result is the result is 120 now
00:07:12
let's go back and see what happened in
00:07:15
this case if I look at it
00:07:16
Excel first calculated this part which
00:07:19
is d 1 multiplied by d 4 d 1 multiplied
00:07:22
by d 4 this is 20 then Excel started
00:07:25
with this part and this was calculated d
00:07:28
4 D 2 multiplied divided by d 4 so D 2
00:07:32
divided by d 4 is 10 and this was
00:07:34
multiplied by 10 so this became 100 and
00:07:37
this became 20 now I am able to
00:07:40
calculate this because I know that there
00:07:42
is an order of precedence I have not
00:07:43
added these terms before I have used the
00:07:46
multiplication and division operators
00:07:48
because there is an order of precedence
00:07:50
you can predict what would be the answer
00:07:51
and in these cases it is good to know so
00:07:54
that you would get an answer that you
00:07:56
expect instead of something random so
00:07:58
let me show you what is the order of
00:08:00
precedence this these are the
00:08:04
president's order number and these are
00:08:06
the operators so exponential would get
00:08:08
the highest preference if we have
00:08:10
something like this if I say 10 raised
00:08:14
to the power 2 plus 10 then in this case
00:08:18
you would be able to predict that first
00:08:20
this would be OP executed and then 10
00:08:22
would be added to it so if I hit enter I
00:08:26
would know that the answer would be
00:08:28
hundred and ten similarly the second
00:08:30
order of precedence Falls with division
00:08:33
and multiplication so if in a in a
00:08:37
formula there is a division or
00:08:38
multiplication it would be executed
00:08:40
before all these signs the third is plus
00:08:43
n minus the fourth is concatenation and
00:08:46
the last in this series is equal to
00:08:49
then less than greater than equal to or
00:08:50
less than equal to now what if you want
00:08:53
to override these precedents you can do
00:08:55
this by using round brackets or
00:08:58
parenthesis so for example let me take
00:09:00
the same example I would say 10 raised
00:09:05
to the power 2 plus 10 now in this case
00:09:11
what if I want to add these two first
00:09:13
and then make it 10 to the power that
00:09:16
number so if I have to do this I would
00:09:18
use round brackets and when I use round
00:09:21
brackets it over writes the order of
00:09:24
precedence in this case whatever is
00:09:26
within the bracket would be executed
00:09:27
first and then the order of precedence
00:09:29
would kick in so in this case it would
00:09:31
become 10 raised to the power 12 as soon
00:09:33
as I hit enter you can see it gives me a
00:09:35
huge number which is 10 raised to the
00:09:37
power 12 similarly if you have a huge
00:09:39
formula you can absolutely forget this
00:09:42
order of precedence and only use
00:09:44
parentheses to make sure that something
00:09:46
that you need to be executed first is
00:09:49
actually executed first because it is in
00:09:50
round brackets so this is one way to
00:09:53
completely forget about it but anyways
00:09:55
it is a good idea to know this order of
00:09:57
precedence so that it gives you the
00:09:58
answer that you expect
00:10:02
now there are two ways you can insert a
00:10:04
formula in an Excel worksheet so for
00:10:06
example let me say I want to insert an
00:10:08
average formula here in this cell one of
00:10:11
the ways is to go to the formula tab and
00:10:13
within this tab you have this option
00:10:14
insert function you can also use the
00:10:17
keyboard shortcut shift f3 and as soon
00:10:20
as you click shift f3 you can see that
00:10:22
Excel automatically inserts an equal to
00:10:24
sign because it now knows that you are
00:10:25
going to insert a formula and you can
00:10:27
search for the formula so if you are new
00:10:29
to excel and you do not know the formula
00:10:31
that you want to use to find the average
00:10:33
then you can simply type the word
00:10:36
average here and click on go and as soon
00:10:39
as you click on go you can see that this
00:10:41
would give you a list of functions that
00:10:43
can do this work for you so they can
00:10:45
average it for you now it may not always
00:10:48
be very accurate it may give you a list
00:10:49
of all these functions and you may have
00:10:51
to scroll down and see what you want but
00:10:53
you would have this these lines here
00:10:55
that can help you guide in choosing the
00:10:58
right function so in this case I want to
00:10:59
choose average I would click on it
00:11:01
average and I would click on OK and as
00:11:03
soon as I do it it inserts this function
00:11:06
and opens the function arguments dialog
00:11:08
box this is the dialog box where I would
00:11:10
insert the argument so if there is a
00:11:12
function it would always have a couple
00:11:14
of arguments it could be either a single
00:11:16
argument it could be more than one
00:11:17
arguments or there could be functions
00:11:19
which do not take any argument such as
00:11:21
today function that would simply give
00:11:23
you the date or now function that would
00:11:25
give you the date and time so average
00:11:27
actually has a couple of arguments in it
00:11:29
if you see here it has two fields number
00:11:32
one and number two and it also has some
00:11:33
description below so it says returns the
00:11:35
average arithmetic mean of its arguments
00:11:38
which can be numbers or names arrays or
00:11:41
references that contain numbers and
00:11:44
since I have clipped on number one field
00:11:46
it also gives me a short description of
00:11:48
this argument which says number one
00:11:50
number two are 1 to 255 numeric
00:11:54
arguments for which you want the average
00:11:56
so here I could type a number here I
00:11:58
could type a cell reference and I could
00:12:00
have 255 of these arguments within one
00:12:03
average function and as soon as I type
00:12:05
something L so for example I type 10
00:12:07
here it would also give me a formula
00:12:09
result since I only have
00:12:11
argument as of now the average is 10 but
00:12:13
if I go and type 20 here you can see
00:12:15
that the formula result changes to 15 if
00:12:17
you are not even able to use the
00:12:20
function properly after this window you
00:12:22
can also go to the help on this function
00:12:24
so if you click on it you would see that
00:12:26
Excel opens the help for you if you are
00:12:28
using 2010 then the window might look
00:12:31
different but again the helpful open so
00:12:33
this is one way and Microsoft help is
00:12:35
probably the best place to learn about
00:12:38
Microsoft Excel functions it may go a
00:12:40
bit in detail but again I would
00:12:42
reiterate this is the best place to work
00:12:44
to learn about Excel functions now when
00:12:46
I click OK I would have the result and
00:12:49
if you look at the formula bar I have
00:12:51
the entire formula along with the
00:12:52
arguments now this is one way but this
00:12:55
is not the most efficient way and people
00:12:58
who have been using Excel would agree
00:12:59
that the most efficient way would be to
00:13:01
simply type is equal to and start typing
00:13:04
the name of the function so let me show
00:13:06
you what happens as soon as I start
00:13:07
typing as soon as I type a I have this
00:13:10
list of functions this entire list of
00:13:13
functions and I can choose what function
00:13:16
I want because all these start with a
00:13:18
but if I know I want to use the average
00:13:20
function I would continue typing I would
00:13:22
type of e and as soon as I type V it
00:13:24
would give me the name of all the
00:13:25
functions that start with a and we and
00:13:28
let me type average and as soon as I
00:13:30
continue to type it would give me filter
00:13:33
this list and only give me those
00:13:34
functions that start with these
00:13:36
alphabets so I know it has average I can
00:13:39
simply hit tab and if the function is
00:13:42
selected and if this is the function
00:13:43
that I want to use if I hit tab this
00:13:45
would aught to complete the function if
00:13:47
this is not the function I want to use
00:13:49
then I can navigate this list by using
00:13:51
the arrow keys in this case I want to
00:13:53
use average so I would hit tab as soon
00:13:55
as I hit tab it autocompletes the
00:13:57
function now if you want to go to the
00:14:00
help for this function you can simply
00:14:02
click on it and if I click on this you
00:14:05
can see it opens the excel help but I do
00:14:07
not want to go to the excel help I want
00:14:08
to open the argument box where I can
00:14:11
insert these arguments there are two
00:14:13
ways to do it again you can click on FX
00:14:14
or simply use the keyboard shortcut
00:14:17
shift f3 and as soon as you use shift f3
00:14:20
it opens the function arguments dialog
00:14:22
box and you can insert these
00:14:23
arguments if you do not want to use
00:14:25
these arguments you can go end
00:14:27
manually so for example let me type
00:14:30
again average I hit tab to autocomplete
00:14:31
the function and here I can manually
00:14:33
type these now these could be numbers
00:14:35
that I am typing in so for example 10
00:14:37
and 20 and I hit enter and this gives me
00:14:41
15 or these could be cell references so
00:14:43
here I use 10 here I have 10 here I have
00:14:46
20 and I go to this cell and instead of
00:14:50
these numbers I would use these cell
00:14:52
references so to use these cell
00:14:53
references I could manually select one
00:14:55
by one
00:14:56
I have b1 comma d2 and I close the
00:15:00
bracket and hit enter it gives me the
00:15:02
right result or I can simply select it
00:15:05
at one go and it says D 1 is 2 d2 and it
00:15:09
would consider this entire range and now
00:15:11
when I close the bracket and hit can hit
00:15:13
enter it gives me the same answer again
00:15:19
in this case I had these cell references
00:15:22
in this sheet only but you can have it
00:15:24
in a different sheet as well so for
00:15:25
example here I have another sheet and I
00:15:27
have these numbers 10 20 and 30 now if I
00:15:31
again go back to the function type
00:15:34
average hit tab to autocomplete the
00:15:36
function and here instead of selecting
00:15:38
these I can also go to the inner another
00:15:40
sheet and select these functions so for
00:15:42
example I can select these three and you
00:15:45
can see here in formula bar you can drag
00:15:48
this down you can see here in formula
00:15:50
bar it has this entire reference which
00:15:52
says sheet 3 exclamation marks C 3 is 2
00:15:55
C 5 so this is the convention that Excel
00:15:58
follows when you are referencing other
00:16:00
sheets and now I would close the tab and
00:16:03
hit enter and it gives me the right
00:16:06
result because it is averaging 10 20 and
00:16:08
30 so this is how you can use a
00:16:10
references in us in an excel formula you
00:16:14
can either use values you can use text
00:16:16
you can use references
00:16:20
if you have a formula in Excel you can
00:16:22
edit it by going to the formula bar so
00:16:25
here as soon as you select the cell you
00:16:27
would see the formula in the formula bar
00:16:29
and when you select the formula you
00:16:31
would see if there are any references
00:16:32
that could that would get highlighted in
00:16:34
different colors in this case I only had
00:16:36
one reference so it is using blue color
00:16:38
but if you have multiple references then
00:16:40
Excel would use multiple colors so the
00:16:42
easiest ways to just go to the formula
00:16:43
bar and edit this formula you can also
00:16:45
go to this cell directly and a double
00:16:48
click and as soon as you double click it
00:16:50
enters into the edit mode you can also
00:16:52
enter into the edit mode by pressing f2
00:16:54
and as soon as you press f2 you see now
00:16:57
you can edit this formula so these are
00:16:59
the ways you can change a formula or
00:17:01
edit it or modified
00:17:05
if you're learning about Excel formulas
00:17:08
to know about referencing cell
00:17:10
referencing is really really important
00:17:12
there are three ways you can reference a
00:17:15
cell and in terms of excel lays out
00:17:17
these are called absolute referencing
00:17:19
relative referencing and mixed
00:17:21
referencing let me show you what this
00:17:23
means so for example a very very simple
00:17:25
example is if I go to the cell and I
00:17:27
press equal to and I click on a one and
00:17:30
I hit enter then this is a simple
00:17:33
example of relatives have a cell
00:17:35
referencing where if I drag this down
00:17:38
you can see that the cell references
00:17:41
changes and it is now relative to the
00:17:44
row it is in and the column it is in so
00:17:46
for example here this refers to a 1 this
00:17:49
cell refers to a to this cell refers to
00:17:52
a 3 and so on this cell refers to a 10
00:17:55
if I copy this formula if I ctrl C go
00:17:58
here and copy this formula this cell
00:18:00
would refer to a 18 similarly if I go to
00:18:02
the right and I go here and I press
00:18:05
control V this cell would refer to F 1
00:18:08
the reason being that this was referring
00:18:10
to a cell which was to sell left to it
00:18:13
similarly this is again referring to a
00:18:15
cell which is no cell left to it so this
00:18:18
is called relative cell referencing when
00:18:20
your cell reference changes in relation
00:18:23
to where your position of the cell is
00:18:26
now let me clean this let me show you an
00:18:30
example of absolute cell referencing an
00:18:32
absolute cell referencing means that it
00:18:34
will not change no matter where you put
00:18:37
it
00:18:37
and to do that a simple convention is
00:18:40
you use a dollar sign and here I would
00:18:43
put dollar sign dollar sign would mean
00:18:46
that if it is in front of the column
00:18:49
alphabet which is a it would mean that
00:18:51
this column number would not change if
00:18:53
you copy this reference similarly if it
00:18:56
is in front of the row number then it
00:18:59
means that this row number would not
00:19:00
change no matter where you put it so for
00:19:03
example now let me click enter I copy
00:19:06
this and I paste it somewhere here again
00:19:09
if I go and put it into edit mode by
00:19:13
clicking f2 you can see it still refers
00:19:15
to a-1 no matter where you put it in
00:19:18
this
00:19:19
it would still refer to a 1 so this is
00:19:21
absolute cell referencing where no
00:19:24
matter where you put it it would still
00:19:26
refer to the same location to it which
00:19:28
it referred originally an important
00:19:31
trick here is that if you go and click
00:19:33
on the cell reference you can change
00:19:35
these by clicking f4 so now if I hit f4
00:19:39
you can see that the dollar sign in
00:19:41
front of a goes away
00:19:42
similarly the dollar sign in front of
00:19:44
one would go away if I press again if I
00:19:46
press f4 again and now it is in front of
00:19:48
a and now if I press press it for the
00:19:51
third time it becomes a relative cell
00:19:53
reference now what you just saw a dollar
00:19:56
sign in front of a but not in front of
00:19:58
one or the vice-versa dollar sign in
00:20:01
front of one but not in front of a that
00:20:02
is called a mixed reference so now that
00:20:05
we have understood relative cell
00:20:07
reference and absolute cell reference
00:20:08
let's go and understand mixed cell
00:20:10
reference here I have some data set and
00:20:12
say I want to do a simple thing I want
00:20:15
to divide all these numbers by 100 and
00:20:17
put these here and I want to divide all
00:20:20
these numbers by thousand and put it put
00:20:22
these numbers here so to do that I would
00:20:24
select 10 divide this by 100 and I would
00:20:28
click I would hit enter it gives me the
00:20:31
right result which is 0.1 but now if I
00:20:33
drag this down it gives me the wrong
00:20:35
result and I'm sure you would have
00:20:37
identified by now that if I check this
00:20:40
since this was not an absolute cell
00:20:43
reference it dragged this down so now
00:20:45
when I put it down it had shifted by one
00:20:48
row here and by one row here but I do
00:20:52
not want this value to shift I only want
00:20:54
this value to shift so I would do a
00:20:56
simple thing I would make this absolute
00:20:58
and true that I would hit f4 once and
00:21:01
now when I drag this down you can see
00:21:05
that it works perfectly this is point
00:21:07
one it is supposed to be point one point
00:21:09
two point three so these are the right
00:21:11
results but what happens when I drag
00:21:13
this to the right it gives me one but I
00:21:17
should be getting point one because one
00:21:19
hundred divided by thousand is point one
00:21:21
the reason being that now when I made
00:21:24
the denominator absolute no matter where
00:21:27
I dragged this it would always be
00:21:29
referring to this cell only and this is
00:21:32
not what I want
00:21:33
I want that while I drag this down it
00:21:35
should refer to this cell but when I go
00:21:37
to the right it should refer to this
00:21:39
cell which means that I want to make it
00:21:41
partially absolute or what they call
00:21:44
mixed reference so to do that I would
00:21:46
simply go here and if you check this I
00:21:49
want this to move across columns but I
00:21:52
do not move on it to move across rows
00:21:55
which means that I would want a dollar
00:21:57
sign in front of the row number but not
00:21:59
before the column number so I would hit
00:22:01
f4 and I have what I want now I would
00:22:05
hit enter now if I drag this down it
00:22:08
gives me the right result and now if I
00:22:10
drag this to the right it gives me the
00:22:12
right result as well I can again simply
00:22:14
track this term now if you if you go to
00:22:17
any of these cells you would see that
00:22:18
now it refers to the right cell
00:22:20
references you can drag this again to
00:22:22
the right I do not have the data so it
00:22:23
gives me an error but you can see that
00:22:25
it would shift based on these but if you
00:22:28
drag this value down it would still keep
00:22:32
on referring to this value which means
00:22:34
that you have fixed the row number but
00:22:37
you have not fixed the column number so
00:22:39
this is called mixed referencing now
00:22:41
these are very very important in
00:22:42
formulas you may want you may have a
00:22:45
huge data set and you want to create a
00:22:47
cell reference and if you as soon as you
00:22:49
drag this down it can completely
00:22:50
jeopardize if you do not know how to
00:22:52
work with absolute relative or mixed
00:22:54
cell references
00:22:58
now as you go forward in this course and
00:23:01
learn about a lot of formulas you will
00:23:02
notice that in some cases you have to
00:23:04
create really huge formulas which are
00:23:07
really really complicated and it may
00:23:09
become a bit tedious if it returns an
00:23:11
error so for example you might not be
00:23:12
able to identify where is the error you
00:23:15
may have to revisit the entire formula
00:23:17
Excel has some features that can make it
00:23:20
easy for you so for example let me type
00:23:22
a very simple formula as of now I'm
00:23:23
using a very simple formula because you
00:23:25
have not gone through this course but
00:23:27
you can have any formula here and this
00:23:29
trick would work so I would use some and
00:23:31
I would add these two plus I would use a
00:23:34
sum again add these two and then let's
00:23:38
use average add these three now when I
00:23:42
hit enter this gives me the result as
00:23:44
160 if for example this is not the
00:23:47
result that you expect or if there is an
00:23:50
error in this result then you can check
00:23:53
where the error is by going to the
00:23:55
formula tab and within this go to the
00:23:57
formula auditing option and here you
00:23:59
have this option evaluate formula and as
00:24:02
soon as you click on evaluate formula
00:24:03
you would see that evaluate formula
00:24:06
window opens up with the entire formula
00:24:07
in this part which says evaluation now
00:24:10
click on this button once and it would
00:24:13
give you the result of the first part of
00:24:15
a formula when you click on evaluate
00:24:17
twice it gives you the result of the
00:24:19
second part and similarly when you click
00:24:21
on it and again and again keep on
00:24:22
clicking on it it would keep on doing
00:24:25
these operations for you and you would
00:24:26
see that this is the result and now
00:24:27
again if I click evaluate this is the
00:24:29
final result now for example there is an
00:24:33
error and let me purposely introduce an
00:24:36
error let me purposely introduce that
00:24:38
here is only so I would say I want to
00:24:41
divide this number by zero now I know if
00:24:43
I divide any number by zero it would
00:24:45
give me an error so here is the error I
00:24:46
know it but imagine that if this is a
00:24:48
huge formula and you want to identify
00:24:50
where the error is go to evaluate
00:24:52
formula
00:24:52
this opens the evaluation evaluate
00:24:54
formula dialog box and hit evaluate and
00:24:57
as you are doing it you would notice
00:24:59
that at some point of time it puts a
00:25:01
division and you would be able to
00:25:03
identify that this is the step where you
00:25:05
have the error so you would know that
00:25:07
the first two steps are working fine but
00:25:09
the third step is something where you
00:25:10
have
00:25:10
the division error let me control Z and
00:25:13
go back to the original formula now
00:25:15
there is another weight of B bugging
00:25:17
formulas and and this is the way that I
00:25:19
prefer because this using this you can
00:25:21
really go granular so for example you
00:25:23
have this entire formula if you just
00:25:25
want to see the result of this part then
00:25:28
select it using your mouse or by using
00:25:31
the f2 key and then using the arrow keys
00:25:33
and press f9 and as soon as you press f9
00:25:37
Excel would evaluate and give you the
00:25:39
result of only that part which is
00:25:41
selected so now I know that the last
00:25:43
part of this formula returns 60 I would
00:25:45
control Z and go back similarly I can
00:25:48
select only this part and hit f9 and
00:25:51
this gives me 70 let me control Z and go
00:25:53
back I can select parts of it so I would
00:25:56
select this entire part these two parts
00:25:58
and I press f9 and this gives me the
00:26:01
result so this is a really powerful way
00:26:03
of debugging your formulas just to be
00:26:05
careful of one thing so for example if I
00:26:08
go to this formula I select this I press
00:26:10
f9 it gives me 60 but remember to
00:26:13
control Z and go back to the original
00:26:15
formula because if you do not if you
00:26:18
click enter then this value 60 gets
00:26:22
hard-coded in the formula which was not
00:26:24
originally there so your formula would
00:26:25
go away just be a bit cautious
00:26:27
do you remember to control Z when you do
00:26:29
this so these are two powerful ways to
00:26:31
audit your formula or to debug your
00:26:33
formula that is it in this video I hope
00:26:35
you found this useful thank you and have
00:26:37
a nice day
