Time
6 hours 3 minutes
Difficulty
Beginner
CEU/CPE
6

Video Transcription

00:00
Okay,
00:01
let's now see howto do the revelation of formulas. How to look for errors because errors will a cure. You have seen that in a couple of examples before. So, uh, let's go
00:14
two
00:15
the XL and we have a simple calculation. We have part. We have time measured in some experiment. We want to calculate speed. So it's where a simple formula speed is equal path over time.
00:29
So it means that this
00:32
riding by
00:34
this
00:36
is speed
00:39
and we multiplied the cells and we get the results very simple, very clear.
00:46
And we let's want to form into this to be a number. So we want you to decimals because we don't need any more precision.
00:56
And then somebody makes a mistake and enters here
01:00
something that is ah,
01:02
wrong, like minus three. And we get some something wrong Now. This is very simple formula, and it's a is easy to see where the problem is.
01:12
But ah, let's Ah,
01:17
let's, uh, look at this.
01:19
And look, it's the
01:22
value it formula
01:25
function in Excel. So we would go to formula auditing part off formulas
01:30
Deb
01:32
and click here,
01:34
and we get the formula here.
01:37
And when we go and evaluate,
01:40
and it first puts the first argument, replaces it with the value
01:46
and then puts the 2nd 1 And here we already see that something is wrong because the time cannot be negative.
01:53
But let's go all the way. It gives you the results. So it basically goes step by step through the formula. And Cox calculates its in a way that Excel is actually actually calculating your formula. So it puts the first number and instead of the reference,
02:09
because the value of the second Stella's instead of its reference and then does the calculation.
02:15
So we got this,
02:17
and, uh, we know that it was wrong The moment we saw the time is my mystery,
02:24
and you ask yourself why we do that. But can you imagine that you have 20 columns and you have, ah, 1000 trolls and suddenly you get something that makes no sense. You just pick on that cell, you go to evaluate formula, and you get or, for example, you are
02:39
pulling data from a different sheet or even from a different file, which can be done.
02:46
And then what do you do? You go to evaluate formula and see Why's it drunk? So can you imagine that somebody has by mistake play zero? Here we get the division by zero era
02:58
and we go toe are checking
03:01
and it says error in self see three
03:06
and you get the divide by zero error. The formula function uses dividing by zero or empty cells. So it can also be that, for example, here we have nothing.
03:16
All right,
03:17
In this cell here, we have nothing. Yeah, we cannot do that while this is
03:23
okay. So we can just
03:27
resume because we have
03:29
Yeah,
03:30
and we can show calculation steps so it will do the valued former life will give you that. It's 76 divided by zero
03:38
and
03:39
you get the evaluate and you get division by zero. So this is what's happening now. If this would be much complicated formula with, I don't know, 20 arguments
03:50
and ah, this division by zero doesn't happen exactly by just the referencing toe a cell that has the value of zero. But it comes as a result off formula within the formula. And you have like,
04:04
um, a miner's B and then it happens to be zero. Then it's not so obvious,
04:11
and then you would have this problem.
04:15
So let's show you how it looks. So, for example,
04:20
let's say that you don't have a stopwatch, but you have. Ah,
04:25
so you have time
04:27
start
04:29
and then you have
04:31
time and
04:34
and the formula is actually,
04:38
uh, we open brackets here and then we say
04:43
time and minus
04:46
time start.
04:49
And, uh, yeah,
04:51
my name's sorry.
04:55
My system start here
04:58
and we get something, that is Yeah, because what they should be like 46 or ah, they should be, like,
05:06
33.
05:11
And they should be, like, 125.
05:15
And they should he like, I don't know, 55 Doesn't matter. We're just showing the calculation. We just copy this
05:24
now. We don't no longer have a division by zero, But what happens if we put 33 here?
05:29
So now we have the vision by zero. It's not so obvious.
05:33
So we go toe are checking
05:36
and let's say helping d, sir,
05:40
it's, uh, this
05:43
you get
05:46
you get this
05:49
and it gives you
05:51
this page on Microsoft site that shows you what the air is. So let's close it, But let's guilt and show calculation steps. So we do the evaluate.
06:03
Yeah,
06:03
Okay. We did the rest start, so restart. So it's 76 miners, 30 derided by open brackets, 33 minus circuitry. And now we see that it zero. So something is wrong
06:17
And we did enter this wrongly. So we're just going to close this.
06:23
You're just going to close this
06:26
and say, OK, this was wrong to treat have bean 22. Somebody made the mistake when entering, and we got the correct results. So this is how evaluation and they're checking in Excel works. If you have more complex formulas, you have much more complex calculations. This is needed more and more
06:45
if you get something wrong. So maybe you have ah,
06:47
pointed to the Bronx l or, for example, here you might have entered by mistake, not see three minors B three, but the other way around be three minors,
06:59
six c chri.
07:01
And then what happens? You get the negative speed, which is something that you shouldn't have got in the first place.
07:08
So this is the reason by these things are very, very useful. If you're doing first time preparing the sheet

Up Next

Intro to Excel

This basic Excel training course will give you knowledge of Excel and the ability to use this software to do elementary calculations, file manipulation, data manipulation, to create charts, pivot tables and templates.

Instructed By

Instructor Profile Image
Milan Cetic
IT Security Consultant
Instructor