Welcome to Highline BI348, class number 10.
Hey, if you want to download this workbook-- BI348,
chapter 7-- and this is the second file, click
on the link below the video.
Hey, in this video we've got to talk about tracking down
formula errors.
Now, here's a bunch of steps here.
And basically, you can think of errors as two types--
an error for a formula that gives you an incorrect answer
where it gives you the number or the text that you think
you should get, but the answer's incorrect,
or you actually have some error.
Now, here's a bunch of steps.
Here's the short version of these steps
that we will use on our next sheet.
Before we go over to this sheet and look
at some great examples, I just want
to look at this list of errors.
Now, here's a description.
And this is what it looks like.
Divide by zero is simple.
If you divide a number by zero, you're going to divide by zero.
Now, the reference arrow, watch this.
Notice that formula says equals C26.
And when I enter it, you can see it up in the formula bar.
There's C26.
But if I come down to C26 and right-click Delete, now,
right-click Delete does not delete content.
It actually removes the cell.
And it will always ask you, which direction
do you want to shift things?
Now, this is usually a dangerous thing because if I shift it up
and there's a table down below, it actually
shifts just part of the table up.
But watch this.
As soon as I delete this, click OK, that's a reference error.
No more C26.
Anytime you see reference exclamation point,
it means the cell reference was deleted.
Name, if I said SUMM function and then highlighted,
name always means there's some text in the formula that Excel
does not recognize as either a function name,
because there's no S-U-M-M function.
There's an S-U-M function.
Or it doesn't recognize it as a defined name or a table name.
Or it might be that your text in your formula,
you forgot to put it in double quotes.
Now, what is that?
Notice, that error's right there.
That error actually means either one of two things.
Either the column is not wide enough, which is the case here,
or you can have negative time.
You always have to take the later time minus the earlier
time.
And I'm not going to show you that one.
But if you do earlier time minus later time,
that's what you'll see.
N/A, we'll see that later in this video.
That's when you look something up, and it's not available.
Value error, that's when you do something silly
like take a word and try to multiply it by a number.
You can't take a word times a number.
Value error can also mean you have the wrong argument type,
or you entered your array formula, which
requires Control, Shift, Enter, without Control, Shift, Enter.
Null error, you're never going to run into that.
That's an intersection error.
NUM, that means the number is either too small or too big
or IRR function can't find an answer.
And an example of a number that would be too big
is, hey, 24 caret 307.
It's just too big for Excel to handle.
We talked about pound signs and circular reference.
Watch this.
SUM and I'm going to highlight a few cells below
and accidentally include the cell that contains the formula.
If you do that and enter it, it says, hey, we
have a circular reference.
The fix, of course, is to not include the cell
that the formula is sitting in.
We meant to go from C26 to C28.
All right.
Now let's go over to the sheet.
Click on Error Examples.
This is the model we were working on from video 2 to 9.
And here we have some errors down here.
Here's our list.
We always want to use F2 and check if the formula's correct.
Check if the cell references are pointing to the right cells.
We always want to check if these two are right,
whether the formula inputs or the raw data
are correctly entered, meaning the correct numbers
or correct text.
We can also use Formula Evaluator and the F9 key
to evaluate, which we will see.
And there's also a feature called Trace Precedents.
Now, let's just come and start with this formula, F2.
The formula looks good, right, equals, SUM, and a range.
But check this out.
I'm looking up at the range.
And I have one, two, three, the right variable cost per unit.
But check that out.
I accidentally included revenue.
So the range is incorrect.
Now, there's a few ways we can fix this.
One way is you could click on the screen tip.
And once it's highlighted, you can simply
drag to get the correct ranges.
Escape, F2.
Another way you could do it is you could come down and notice,
well, it's supposed to be 5 to 7,
so I'm going to change this to B8 to B7.
And as soon as I type 7, it updates.
Escape.
Still another way, you can simply point to the corner.
And when you see your diagonal cursor, click and drag
to get the correct range.
And when I hit Enter, 997.
That's still not right.
So this is often the case that you check for one and two.
You've got the formula.
You've got the ranges and cell reference right.
If all of those are right, then it has to be number four.
We have to look through formula inputs and the raw data.
It means one of these numbers is not correct.
And if we go back and look at our original description,
Assigned Indirect Costs was not $1.50.
It was $1.15.
So if you're sure that the formula's correct,
then it has to be some of the formula input.
Now, what about this one?
Value, remember that was either the wrong argument
in your function or F2 text times a number.
So sure enough.
And this is common.
We accidentally click on one of the labels for our formula
inputs.
I'm simply going to click and drag.
And now we have the correct two inputs.
For Total Variable Costs, I need Variable Cost
per Unit and Total Units Produced.
And so when I hit Enter, that's correct.
Now let's look at our next one, F2.
Sure enough.
I'm looking, formula's correct, cell plus cell.
And the input, Total Variable and Total Fixed, you know,
that's looking fine.
Enter.
This is Total Revenue.
And we can see our formula right here.
So when I hit F2, it looks like I should have--
and there it is.
I'm looking right off the bat.
It should be Revenue times Unit times 1 minus the Discount
Rate.
So I'm simply going to point to the edge,
and click and drag to get the correct Revenue.
So Revenue looks right, just like it says over there.
Quantity is right.
And 1 minus the 2% is right.
So Control, Enter.
Oh, but look at this.
I have a check figure.
That's not even close.
Well, F2, wait a second.
Times, times, parentheses, minus, the formula's
looking correct.
The cell references are correct.
I'm going to try number five here, the Formula Evaluator.
Up on the Formula ribbon tab, there's
a bunch of auditing tools.
And the one I use the most is Evaluate Formula.
Now, if you click on this and them click Evaluate, Evaluate,
it will step through and show you how
Excel calculates the formula.
I'm going to click Escape because I've
got to teach you the keyboard.
I use it so much that I use the keyboard, Alt, M, V.
And it opens it up.
Now, we know from dialogue boxes that if a button is
highlighted, you don't have to use your mouse.
You can use the Enter key to enact it.
Before I hit Enter, though, look up here.
It'll always underline what it's about to evaluate.
So right now it's going to look in cell B8.
So I'm going to hit Enter.
B8, that should be Revenue.
So 1,650, that's correct.
Now it's going to evaluate B13.
Enter.
So 2,000 is correct.
Now it's going to evaluate the multiplying.
Enter.
That's looking OK.
Now it's going to evaluate B9.
Enter.
And there it is.
Using Formula Evaluator, we see that it's
not 2% like we actually saw in the spreadsheet.
Underneath the Number Formatting,
someone accidentally entered 0.015, which is 1 and 1/2%.
So I'm going to click Escape and come here.
Now, when I click on that cell, I can immediately
look up in the Formula bar.
And I see the problem.
I could also increase decimals.
And there we go.
Someone accidentally put 1.5 and, with no decimals,
displays as 2%.
So I'm simply going to type a 2.
Notice because it's pre-formatted with percentage,
that percentage symbol appears.
So now when I Enter, I have my 2%.
And now I have my correct match number.
Now we have another error here.
Oh, it's not.
It's just simply I need to widen the column.
Whoa, wait a second.
That is completely the wrong answer because Total Profit,
well, I'd love to have whatever billion dollars
or whatever that is.
So I better check this out.
F2.
It's supposed to be revenue minus expense.
Oh, yeah, someone accidentally put a multiplication symbol.
So I'm just looking at the formula
and saying that's supposed to be minus.
So I Enter.
And sure enough, that is correct.
Now, this formula is the Units Break Even Point.
And we know from earlier using Goal Seek that this should be
2,661 point something or 2,662.
So I'm going to hit F2.
And look at this.
Now, the cell reference is Fixed Cost
divided by Revenue times the Defect or Return Rate
minus the Total Variable Cost.
So all the cell references are pointing to the right area.
And we have the right number of digits.
Right?
So now if we look at our formula over here,
we were supposed to force all of the calculations
in the denominator to calculate before fixed cost
or before the division.
So it looks like we forgot those parentheses.
So sure enough, in the denominator open parentheses,
close parentheses.
The formula matches the formula we're supposed to use.
The cell references are looking good.
I'm crossing my fingers.
Control, Enter.
Sure enough, there it is, 2,662.
Now I want to show you how to use the F9 key.
F2.
And we're not really using it to track down an error.
But I want you to notice that the denominator here, this
is actually the net profit or contribution
margin after our defect or refund rate per unit
that we have to pay off fixed costs.
And I don't see it calculated anywhere in the spreadsheet.
So I'm going to use a trick.
I'm going to use the F9 key, which is Evaluate.
F9.
And it's great because it will evaluate
any part of the formula that you highlight.
Sure enough, now I know $6.55 is contribution margin,
or the net profit per unit, to help pay off fixed cost.
Now, if you F9, that means it's hard-coded in.
I don't want to hit Enter.
I'm going to Control-Z to undo.
When I hit Enter, there it is.
Now let me show you another cool aspect of the F9.
Notice that I hit F2.
And before I put it in Edit mode, it was correct.
Right?
So if I highlight to evaluate it and hit F9, I've looked at it.
I've noted that it's 6.55.
I can also hit Escape to revert back to what was in the cell
before I put it in Edit mode, so Escape.
F2 confirms that it actually did revert back to that formula.
Now, one last example of errors.
Let's look over here.
N/A. We were hoping this is a F2, a LOOKUP formula.
And, in fact, it is.
Index has an array.
Then it needs a row number and a column number
to do a two-way look-up.
So it's supposed to look up West here, find this row.
Then it's supposed to look up Majestic [INAUDIBLE] in this,
find the column, and then return that intersecting value.
Well, sure enough, it doesn't look like it's doing that.
So step one, F2.
Now I'm going to look.
It's got West for match, so that looks OK.
It's got Majestic [INAUDIBLE] for the second match.
So it looks like those cell references are right.
Oh, look at that.
If I go over to Match, Match has to look up West within the row
headers, which it's not got the correct range in order
to find the row number.
Right?
So that purple range is incorrect.
I'm simply going to point to the edge and click and drag.
And now that's correct.
So it looks like for the row number match
I have the right cell reference and range to look up.
For column number, it looks like I got the right green one.
Man, I tell you what.
I love this Range Finder.
That color coding really helps here.
The purple one, L1 to T1, it's hard to see,
but there it is up there.
So all the cell references are looking right.
The zero for exact match look-up is correct, too.
Let's look right here, the array.
Sure enough, ah, look at that.
I didn't include the entire range.
So I'm going to do that trick.
I'm simply going to point to the edge.
I see my diagonal cursor, click and drag.
Maybe that's going to work.
Remember, I looked at the formula.
I looked at the cell references.
So when I hit Enter, I get the-- no.
I get an N/A.
Now let's use Evaluate Formula because if I F2,
I don't know which part of the formula's causing the N/A.
So Alt, M, V. And now I'm going to look up here.
Hitting Enter instead of using my mouse, I'm going to look.
Enter.
So it's got match.
Enter.
Ah, it's giving us a one, which is correct.
West is in the first row.
Enter.
It looks like there might be a problem there
with too many spaces.
When I hit Enter, sure enough.
Formula Evaluator or Evaluate Formula
has verified it's the second match that's
causing the N/A. So Escape.
F2.
I could actually come here, click on Call Number, and F9.
Sure enough, that's causing the problem.
Control-Z.
Now, I'm going to actually use F9 twice in a formula.
So I had to do that Control-Z to undo it.
I'm going to double-click this, and hit F9.
And check that out.
All I'm doing is looking at what's in that cell.
It's got some extra spaces.
Now I'm going to hit Escape to revert back.
Click in the cell for Product, and F2.
Sure enough, there's some spaces.
Now just for the kick of it, I'm going
to select that cell, and F2.
I don't see any extra spaces.
So now F2, Backspace, Backspace.
We should have a direct match between Majestic [INAUDIBLE]
and Majestic [INAUDIBLE] there, so there
shouldn't be an N/A. Enter.
Whew.
That's looking good.
Now I'm actually going to F2, click here, column number,
and F9, and there it is.
It looks like one, two, three, four, five, six, seven,
it's the ninth column.
So our formula looks like it's working.
Not only that, but I can see it's returning the right value.
I'm going to test the formula.
I'm going to look up the East Majestic [INAUDIBLE].
So it should be returning 11,512 East.
And sure enough, it is working.
Now, the only thing I did not show you
is up in Formula Auditing, Trace Precedents.
Watch what happens.
I have the cell.
I click Trace Precedents.
And instead of Range Finder with rainbow,
it just has a dot, which means that's the start of the range.
And in this case, it's the cells that are all
being used in that formula.
F2 to put it in Edit mode.
And you can see, sure enough, it's a different way using
arrows than using Range Finder.
Escape.
Remove Arrows.
And I click, and they're gone.
Wow, so in this video we saw a lot
about finding errors, tracking them down, and fixing them.
All right.
We'll see you next video.