Time
8 hours 33 minutes
Difficulty
Intermediate
CEU/CPE
9

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

Instructor Profile Image
Milan Cetic
IT Security Consultant
Instructor