### Intermediate Excel

Course
Time
8 hours 33 minutes
Difficulty
Intermediate
CEU/CPE
9

### Video Transcription

00:02
so in a continuance off.
00:07
Listen
00:10
with the examples,
00:12
we will go
00:14
to another example
00:16
the second part of this lesson.
00:19
And here we have the famous \$250 cookie recipe. I don't believe nobody has heard the Reed. So there is a popular
00:29
ah story. It's one of the first spoofs on chain letters on the Internet.
00:36
It was actually there before the Internet. While
00:40
people are still exchanging,
00:42
uh, males, why are different kinds of means by bulletin boards?
00:48
And basically, somebody invented the story that people went toe. Um ah, department store went to get eat something. They found some very nice cookies, asked for a recipe,
01:00
and they told him that because 2 50 So they
01:04
paid with the card and they got the bill. Later, when they looked at it, they realized the cost of recipes \$250 instead of \$2. 50 cents.
01:15
Anyway, this is very nice
01:18
cookie recipe. I have tried it. So it makes really great cookies, and it's publicly available on the Internet, and I have used it as a example in this,
01:29
uh,
01:30
listen, so we want to convert.
01:34
Ah, the
01:38
this, uh
01:40
metric, the imperial units, the metric if needed.
01:44
And, uh,
01:47
we want to do it so that when we
01:49
converted to metric, we can convert it back to Imperial if needed. So let's say that we have, ah, standardized way off writing
01:59
recipes in Excel, and we keep them in one file,
02:04
and we just want
02:06
the way to do it.
02:07
So,
02:08
um,
02:10
the way the recipe structured these, we have the recipe name.
02:15
Then we have a line with ingredients. Amount unit
02:17
02:22
uh, these ingredients,
02:25
and when they finish, we have a single cell with instructions, and then we have instructions.
02:32
Ah, separated in easy, uh,
02:38
perform herbal actions,
02:40
uh, be the serial number.
02:43
So we want D's
02:46
to be converted to these. And we have a comparison chart, and these are the units were goingto change with our macro. So we have ounces. It goes to grams, we have cups, it goes two liters,
03:00
we have pounds, it goes to kilograms, and we have pints. If they're there, they go to leaders. So the idea is that we have,
03:08
uh, in both ways were a simple,
03:13
uh, change. So if something is given in grams.
03:15
It should be translated tow ounces,
03:20
but it's something is given kilograms, then it's probably a lot of its show, so we should convert it to pounce.
03:28
Also with cups they go Two liters and pints day also goto literature. This It's a slightly problematic think and now we have a conversion rate, which is from this to this. So
03:43
if we have
03:45
five ounces, then we multiply with 28.3 and we get grams. But if we get grams, we just
03:54
divide the amount. For example, we have 200 grams of chocolate. We divided 28.3 and we get ounces,
04:02
which is a little bit over six ounces. So
04:06
this is a comparison chart. We don't need to hide it. It's just there, and we want to do this calculation
04:15
now. First thing we're going to do is we're going toe insert the shape which is going to be a
04:24
round the direct and do,
04:27
and we're just going toe put here
04:30
fixed,
04:32
convert,
04:35
and then
04:38
we're going to change the text size
04:42
so it fits
04:49
like 14.
04:51
You're going to put it in the middle.
04:55
Uh, we can change the
05:00
front
05:05
and we go here
05:09
and then we go home and every click here is Well, so we sent through it on the
05:15
work took Alexis is well,
05:16
and we just place it over here,
05:20
and when we finish everything, we're going to assign macro to it.
05:27
So
05:28
first of all,
05:30
we have to decide
05:32
what to do when we click this. We have to check if these units are metric or imperial.
05:40
05:42
05:44
and we're going to go through it. It's called Decide
05:48
and let's edit it.
05:51
And at this point, this macro is just
05:57
displaying message boxes, imperial or metric, And what is doing? So we have ah
06:02
ah counter
06:04
for going through the
06:08
ah
06:10
comparison chart.
06:12
And, uh, we have Ah,
06:15
when the,
06:16
uh, logical
06:19
variable court, we call it in. I called it Imperial
06:23
and its default setting to false, and we're checking if this is
06:30
imperial or not. So we're going to have a do loop until
06:34
loop
06:35
and we see So we set eye to three. Why?
06:40
Because the first
06:43
line off ingredients always start on in the third row
06:48
and then we check if range, see and I So first pass it's C three equals
06:58
ounce then Imperial is true. If not, we check for other imperial measurements. So
07:04
cup
07:05
and again it's true.
07:08
If it's not, we check if
07:11
there is found there.
07:13
Well, I see three
07:14
just the check. So see, trees this
07:17
and
07:18
then if it's not bound, we check for point. Because we have these four units there.
07:26
And then again it's true. If not,
07:29
we close and structure. We have four ends.
07:32
We
07:34
see
07:36
I equals eye plus one. So remove one row lower
07:41
and we go loop until range or off A and I equals instruction. So we started at three
07:48
and the end of this check. If nothing happened, where it happened,
07:53
we increase this too
07:55
four.
07:56
And then we look, if their instructions Why? Because we can have between I don't know, two and 22 or 25 ingredients in this list. So we have to do the do look because we don't know how many lines we have arose. We have before we reach to instructions
08:15
Why we do that for every line because we can have
08:18
things like eggs
08:20
and, uh, I don't know.
08:24
Ah
08:26
oranges or lemons,
08:28
which don't have units because it's just the two or three or five of them.
08:33
So the recipe might not contain
08:39
anything here.
08:41
Probably there is going to be something like,
08:45
uh, some measurements, but it may be all in
08:48
teaspoons and tablespoons. So then we don't need any conversion.
08:52
Uh, but this is it. So
08:58
look at here
08:58
and we display
09:01
message books, Imperial or metric.
09:05
And if we start this macro
09:07
here,
09:09
so
09:09
we go to decide,
09:11
we get information that this is obviously imperial metric.
09:16
So I have already created that macro man, Let's stop here and look at it in the next video, which will continue this lesson.

### 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

Milan Cetic
IT Security Consultant
Instructor