Time
8 hours 33 minutes
Difficulty
Intermediate
CEU/CPE
9

Video Transcription

00:00
and the of this module. Let's look ATT past four lessons and make short recap of what you have been able to learn.
00:09
So the
00:11
first and most important thing you have bean
00:15
able to learn
00:17
are the pros and cons off use of Harry's.
00:21
So the pros are that there is no accidental change of cells. When you, um, do a single cell formula or you define a constant or you may come full range of multi self formulas with Reyes,
00:39
they cannot be easily changed by just changing one cell.
00:43
You have to select the whole range and type the formula again.
00:47
Um,
00:49
it also eliminates the needs for intermediate formulas, making your worksheet much clearer to read.
00:56
And, uh,
00:58
you have the ability to do things that are otherwise impossible.
01:02
Now cones are
01:04
that it can slow worksheet calculation, especially if you have a very, very large number
01:11
or multi cell formulas with Berries.
01:15
Ah,
01:18
so when you type constance you, it requires two.
01:22
Do the exact sells election to be entered, so if you make a mistake, you have to type it all over again, or to just re type it. It's a complicated process, anyway.
01:34
and it requires more care, man entering. So if you forgot to press control shift, enter And you have been typing the very complex formula. You mess things up. You have to sometimes do it again.
01:46
So, um, these are pros and cons.
01:49
Ah, off the other things. You have learned what the right constants are.
01:55
What are the,
01:57
ah, single dimensional two dimensional rays? And what are the single cell? Ah ah. Think the single cell and multi cell functions with race. Also, you have learned how to use the raise in simple calculations as well as in
02:15
formulas and
02:16
much more complex formulas, especially ones that require Look up.
02:22
Um, So, um,
02:24
now we're going to use these rays much further, especially in the last model where we're going to do some very, very complex calculations
02:35
and lookups. Um,
02:37
but in the meantime, you should do some practice off what you have learned in this model.
02:43
And here is how the exercise sheet looks.
02:47
So this is, uh,
02:50
this is exercise sheet. You have three tasks, and in the first time you have the instructions. So for task one, you're supposed to create two errors in worksheet task one sheet
03:00
using cells a one to see four from Brooke Sheet task one template
03:05
one in cells A C
03:07
and A four another in cells D one
03:09
to defore. Then transform these areas into content, Constance. And then in cells a six to see nine creating area that adds thes two areas. So basically, this is this one template. So you have
03:23
this, uh,
03:24
these numbers and then on task sheet, you have to create one constant here, then one constant here and then two
03:32
making the ray, which is simply adding these two things.
03:37
So you'll see that this is not just in America have true and false.
03:42
Ah, but it will work Fine. Don't worry.
03:46
So
03:46
task to is that he s l e i three off the worksheet task to write to formula that will determine top selling week of the month and year that can be selected from drop down list in cell. I want I to
04:04
So
04:05
you have these drop down lists, so you have months
04:10
and then you have years.
04:13
So, for example, for January 2006 you have to determine make a formula that will determine which is the highest selling
04:21
Speak in that
04:23
Ah month.
04:25
Also, if you change, it should automatically re calculate this.
04:30
So this is tasked to and then we have test three. So, in currency of the worksheet,
04:36
that's three enter multi. Say, for your debt will,
04:41
um,
04:42
in both know if both numbers in every rowing comb off A and B are negative. Give their some,
04:47
um, it number in Comey's negative income, bees off every row is positive. Calculate their product.
04:54
And if they're both positive, calculate the question. So sorry.
05:00
So in task tree, you have these numbers. So you have to make a multi cell formula that will expend true cell, See, want to see 14
05:11
and to follow these
05:13
ah
05:14
conditions that were given. So you have to create a MME. Nested if to do this just as a hint.
05:23
So thank you ranch for
05:25
learning from this course so far
05:27
And good luck with your exercise

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