Video Transcription

00:01

in previous lesson, you have been able to learn what

00:06

a raise in Excel where sheets are,

00:09

um, what kind of rays have so single dimensional, which occupy cells grouped in single row or single comb

00:19

and two dimensional, which consists of for cells

00:23

grouped in

00:25

a T least two rows and to colds.

00:29

Then you have been able to learn how to enter a constance

00:35

and, uh, especially the most important parties when they were. You do something with the Rays and Excel. When you finish typing in the cell, you press control shift enter.

00:47

In this lesson, we're going to go with step further and talk about how to use the race and functions. And let's go directly

00:56

to, uh,

00:58

just a reminder that the rays and formulas can be really useful in calculations and simple functions so you can use them with mathematical statistical functions, and you can also use the race within formula. Is there a constant spitting formulas if needed? But you don't have to.

01:15

So the examples we have here

01:19

are without using a race array constants in formulas.

01:23

And, uh, this is, uh, this example consistent were simple.

01:29

Ah,

01:30

sales data table that might be have been entered in small company that sells furniture or furniture items.

01:38

And we have five of these. So we have table chair bar still so fun armchair,

01:44

and we have the price per unit.

01:46

And then we have quantities

01:49

entered the end of each week of how many units off each item has been sold.

01:55

And we just want to calculate the total sales for the period of four weeks

02:01

now in every example with the rays and going to try to explain to you how it's much simpler to do it with a raised in classical way without them.

02:12

So in this case,

02:14

if you want to do it the traditional way, we would have to add additional conduit, for example, monthly

02:22

sales

02:24

per item.

02:27

Okay, this is not exactly monthly sales, but, period, the four weeks is approximately one,

02:32

and then we simply multiplied the price

02:37

with some

02:38

off sales for all four weeks.

02:43

Close it through percenter,

02:46

and then we just multiplied this formula toe all five rows with 545 items,

02:52

and then we just

02:54

enter

02:57

the sum

02:58

where these we get the number of 58,900 and 20

03:02

and now the same thing done with

03:06

Aires.

03:07

So again it's a sum.

03:09

And then we're going to use array for this

03:13

colon. These five cells,

03:15

which are going to of course, be multiplied with

03:20

some

03:21

off all these.

03:23

So we're going to

03:24

just

03:27

track Ah, add old these. Sorry,

03:31

and make this calculation.

03:35

And then at the end of the entering, we just press control shift, enter.

03:39

Then we get the exact number.

03:42

Now, don't be wrong. When you're entering creating such formula,

03:46

you have to,

03:49

you know,

03:50

multiply a apples with apples, not apples with oranges.

03:53

So we have here

03:55

this column

03:58

which has prices,

04:00

and then we multiply it with

04:02

huge colon

04:04

added. Together,

04:06

we cannot do this

04:11

some off

04:13

and then this column

04:15

and then multiplied with

04:18

this

04:19

because this is completely wrong. Because what were then trying to do is the plum. Multiply this

04:27

with this

04:29

and this is not what we want to do.

04:32

Ah Ah! Sorry. Um and then we had this.

04:39

So if you would

04:41

make the formula like this

04:44

just to see how wrong this is

04:50

Clothes.

04:51

Apprentices with press control shift enter

04:55

and we get completely different number

04:58

now in Ah,

05:00

orderto understand. Why is this the case? Let's do devaluate formula just to see what's happening.

05:09

So we do the value it and then we have

05:13

244 51 for 5300 and 25 multiplied with.

05:20

And then we have 86 84 98

05:26

60

05:27

five

05:30

plus

05:32

And then again, this

05:34

and then again this and then again, this

05:40

And when we have this all together,

05:44

this is the sum of the numbers.

05:46

And you understand, why is this different? So we have to understand the logic off how

05:53

these formulas work.

05:56

So

05:58

when you're trying to

06:00

multiply some numbers in the rays and formula, you have to put

06:04

comes to the columns, not comes to the rose.

06:09

Another example

06:12

off using raising simple functions

06:15

is this. We have ah,

06:17

not like the coach that this training, traveling dean,

06:21

and they're starting to starting the five week training program and they want to see how

06:29

this whole team has improved after five weeks off training so that they can

06:34

see if the training method is working.

06:38

So they have their results. The lengths that they have thrown at the beginning of the training period and then at the end of the training period. And we want just the average.

06:51

We don't want it to look by athlete. We don't want it to look by,

06:56

Uh,

06:57

anything else, Just the average for the team.

07:01

So what are we going to do?

07:04

In traditional way, we would say improvement

07:11

per athlete,

07:14

and then we would

07:15

make a simple formula in which we divide

07:18

this

07:19

with this,

07:21

and then we subtract one meaning we don't want to see, because if we would do it without strictly when we get 105 points or seven. Now, this Atley didn't improve for 105.7%. No, he has improved, actually,

07:41

four

07:43

5.7%.

07:46

Now, we copy this formula here,

07:49

and then we calculate the average by

07:54

the wedding, some off all these improvements

07:59

by seven,

08:01

and we get the number of four

08:05

0.98

08:09

out to do it with a raise.

08:11

We're going to type it very simple. Just going to say

08:15

average,

08:18

which is basically the function that it's average,

08:20

and then we're going to use race. So we're going to Ah,

08:28

the white

08:28

but there were is in cold. See it can be. And then it yet from the results were going to subject one.

08:37

And we don't forget to press control, shift, enter

08:39

and we get the same result. So it shows you how simplified can be the calculation in excel when using race.

Up Next

Intermediate Excel

Being an intermediate level user of Excel can help save a user hours of work time and take full advantage of the Excel application. Taking an intermediate Excel training will help students advance their skills and pass an intermediate Excel exam.

Instructed By

Similar Content