Welcome to Excel and Business
Math video number five. In this video, we've got
to talk about the ever important "number
formatting as a facade." And we've got to talk
about the Round function. Now our topics-- we'll
talk about number formatting as a facade. We'll see that formulas do
not "see" number formatting. We'll see how to round
numbers for the first time. We'll talk about Excel's
built-in Round function that will make rounding
numbers easy for us. And then we'll apply
the Round function to the examples we saw
in video number three. Now let's go over to the sheet
NF1, for Number Formatting One. We need to ask the question,
what does number formatting do? And it's very simple. Number formatting
allows you to change how the number is
displayed without actually changing the underlying
number that sits in the cell. So we've seen this before. If I select F2, here's the
Number formatting group. We'll be able to,
throughout the class, use all the features here. But the one we want
to use right now is the Decrease Decimal button. Now before I click that, we
can see the number 35,060. And there's a decimal
and then 7056. We can see up in the Formula
Bar the same exact number. So right now, the number we see
on the surface of our worksheet matches what's actually
underneath in the cell. If our goal is to display the
number with fewer decimals, we can do that using
Decrease Decimal button. So watch what happens. I'm going to click
it once, and twice. We can already see
the decimal part. It says 0.71. But it did not change
the underlying number. We are just displaying
it differently than the underlying number. We can decrease the decimals
further if I click it twice. Once, and twice. Now the number we see on
the surface of the worksheet is 35,061. The number underneath
has not changed. Now sometimes that's
what you want to do. You just want to
display it so that it doesn't show any decimals. I always refer to what we see
on the surface of the worksheet as number formatting
as a facade. Now "facade" is a word that
comes from architecture. We see on the outside
of the building all the decorative
design, but we have no idea what's on the inside. A facade can be defined as
an outward appearance that is different than what
is underneath or inside. For us, for this example right
here, the facade shows 35,061, but underneath, the number
is still 35,060.7056. So the first thing that
number formatting does is it can change how the number
is displayed without actually changing the underlying
number that sits in the cell. Now let's go look
at the second thing that number formatting does. I'm going to go to NF2. Here's price of
100, quantity of 2. I don't need a
formula to do that. 2 times 100 is 200. But let's make our formula. Equal Sign, Left
Arrow, Left Arrow. 100 Times Left Arrow 2. When I hit Enter,
of course I get-- 200? That's not 200. That's 199. If I hit F2, guess what? Formulas are not going to
make math errors in Excel. When I hit Enter, as
soon as I see 199, I know there's something
misleading about this. And it probably has to do
with number formatting. Any time you get
a formula result that doesn't match what
you see on the surface of your worksheet, you have
to suspect number formatting. If you click in the price,
look up to the Formula Bar. There is the culprit. 99.5 is the actual
number in the cell, whereas we're
displaying it as 100. So that's misleading. Let's not leave our
Excel solution like this. With cell A2 selected, I'm
going to increase the decimals. Now 99.5 times 2. That makes sense. 199 is the answer. What we want to learn for
our second point about number formatting is formulas do
not "see" number formatting. Formulas make calculations
on the underlying number that sits in the cell. Formulas do not
make calculations on the number
formatting that you see on the surface of the cell. All right, so number one,
number formatting is a facade. Number two, formulas don't
"see" number formatting. Let's go see another important
point about number formatting. And I clicked on NF3. Number formatting can save us
a lot of time with data entry. So if our goal is to
enter these numbers into this column over here, this
is what we do not want to do. I'm looking at dollar sign,
45, point, zero, zero. I just typed in six characters. When I hit Enter,
yes, that would work. But if I entered the entire
column using that method, I would have to do
too much typing. Here's a much better way. And I'll move this over here. If we just enter the numbers and
the minimal amount of decimals and then apply
number formatting, we can save a lot of
time with data entry. Now I'm going to use the
number pad and the Enter key on the number pad. So I'm going to enter 45, Enter. 65.2, Enter. 78.99, Enter. 100, Enter. 101, Enter. 98.2, Enter. 20 and Enter. The first thing
is the number pad allows us to enter
those numbers quickly. The second thing is I
use my selection cursor, I highlight the
range, and now I'm going to go up to the
Number group dropdown. And I'm going to apply currency. When I do that, instantly I have
a consistent dollar unit number formatting that shows us dollar
signs and the proper decimals for our money amounts. Now when I click
in this cell right here and look up
in the Formula Bar, I can clearly see
there's only a 100. That dollar sign,
the decimal, zero, zero-- that's all the facade. That's the number formatting. So number formatting
is a facade. Formulas do not "see"
number formatting. And number formatting
can save us a lot of time with data entry. Now before we go on
to point number four, I want to take my
selection cursor and highlight this entire range. And up in our Number
Formatting dropdown, we can see that
currency is applied. If I click in a cell that does
not have number formatting, I see the general
number formatting. That means there is
no number formatting. Anything we type will
be visible in the cell. Now let's go look at NF4
for our fourth point. We want to learn the difference
between general, currency and accounting. Now really, we just want to see
the difference between currency and accounting, and
then we're going to see how to use General
number formatting as an eraser to remove number formatting. Now let's start with currency. I'm going to select the range of
my selection cursor D4 to D10. Go up to the Number
group, the dropdown. And these are our built-in
standard number formattings. I'm going to select currency. Now let's do the same
thing for the accounting. Highlight the range. Drop down. And let's select accounting. So we can see three
main differences. The first one is the
dollar sign for currency is always floating next
to the leftmost number. It goes in and out,
whereas accounting always lines that dollar
sign up perfectly, fixed on the outside. Zeros-- currency displays
it as an actual zero, accounting as a dash. That comes from zeroing out
the accounts in accounting. Negative numbers-- we can see
a negative sign for currency. And parentheses--
that's how negatives are shown in the
profession of accounting. Now let's take a closer
look at currency. I'm going to
highlight this range. And the dropdown provides
one option for currency. But we can change
some of the settings. For example, how to
show a negative number. I'm going to click Escape. The keyboard to open up
the Format Cells dialog box is Control-1. Now throughout the
class, we'll get to see all of the
different tabs. But for right now,
the Number tab offers us a number
of categories, things like accounting. Later we'll see date,
time, and percentage. We're going to select currency. And if we want to change how the
negative number is displayed, we can do that. We can change the symbol. We can also change how
many decimals to display. Now I'm going to change the
negative to show it as red when it's a negative number. Click OK. And just like that,
that indicates that that is a negative number. Now accounting and currency
will always line the decimals up perfectly. Currency has one
strange behavior. Since you're allowed to change
how negative numbers are displayed, if you happen to
change the settings for one of the numbers,
but not the others, then the decimals
will not line up. So I'm going to Control-1
and select Parentheses. When I click OK , look at that. The decimals are not lined up. Now I'm going to Control-Z to
remove that number formatting. Control-Z. Now we got to talk
about general. And general is really
helpful because if we want to remove
number formatting, I can highlight
all of these cells and simply go up and apply
the General number formatting. And instantly, it's like
an eraser for any number formatting applied. So if I click General, instantly
all of that number formatting is removed. Now I'm going to
Control-Z. We will use that trick
throughout the class, especially later
in the class when we learn about Percentage, Date,
and Time number formatting. All right, so number
formatting is a facade. Formulas do not "see"
number formatting. Number formatting can definitely
help us with data entry. And we have a bunch of built-in
number formatting options like Accounting and Currency. Now in that list of four
important number formatting topics, number two is
the most important. Back on the sheet NF2, we
saw how we got in trouble because formulas cannot
"see" number formatting. Now we need to switch gears. I'm going to actually
use my Sheet Scroll arrow to scroll the sheets. And I want to click on
the sheet Round Two. Now what we want to do is
a simple payroll example-- gross pay, gross pay
for each employee. There's our tax rate. So we're going to
build a formula, copy it down, and then notice
that we get into trouble with number formatting. All right, so we're going
to build our formula. Equals, Left Arrow, to get
my relative cell reference. That's the employee
gross pay, Times-- and I'm going to click
on the tax rate cell B19. And I need it locked as I copy
down, so I hit the F4 key. Now I'm going to Control-Enter. Point to my fill handle
and click and drag, just down three cells. Go to the last cell and hit F2. All right, so the cell
references are looking correct. Now let's click in
the bottom cell, and we need a total for
all the tax deductions. So we use our keyboard. Alt-Equals. We learned that last video. That gets us the Sum function. We see that the range is
correct, so I hit Enter. Now if you were at your job
and your boss happened to be walking by-- and she was
really good with math-- she would quickly do this
in her head and go, no way. That is not equal to 163.89. It's 163.90. And imagine that she
grabs your mouse. And she sees that it looks like
there's 152.73 in the cell. So she types 52.73, Enter. 58.58, Enter. We're still typing what we see. 52.59, and Enter. The boss was impressed
that you knew Alt-Equals. So Alt-Equals. And Enter. And she goes, there it is. The total's actually 163.90. So what is going on here? Well, let's click in the
last cell and hit F2. Remember, formulas do not
"see" number formatting. So I'm going to hit Enter. Highlight this whole
range, and we're going to use our General
number formatting trick for the first time. And click the dropdown for
your number formatting. And there's General. General is our eraser. When I click General, it erases
all of the number formatting. And there we can
see the problem. It looked like there
was a 73 in that cell. But really it was 7, 2, 7, 6, 2,
5-- a bunch of extra decimals. And the Sum function was looking
at that unrounded number, not the formatted 73 pennies. So all of these extra
decimals are being added in the Sum function. And we don't want that. Why? Because this is money. So many of our business
examples in this class will be dealing with money. Guess what? There's no partial pennies. So we're actually
required to round. In a payroll example like
this, or an invoice example, we have to officially
round these to correct penny amounts before
we can use the Sum function. Now before we see the
Round function, which will make rounding
easy for us, we need to see how to do this
the long way, by hand. Now I want to go over
to the sheet Round One. Now here's our official standard
rounding rule that we all learned in school-- in
grammar school, middle school, high school-- and we're seeing again here. Now this isn't the
only way to round, but this is the standard
method for rounding when we do things like
invoicing, payroll, or shopping at a store. So the standard rounding
rule goes like this. Here's our number. This is the same number
we were just looking at. Step one is pick the position
you want to round to. So since we're
dealing with pennies, we have to pick the position. So here I've
highlighted it in blue. Now when we get to
the Round function, this is the only step
we're going to have to do. We have to count from the
decimal and move to the right-- 1, 2. So because that's the second
position for the Round function, we just tell it to
round to the second position. And it will automatically do it. But the by hand method,
that's the first thing we do because then we
go to the next digit directly after that position. So I've highlighted that in red. Once we see what
that is, we have to go to step two
and either decide, is it five or bigger
or four or less? Because seven is
five or bigger, we add one to the position
you are rounding to, and remove the unwanted digits. So here, that two is the
position we're rounding to. So we add one, remove
the extra digits, and we're left with $52.73. Now-- slightly different number. We're still going to round
to the second position. That's the blue two. We immediately
look to the right. That is a four, so we
apply our second step. Four or less, just
remove unwanted digits. That means we don't add
anything to the position we want to round to, we
simply remove the digits. And we're left with 52.72. So that's how to round
the longhand way, by hand. But now let's see how
to do it in Excel. I'm going to take that
same first transaction for gross pay, times tax rate. Equal Sign, Left Arrow to get
gross pay, times Left Arrow, Left Arrow to get tax rate. Now I'm going to use the Tab key
to put the formula in the cell and move to the right. I'm going to leave that cell
with no number formatting. Now I'm going to create
the same formula. Equal Sign, Left Arrow
to get gross pay, times Left Arrow
to get tax rate. But now, Control-Enter to
put the formula in the cell. Keep the cell selected. And now I want to apply
number formatting. So I'll put the Number
group-- dropdown, Currency. Now Currency number formatting
does not remove those decimals. It just displays two decimals
on the surface of our worksheet. Now let's see how to
use the Round function. The Round function will
officially remove the decimals. Equal Sign, and I'm going
to do the same formula. Left Arrow to get
gross pay, times Left Arrow to get my tax rate. Control-Enter. We can see all the
decimals, but now we need to have the Round function
do the rounding for us. F2 to put it in Edit mode. And this is the first
time we've seen the Round. We're almost always going
to have some formula, like multiplying,
dividing, decimals. And we have extraneous decimals. Instead of rounding by
hand, we click directly after the Equal Sign and
before the formula and type the word "round." Now all I'm going
to do is type R-O-U. As soon as I see the
built-in function Round highlighted in blue,
I can use the Tab key. Now notice there are other
types of rounding functions. The one we're going to use in
this class for our standard rounding is Round . So I hit Tab. Now as soon as I hit Tab,
I could see my screen tip. And there's two arguments for
the Round function-- number, and number of digits. The number argument is always
going to contain our formula. You very carefully come to the
end with your I-beam cursor and click at the end of our cell
references and math operators. And then we look
to the screen tip. And just as we saw last
video for the first time, if we type a comma, we can
get to the next argument in our function. So I'm going to type Comma . Notice, highlighted in bold,
it says "number of digits." When the argument is
highlighted in bold, that's asking you
to please enter the item for this argument. Now number of digits. That's what position
you want to round to. Now for us, we're always
counting from the decimal. If we're going to the right, we
say 1, 2, 3, 4, 5, and so on. If we're going the
other direction, like we will for dollars-- since to the penny
is 1, 2, going this direction would be 2, 1. To the dollar would be zero. Later in the class,
we'll see if this is penny 2, that would be 2, 1, 0. Minus 1, minus 2, minus 3, would
be to the thousands position. Now here is a little
reminder list down here. It's also in your PDF notes. In this video, we'll
just round to the penny. Later in the class, we'll
round to different positions. All right, so for
number of digits, we're rounding the
penny, so we type a "2". That tells the Round function
to round to the penny. Close Parentheses. Control-Enter. Notice we don't have any
number formatting applied. It's the General. So it always shows you the
number, the actual number. I can prove to myself that
the decimals have been removed by increasing the decimals. Sure enough, the Round
function did exactly what it's supposed to--
rounded to the penny, removed all the extra digits. Now I'm going to use
decrease decimal. All right, that's
the Round function. Let's go see how to do it
on the payroll problem. I'm going to click on Round Two. Now I'm going to
reapply currency because we want to leave that
as a trail of our mistakes. So I'm going to apply currency. We're going to do the
same base formula. Equals, Left Arrow, to
get gross pay, times-- I'm going to click
on my tax rate. I need to lock it,
so I use the F4 key. Before I round or add
number formatting, I'm going to Control-Enter. Whoops, I already have
number formatting. I want to prove to
myself that there's a bunch of extraneous
decimals, so I'm going to use my eraser
for number formatting-- the general number
formatting option. I'm going to copy this down. And now I see that there
are extraneous decimals. Because I'm going
to add all of these and we're dealing with money, I
have to officially round these. So now I come to the
top cell, hit F2. Then right after the Equal
Sign, I type "round." I see it highlighted in blue. I hit Tab. The number argument-- that
contains my multiplication. Click at the end with
my I-beam cursor. I have to type a comma,
number of digits. That's what position do
you want to round to? I'm typing a "2" because we
need around to the penny. Close Parentheses. Control-Enter. Look at that. I love that. Copy it down. Now I can come to
the bottom cell. Alt-Equals to get
my Sum function. And Enter. And now I have correctly
calculated the tax deductions, rounded them, and then added
them in a subsequent formula. Now, I can apply my
number formatting. Up here, dropdown, Currency. And there's our correct
calculations using Round. These are not correct
because we didn't use Round. Now when must you use
the Round function? Three conditions--
when they're all true, then you have to use
the Round function. First, if you're required to
round, like we are with money; second, you have
extraneous decimals, like past the penny
position; and third, you will use the formula result
in a subsequent formula. Where we got into
trouble down here is when we made
this calculation. Because we're going to end up
using it in another formula, and we have money, and we
have extraneous decimals, we were required to round. Now we'll get lots of practice
in this class applying these three tests to see if we
need to use the Round function. And on tests, you'll be tested
to see if you can figure out when to use the Round. When to not use Round? When you're just
looking at a number. Then you can just use
number formatting. Rules for that second argument. Well, "2" is to the penny,
"0" is to the dollar, "-3" is to the thousands position. We'll see these two
examples later in the class. Examples when we often
have to round our numbers and use the Round function? Payroll invoices
and income taxes. Now before we leave
this video, we have to go look on
the sheet Video Three at two examples we did
back in video number three. We calculated the
insurance expense discount. We had our items
we were insuring. Here was the annual expense. Here's the discount we got. Now back in video
number three, we had a discount of 10%, which
didn't cause us trouble. But if we get a discount
like this, which is 0.0275-- later we'll see that that's
equivalent to 2.75%-- then we run into trouble. So let's make our formula. Equals insurance expense as
a relative cell reference, times-- and I'm going to
get my discount-- F4 to lock it. Look at that. We "F4"-ed on the F4 key. Control-Enter. And copy it down. Go to the last cell, hit F2. We're verifying the
cell references. Now we can add
using our keyboard. Alt-Equals, and Enter. Now let's see if we can
apply our three rules. Do we really need to
use the Round function? First, are we required to round? Yes, because we're
dealing with money. Second, do we have
extraneous decimals? Well, I didn't check
because there was number formatting disguising this. But for a second, I'm
not going to touch that. I already know there's
potentially trouble over here because if I had 0.02,
that's 2 pennies. But I have something
past the penny position. So I already know I'm in trouble
for the second condition. The third condition, did
we use all these formula results in another formula? Yes we did. Now let's not even bother about
removing the number formatting. Let's just create the correct
formula, since all three conditions are met. Equals. And I'm going to start off
by using the Round function. Now, round. Number-- that's where
we put our formula. This would be the first time
we entered the Round first. And then right inside the
Round function number argument, we'll build our
multiplication formula. It's no problem. Arrow, Arrow to
get relative cell reference for our annual
insurance expense. Times, Arrow, Arrow. And I'm going to lock
it with the F4 key. I love that. "F4" on F4. So we built our multiplication
formula right inside Round because all three tests for
requiring Round were met. Now I'm going to type a comma. I'm counting on my fingers. There's the decimal. 1, 2. That's the position I'm rounding
to, so I simply type a "2". That "2" tells Round
to round to the penny. Close Parentheses. Control-Enter. Copy it down. Look at that. If I visually check everything,
they're all looking the same, but now when I come down here
and use my keyboard Alt-Equals and Enter, I can clearly see
the Round function, properly rounded. So that means the Sum
function can properly add all of the
correct penny amounts. Now let's prove
this to ourselves. We're going to use our General
number formatting trick. This is not a trick. This is just our
ability to verify that everything we're doing
is correct or incorrect. So I'm going to click
the Dropdown and General. We can see all the
extraneous decimals. So the sum is adding all
those extraneous decimals. Here it's adding
real penny amounts. Now I'm going to Control-Z
to undo that because I want to leave it as a trail. This is not correct. This is correct. Let's look at our second
example from video number three. In that example, we calculated
a deduction for each employee. Here's the taxable
pay for each employee. There's our tax rate. That tax rate is
actually accurate. That's the 0.0765. Later in our payroll
chapter, we'll learn that that's
the FICA tax rate. All right, so you ready? Equals, Left Arrow, times-- and I'm going to arrow
over to get my tax rate. F4 to lock it. Control-Enter. And copy it down. Click in the last cell and F2. The cell references
are looking good. Now I click on the last cell. Alt-Equals to add. I know I'm going to get a
potentially wrong answer. Sometimes it comes out
correct, but it's not because you built the
spreadsheet smartly or correctly. Now we're going to build
it the correct way. We're going to use
our Round function. In the number
argument, we're totally allowed to create our formula. We get taxable pay times-- there's our tax rate. F4 to lock it. I'm looking at the screen tip. I know I have to type a comma. Comma. Number of digits. I'm counting 1, 2. 2 is for the penny,
so I type a "2". That tells Round to round that
amount officially to the penny. Close Parentheses. Control-Enter. Copy it down. I'm going to click on
the last cell and F2. Cell references looking good. Alt-Equals and Enter. Looks like I was
only a penny off. Now the question for you
is, well, wait a second. It's only one penny. Does it really matter? Yes, it does. Especially if you're the
one creating the spreadsheet for payroll or invoicing. That means your employee
or your customer, once they see that
you're a penny off, they don't trust your
calculations anymore. So we use the Round
function for this example. Why? Because we're required to round. This is money. We had extraneous
decimals, and we were using all of
these formula results in a subsequent formula. All right, that was a lot of
fun with number formatting and the Round function. Right at the end, there
are two homework problems you can practice. And what do we do in this video? We saw two examples
from video number three where we applied the three
rules for when to round. Over on Round Two, we saw
how and when to round. Round One, we saw
how to round by hand. Over on NF4, we talked about
the difference between Currency and Accounting number format. And the ever important
General number format, which will be our eraser
when we need to see what's really underneath, below
the number formatting. NF3, we talked about how
number formatting can help us with data entry. NF2, we talked about
how formulas do not "see" number formatting. And on NF1, we talked about the
essence of number formatting. It changes how the
number is displayed. All right, if you
like that video, be sure to click that thumbs up. Leave a comment and so
because there's always lots more videos
to come from Excel is Fun, including video number
six in this class, where we'll have a comprehensive
formula video that will show us all the
different elements that can go into formulas. All right, we'll
see you next video.
