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

Similar Content