# Evaluating Formulas, Looking For Errors

Video Activity

Join over 3 million cybersecurity professionals advancing their career

Sign up with

or

Already have an account? Sign In »

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

Instructed By

Similar Content