### Intermediate Excel

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

### Video Transcription

00:00
So you have seen that
00:02
in four. Next
00:04
Ah
00:05
ah, loop. We have
00:09
counter as essential part off the
00:12
loop structure
00:14
in do while we don't have to have it.
00:17
So
00:19
we have two variations which is due while and then loop at the end. Or do something loop while
00:25
they're similar in, but essentially different. So we'll have
00:31
both of them
00:33
explained in the examples. So let's go first to this one, which is just the stuff straight do,
00:43
uh, while and then loop.
00:46
And in this particular case,
00:50
we want to calculate,
00:52
um,
00:54
the largest sum off
00:57
integers
00:59
that will be smaller than the given number.
01:02
So we type number in this cell
01:04
and then we want to calculate so one plus two plus three plus four plus something
01:11
and that's total sum should be smaller than this number.
01:15
And let's just do the
01:19
example first. So let's run. I call this maxim
01:23
and I get 1378. I didn't display what is the last number, but let's go to the structure of this macro
01:34
and explain how it's
01:38
done.
01:38
So it's closed this one first.
01:42
So we have this sub and then we dimension dies an integer
01:49
and em Some also is an integer so we won't go
01:52
do high
01:53
And at the beginning we set em some and I to zero
01:57
And then we say Do while
02:00
m Some
02:01
is smaller than active cell value. So this is how we recall the active cell because this
02:08
ah macro has designed that we select the cell
02:13
and then it will put in the cell next to it.
02:16
The result.
02:17
So then we
02:20
menu Aly create the counter. So we say I equals eye plus one
02:25
and then we say m some equals m sum plus one and then we do loop
02:31
and it will go back
02:35
and check if M sum is still smaller than active cell.
02:38
And if it is,
02:40
it will
02:43
add one toe I and do the sub sub summation again and then
02:50
rotate until we
02:53
make em some bigger than active cell value.
02:57
Then, at that moment, we have already added one number too many
03:02
to the total sum. But we have exited the loop and then we're just putting in efs active cell.
03:09
And then we were using the perimeter offset. So with the active cell officer 0.1 means zeroes and one column
03:19
higher location than the active cell
03:23
and then value.
03:24
And then we just
03:28
make it Toby M. Some miners, I so
03:31
one I added too many. We have subtracted and we have ended this up. So you see here,
03:38
this is
03:39
moving around in the loop until we reached this condition.
03:45
In theory, we didn't have to have any kind of,
03:49
ah counter here.
03:52
We could have had ah, for example of
03:55
displayed box in which you were would be entering something yes or no? Yes or no. And regarding off the result, it would
04:08
loup loup, loup, loup, loup until
04:11
and
04:12
so this is how this one works.
04:15
Let's close this for a moment
04:16
and let's go here. So
04:19
the idea of this one is to select the range of cells
04:24
and then put to the cells next to them square root of this number here.
04:30
So
04:31
it's this function and let's edit it first.
04:35
So we're
04:41
saying
04:42
that counter is an integer
04:45
and we'd say, Do
04:46
e I was I plus one and then we say range off e,
04:55
which is
04:57
call him e.
04:58
And then
05:00
I as in
05:00
counter which represent the row
05:06
and then the value of it is equal off square root
05:12
off the cell, which is in column D ended in the same row
05:17
and what we're doing, then we're looping
05:23
while range
05:26
value off the cell in range Incom D
05:30
is different from nothing.
05:32
When we reach the first cell
05:36
that has no values in it,
05:39
we say, Okay, we have calculated them all.
05:42
And then we're just the last cell. We're putting nothing in it because we have already calculated it could be square root off nothing which is zero. And then it would be a zero in that cell. So let's just
05:58
we don't have to select the range, which is go here
06:01
and execute square with Dole
06:03
and it will calculate square roots of these numbers
06:08
and just these numbers. So when this
06:12
macro reaches empty cell, it will stop.
06:17
And, uh,
06:20
let's immediately do the do until
06:25
with the absolutely same examples. So I have
06:28
ah, first maxim, too.
06:30
It gives the same result like before,
06:33
and we who, if we would edit this function,
06:38
we see that we're doing pretty much the same thing.
06:41
Accept, because it's now until
06:45
the condition is different.
06:47
There we had that we were doing this
06:50
while I am some
06:53
active cell value is smaller easel larger
06:59
em. Some is smaller than active cell value and here were doing until we cross the threshold. Effective cell value
07:09
pretty much the same thing.
07:11
So we're just going in the loop,
07:15
adding And then again when we reach this condition, we have already added one number too many. So at the end,
07:25
the value off active cell plus one
07:28
location in the in the same row will be the calculated some minus the last counter.
07:36
And that's the one.
07:41
And the second
07:43
is the same here. So we're going to calculate
07:46
square a toll
07:48
two. We get absolutely the same results.
07:51
And
07:55
07:57
we're doing the same thing. Except we're doing global till.
08:03
So we're until the range of the is equal. Nothing. So there we were looking if it's different than nothing. And here we are now looking for empty cell, and when we find them to sell, we say, Okay, stop.
08:16
And then we just put in the last cell because we have already done the calculation and did the square root.
08:24
We just put
08:24
the empty in. The
08:28
result.
08:28
Calm in the cold e
08:31
and we're finished. So these are the loops in excel,
08:37
and you have seen how to efficiently use them
08:41
in the various calculation. So to do this
08:46
in Excel without Mac Rose,
08:48
to do it on a range that is absolutely variable would be
08:54
quite a difficult thing. It could be possibly done with some array functions and some functions, but it's a
09:01
it would be a problem, if not impossible. So this is very easy way you see how short this macro is. It's very easy way to do something like that.

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