6 hours 3 minutes
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
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.
So it means that this
and we multiplied the cells and we get the results very simple, very clear.
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.
And then somebody makes a mistake and enters here
something that is ah,
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.
But ah, let's Ah,
let's, uh, look at this.
And look, it's the
value it formula
function in Excel. So we would go to formula auditing part off formulas
and click here,
and we get the formula here.
And when we go and evaluate,
and it first puts the first argument, replaces it with the value
and then puts the 2nd 1 And here we already see that something is wrong because the time cannot be negative.
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,
because the value of the second Stella's instead of its reference and then does the calculation.
So we got this,
and, uh, we know that it was wrong The moment we saw the time is my mystery,
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
pulling data from a different sheet or even from a different file, which can be done.
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
and we go toe are checking
and it says error in self see three
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.
In this cell here, we have nothing. Yeah, we cannot do that while this is
okay. So we can just
resume because we have
and we can show calculation steps so it will do the valued former life will give you that. It's 76 divided by zero
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
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,
um, a miner's B and then it happens to be zero. Then it's not so obvious,
and then you would have this problem.
So let's show you how it looks. So, for example,
let's say that you don't have a stopwatch, but you have. Ah,
so you have time
and then you have
and the formula is actually,
uh, we open brackets here and then we say
time and minus
And, uh, yeah,
my name's sorry.
My system start here
and we get something, that is Yeah, because what they should be like 46 or ah, they should be, like,
And they should be, like, 125.
And they should he like, I don't know, 55 Doesn't matter. We're just showing the calculation. We just copy this
now. We don't no longer have a division by zero, But what happens if we put 33 here?
So now we have the vision by zero. It's not so obvious.
So we go toe are checking
and let's say helping d, sir,
it's, uh, this
you get this
and it gives you
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.
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
And we did enter this wrongly. So we're just going to close this.
You're just going to close this
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
if you get something wrong. So maybe you have ah,
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,
six c chri.
And then what happens? You get the negative speed, which is something that you shouldn't have got in the first place.
So this is the reason by these things are very, very useful. If you're doing first time preparing the sheet
LPI Linux Essentials
LPI Linux Essentials practice exam helps to prepare for the LPI Linux Essentials 010-160 certification ...
Being an intermediate level user of Excel can help save a user hours of work ...
9 CEU/CPE Hours Available
Certificate of Completion Offered