Welcome to Office 2013, class video number 40.
Hey, if you want to download this workbook,
video 40, Excel Basics 22, click on the link below the video
or go to our class website.
Hey, we're still studying Excel.
Excel Basics 22.
That's this video.
And we want to talk about Excel's golden rule.
Now, wait a second.
We've already talked all about Excel's golden rule,
all the way back in Excel Basics number two.
Ah, but I'm going to have you do a homework
problem from the textbook, and in the textbook,
they ask you to violate this golden rule.
And I'm going to show you in this video
how I want you to do this homework problem differently.
Now here's Excel's golden rule.
If a formula input can change, put it in a cell,
and refer to it with a cell reference.
If it will not change, you can type it into the formula.
So we've had a number of examples
in this class of when we can type
formula inputs into a formula.
So for example, we have yearly expense and we need monthly.
You simply take the total year expense and divide by 12,
because 12 months in the year is a formula input that's
not going to change any time soon.
Similarly, we did formulas for calculating hours worked.
In parentheses, we took the later time minus the earlier
time, and since that's the proportion of a 24 hour day,
we have to multiply it by 24.
That's another example of a formula input
that's not going to change.
24 hours in a day, not going to change any time soon.
Here's what they're going to ask you to do in the textbook.
It's a great problem.
You have to do this whole problem
to calculate employees' payroll.
Federal tax cell.
F2.
Oh, my heavens.
This is going to be the exemption amount and the tax
rate.
So instead of putting them into an assumption table, like we
did down here, they're going to ask you to type them in.
Forget it.
Don't do that.
Put these numbers into an assumption table
and refer to them with a cell reference.
And we'll see how to do that in just a moment.
State tax rate, not in a formula.
That's an example of a formula input that can change.
Don't type it into a formula.
There's been some studies done over the years about number one
cause of spreadsheet errors, and it comes down
to people type in formula inputs that
can change into the formula.
One other thing.
You're going to also have to remember your rounding rule.
Here we're multiplying decimals, and so we should use the round.
And we've seen lots of examples, especially in the last video.
We saw, like, four examples where you get into trouble.
If you have a subsequent formula,
the sum function, and it's looking
at all these calculations, if they're not rounded properly
then this sum can show the wrong answer.
All right.
Let's go over the sheet.
Similar to homework Excel Project 2.
Here are our employees, dependents, hourly wage,
hours worked for the period, and we
want to calculate gross pay, federal tax, and state tax.
So gross pay.
That's simply going to be hours work
for the period times hourly wage.
We're going to use round.
equals, ROUND, hours worked, one cell to my left,
times wage per hour, two cells to my left, and then comma,
to the penny is two.
Now, this is payroll.
Anytime you have payroll or invoicing
and you're dealing with pennies, round to the two.
Sometimes for payroll, if you have a dollar round
requirement, you use a zero here.
Not here.
This is to the penny.
Control-Enter.
Now, I'm going to copy this down.
And it's going to copy the format and I don't want it,
so I'm going to point to my smart tag
and say, fill without formatting.
I love that smart tag.
All right, now federal tax.
Here's gross pay.
We're not taking gross pay times federal tax rate.
They'll actually subtract some amount
based on dependants and exemption.
The more dependants you have, the more total exemption
you'll get.
So our formula is going to be equals,
open parentheses, gross pay-- and that's a relative cell
reference, as we copy the formula down,
always looking one to my left.
And I need to subtract.
And now I need to subtract dependants
times exemption amount.
Dependants, relative cell reference.
One, two, three, four to my left.
Times our assumption table formula input.
The exemption amount, 25.15, sitting in F17.
Now, we're only copying this down.
We're not copying it to a rectangular range,
so I'm simply going to hit F4 and lock that cell reference.
Now close parentheses.
And now we can multiply times our federal tax rate.
F4 to lock it.
Now notice, we had to put this inside a parentheses
to force this multiplication first
and then that amount is subtracted properly
from the gross pay.
Control-Enter and copy it down.
Point to the smart tag.
Fill without formatting.
Ah, but we forgot something.
I'm going to come up.
Notice I have the whole column highlighted.
If you already have it highlighted,
then you need to edit the formula.
F2.
That's multiplying a bunch of decimals.
So we need to round.
Now, we have to be careful here.
Round.
A lot of times in this class, we have selected a function
and then hit Tab.
I want you to notice what'll happen here.
Because we already have a parentheses there that we need,
when I hit Tab, the round is, like, greedy.
It took it away.
So you have to be careful when you
have a parentheses sitting right there.
I'm actually going to have to type another one.
You can see the color coding is proper now,
the orange is on the inside.
Now I'm in a come to the end.
I could also use my END key, End, type a comma,
and then a 2, close parentheses.
Now I've edited this formula in the active cell
because I have highlighted the column already.
If I want to repopulate the correct formula,
I hold Control and Enter.
I can go to the last cell, and F2.
That is amazing.
Look at that.
It got exactly the gross pay and dependants, relative cell
references, and the two federal tax and exemption amounts
are absolute.
All right, our state tax.
This is multiplying decimals, so I'm going to use round.
And here I'm going to hit Tab.
There was no parentheses there, so no problem.
Gross pay, two cells to my left, times the state tax rate,
and I'm going to lock that with F4.
Comma 2 to round to the penny, close parentheses.
Control-Enter.
Copy it down, and fill without formatting.
Now, I want to point something out about fill
without formatting.
I'm going to Control-Z. Imagine this.
We had highlighted the column before we started our formula,
and then I hit F2 and the active cell gets put in to Edit mode.
Now we're just typing our formula.
If you do this, when you Control-Enter
to populate your formula, it actually
puts only the formula in.
It's not going to mess with any of the number
formatting or other formatting.
Control-Enter.
See that?
That time it didn't bring that dollar sign down.
All right.
I'm going to click in the last cell and F2.
So, that homework problem, you're
going to be sure to round, because you're multiplying
or dividing decimals and you're using the calculations
in a subsequent calculation.
And don't type numbers that have the potential
to change into formulas.
Simply put them in cells.
Refer to them with cell references.
All right, we'll see you next video.