Time
6 hours 3 minutes
Difficulty
Beginner
CEU/CPE
6

Video Transcription

00:00
okay, In this lesson, I'm going to talk about types of formulas at least those that are most important for
00:08
ah,
00:09
de everyday work.
00:12
So
00:13
the formula's again are the main tools Off Excel. This is what makes Excel powerful software,
00:21
and what you have as a major, four groups are mathematical formulas, which are the most common ones.
00:28
Then you have text formulas, you have logical formulas, and you have look up formulas
00:34
and I'm going to go through each one of them in the examples.
00:41
So you have seen some Matt formulas
00:45
playing this one.
00:48
And also,
00:50
uh, these formulas can included in itself
00:54
some
00:56
calculations we dare not
00:58
ah
01:00
easily obtainable for make basic mathematical. A peerage is plus minus multiplication and division.
01:10
So, um, you have when you, for example, when you go to the mat and trigonometry
01:15
formulas, you have a whole variety of them and these air functions
01:22
and these functions are there because they're here to make your life easier because, for example, you have
01:30
logarithms. So if you're using a lottery team off anything
01:34
with the base 10 for any kind of calculation mathematical, you can just put it here
01:40
and add the whole thing.
01:42
I'm not going to go into all of these formulas function. Sorry, because they are, at this point, pretty much irrelevant. Most of these functions, anyway, are used only by people who have very specific need. For example, trigonometry functions
02:00
they are there for,
02:05
you know, people who who do some calculations, engineering or mathematical. And most of the people simply don't need them.
02:13
So let me give you some more examples of formulas. So, for example, we can go with them.
02:20
Uh, sorry, uh, like
02:23
this one.
02:24
And then we wanted
02:30
squared
02:31
and you would get this
02:34
or
02:36
we wanted
02:38
to the power of three.
02:40
And we get this
02:43
Or,
02:44
for example, if you put here number nine
02:49
and we want to calculate the square root. If you remember your math, it will be this
02:54
to the power off 0.5 which square? Root and square root of nine history.
03:01
So this is an example of mathematical formula.
03:07
Now, the next type off formula is string form ur text formula. And the simple thing is,
03:15
for example,
03:16
that you have
03:20
my
03:21
first name and last name,
03:23
and for some reasons, for some self tour for some application. You want all of these in oneself,
03:30
so you would say equals. Which means you're entering formula. Every time you're entering your formula, you have to start it with equal
03:38
and then see this
03:39
and instead of plus because this is not the number, this is a text. We're going to use the ensign
03:46
and this
03:49
and we get this, unfortunately, without the space. So if we want to add the space here,
03:53
we'll just add
03:57
ah
03:59
constant
04:00
or, ah, text constant, which consists of one
04:04
blank one space.
04:08
And if we enter Blair Center,
04:12
we get my name and first name and last name separated by space.
04:17
So this is a simple example of what you can do with text in excel.
04:23
There are other functions that work with text there, quite a lot of them.
04:29
Ah, and I am going to
04:31
talk about just a few off them here because using most of them is, ah
04:40
more for some little bit more advanced level of future of excel. I'm just giving you the example what you can do and how to put the strings together.
04:51
Okay, so we have
04:55
logical
04:56
functions as well,
04:58
and they also can be used on a very primitive level or very complex level.
05:03
And, uh,
05:06
if we have ah, logical constant, which can be true
05:12
or fools.
05:16
You see how they're automatically placed in all capitals? Because this is how excel
05:23
make sure that you understand that this is not a text, that this is a logical variable.
05:30
And, uh
05:31
So,
05:34
for example,
05:36
if we want to say,
05:40
um
05:41
made me want to see to
05:44
to fields and
05:46
put them together
05:51
we can say this one
05:56
plus this one
06:00
and this will give us one
06:01
so true. Plus falls is equal to ideological or
06:08
and it will give us one because true plus false is true and this is represented by one.
06:15
Also, if we say this one
06:18
multiplied by this one is an equivalent of logical end
06:26
so true and falls together
06:30
with end operator.
06:31
They give zero because true and false,
06:36
it can. If it's true and falls, the result is false, which means zero.
06:43
So if I put here true
06:47
will get here too, because the true is equivalent off. Numerical value of one
06:55
and false is equivalent to a numerical value of zero. So true,
07:00
Plus true, is equal to one plus one.
07:04
And,
07:06
uh, true
07:10
multiplied by true is equal to one multiplied by one, which is one.
07:15
So this is how you can manipulate logic. And I'm just going to give you one simple, logical function here just to see what you can do
07:26
so you can have the function If
07:30
and then you can have a logical test, the beach can be, for example, if this cell is smaller than 40.
07:38
Sorry,
07:40
and then what happens if it's Ah well, you is true. So it's you can just put cell is smaller,
07:48
then
07:49
40
07:51
and
07:55
this is it. In case of false, we get nothing. So if you put here 55
08:01
we get false.
08:03
So this is how the if function, which is a logical function, works.
08:09
And we'll get back to, if in the future, more and more because it is a very powerful thing that can help you decide on what to do,
08:18
whether the contents of a certain cell or group of cells gives a certain result or not.
08:28
And um,
08:30
let's ah, look att, just the look up formulas or look up functions
08:35
and for example, Ah,
08:39
let's look a TTE were a simple one.
08:45
Ah,
08:46
and the this is index
08:50
and its index gives you the value off the cell at the intersection off
08:56
cells with dro and
09:00
calm.
09:01
Ah, and, uh,
09:05
let's do this.
09:09
And let's say airy or a group of cells is this.
09:13
And then row number begin. This is two and call number within. This is one
09:20
and it gave us his nine. So we selected this
09:24
range. Lookit here. A one to be four.
09:28
And then we said, Okay, give me the value of the cell
09:31
that is
09:33
first throw.
09:35
Sorry.
09:37
Um,
09:37
second row, first column. And this is this cell
09:43
and use. Look at this and say, why didn't you just pick this cell? Well, it's simple because, for example, we can
09:50
instead of firm
09:52
just giving Constance here we can hear point
09:56
two
09:56
this cell
10:00
and this cell
10:03
and we get the well you, which is something wrong because we're pointing to basically zero and zero. And it cannot be done in that ray.
10:13
But we can put here, for example, one
10:16
and two,
10:20
and then it gives us 3 67 So this is
10:24
the example off. Look up functions now we're not going to talk a lot about look functions in this
10:31
Ah course, But I just giving you the example. What Excel can do with basic look up
10:41
and this ends this
10:43
lesson.

Up Next

Intro to Excel

This basic Excel training course will give you knowledge of Excel and the ability to use this software to do elementary calculations, file manipulation, data manipulation, to create charts, pivot tables and templates.

Instructed By

Instructor Profile Image
Milan Cetic
IT Security Consultant
Instructor