Welcome to Excel and Business Math video number 19.
Hey, in this video, we got to talk
about how to round a percent using
the round function and our knowledge of facade number
formatting.
Now, here are the topics we're going to cover.
And rounding a percent is one of those tasks in Excel
that trips a lot of us up.
And the reason that it trips us up
is because we're often given percentages.
And it's communicated to us how to round
in terms of the percentage.
So someone might ask, please round to the percentage point
position.
Well, the problem with that is, you
can't rely on that position with the round function.
You have to actually look underneath at
the non-formatted number to pick the right position to round to.
So we'll see in this video how to round
to a percent, a tenth of a percent,
a hundredth of a percent.
And we'll get to see for the first time
a change in value formula and a rate of change
formula in a stock example, where we have to figure out
how much the stock changed in value
and then figure out what the rate of change
or rate of return is while rounding to the nearest
hundredth of a percent.
Now, we want to start off by going to the sheet position
and remind ourselves of a PDF note we saw last video.
When we format a percent like this, this is 1.3%.
That position right there is really
the ones position when we're thinking about it
as a formatted percent, or the single percentage point
position, or you might hear it communicated round
to the nearest percent.
That position right there is the tenths position
for our formatted percent.
And we always have to remember the difference
between the number and the formatted percent.
Here it is, ones, tenths.
But really, when we remove the number formatting,
the number underneath, that's the hundredths,
and that's the thousandths.
That means, if we want to round to the ones position,
we have to remember, it's the hundredths and count
on our fingers for round 1, 2.
We'd have to put a 2 if we want to round to the ones
position, or the percentage point position,
or round to the nearest percent.
If we wanted to round to the tenths of a percent,
we'd have to think about the underlying thousandth position
or count on your fingers 1, 2, 3, and put a 3 into round.
Now, let's go over to percentage point.
We are given a formatted percent like this.
The absolute key is to remember what is underneath.
If you want, off to the side while you're
learning how to do this, please select the number.
Go up to number.
Click the dropdown and apply our eraser for number formatting.
In fact, you could do this off to the side.
So once you see, oh, yeah, I need to round to that 5,
then you can see the same thing but underneath.
There is the decimal.
1, 2, so we'd have to round to the 2 using the round function.
Again, the problem comes with the language
that's communicated to us.
Round to the nearest percentage point, or round
to the nearest percent.
The percent looks like that.
If that's the position we want to round to,
you always got to look underneath.
Now remember, number formatting is what we see on the surface.
It's the facade.
Formulas, like the round function,
do not see the number formatting.
Formulas see the actual number underneath the number
formatting.
So if I want to round to the nearest percentage point
or nearest percent, equals--
well, I got to use round--
and for the number, I'm going to select the cell
with my percentage, comma, and I'm rounding to the percentage
point.
So I look at the underlying number 1, 2.
I type a 2, close parentheses, Control-Enter.
And that's the correct way to round to the percentage point.
Let's go over to tenth of a percent.
Here, same thing-- don't get tricked.
Click in the cell, or off to the side, or something.
Apply General-- always look at the underlined number.
In this case, someone communicates
round to the nearest tenth of a percentage point,
or round to the nearest tenth of a percent.
There is the decimal for the formatted version.
That's the tenths position.
But remember, that's the surface,
so we have to look underneath.
Counting 1, 2, 3--
oh, yes.
That's the position right there.
This is what we see.
Formulas will not see that.
Formulas see the underlying number.
So when I come down here, and it's
communicated to us round to the nearest tenth of a percentage
point, or nearest tenth of a percent, no problem--
equals round.
And for the number, I click on the cell
with the percentage, comma--
I'm counting 1, 2, 3.
There it is.
So I have to put a 3 when I'm going
to the tenth of a percent.
Close parentheses and Enter.
And that is properly rounded to the tenth of a percent.
Now, those are two examples.
Let's go look at a great real world example,
I'm going to click on the sheet Stock Gain.
Now, here are some stocks.
And stocks are just companies like Google, Microsoft, Amazon.
You can actually go out and buy a stock, which
is one share of ownership.
Now, these examples are straight from Yahoo Finance
with real numbers--
the actual stock value one month ago, and the stock value today.
But I want to start off with two made-up stock examples
because we want to learn about a change in value formula
and a rate of change formula.
Then, we'll see how to round to a hundredth of a percent.
Now, when we say change in value, that means stock ABC--
on 12/29/2017, we bought the stock, one share for $100.
And then, the stock market on 1/29/2018 value--
that same stock that I just paid a $100 for one month ago--
its valued today at $110.
Now, what we want to do is figure out how much the stock
value changed.
Now sometimes, you'll hear it communicated as change
in value or amount of change.
Well, any time we're calculating change in value,
it doesn't matter if the change is up or down,
positive or negative.
You always take whatever the end amount is
and subtract the begin amount.
So let's do that--
equals left arrow, that's the ending amount.
And from, that I'm going to subtract
left arrow the begin amount.
Now, I'm going to hit the Tab key.
Now, I picked a simple example for our first example.
We can do this one in our head.
It went up by $10.
Now, we want to calculate something
called rate of change, or percent of change,
or percent change.
Also because this is a stock example,
finance refers to this as rate of return.
Now, rate of change just means however much
it changed compared to where it started.
So you're ready?
We're going to do this making a fraction or using division.
The change in value divided by the begin amount.
Now, when I Control-Enter, what that tells us is for every $1,
our stock increased by $0.10.
If we go ahead and format this, and I'm
going to use that number group dropdown, percentage
with two decimals, click.
Now, it's communicating how many parts out of 100.
That means it increased the rate of change--
percent change or rate of return--
it increased 10 parts for every 100 parts.
Now, we're talking money here, right?
So that means we got 10 pennies for every 100 pennies.
That's a pretty good return.
Now, let's go ahead and do our change in value for stock DEF--
started at 100.
We bought it on 12/29.
Oh, and the stock market values it at $90.
It went down.
Well, the great thing about a problem
where the change in value is an increase
or when the change in value is a decrease, the formula
is exactly the same.
Equals sign, I'm going to left arrow,
it's always the end amount.
And we subtract where we started or the begin amount.
When I hit Tab, well we could have done that one in our head
also--
down by $10.
Now, our formula for rate of change or percent change,
it's the same--
equals whatever the change in value is
doesn't matter if it's plus or minus.
Divide it by where we started.
So I got change in value divided by begin, Control-Enter.
Ooh, that's a minus.
We don't like that.
We'd much rather have our investments go up.
But that happens sometimes.
What does it mean?
We lost 10 pennies for every $1 that we invested.
Now, how did I know that it was 10 pennies down for every $1
invested?
Well, let's remember back to ratios in video 12.
If I take our formula--
change in value divided by beginner stock value,
either for the increase or decrease example--
if I keep the units, 10 divided by 100,
well, I'm left with 10 pennies for every $1.
That means, 10 pennies up in value for every $1 originally
invested.
For the decreased problem, oh, minus 10 divided by 100.
But if I keep the units and the 1 in that denominator,
minus 10 pennies down for every $1--
or more specifically, minus 10 pennies down
in value for every $1 originally invested.
Keep the units and the 1 in the denominator
will always help reveal the meaning
of whatever division, or ratio, or rate that's you
are calculating.
Now, we can format this as a percent.
Click the dropdown two decimals.
And now, it tells us that we lost 10 parts for every 100
parts we put in.
The rate of change or percent of change minus 10%.
This one was positive 10%.
Now, we can calculate the rest.
Real numbers, I went out to Yahoo Finance Google,
Microsoft, Amazon, GE, Target and Caterpillar.
The formula for change in value or amount of change
equals always the end amount minus the begin amount,
Control-Enter.
We like that.
Double-click and send it down.
Wow.
Over this one month period, that's a lot of hefty changes.
The only one that went down is GE.
Now, we can calculate our rate of change or percent change.
Now, the nice thing about rate of change or percent of change
is that these are dollar amounts,
and it's hard to compare.
But once we convert it to a rate of change,
or percentage of change, or even a rate of return--
all synonyms for the same thing--
it'll tell us how many pennies for every $1.
Then, it's easy to compare amongst all
of these which one got us the most
pennies for every $1 invested.
So you're ready?
Equals rate of change is always change
in value divided by where we started, or our begin amount.
Control-Enter-- wow, that's a lot of decimals there.
Double-click and send it down.
Even just looking at this, this rate of change,
we can definitely see about 12 pennies for every $1.
About 9 or 10 depending on where we round it.
Wow, look at Amazon--
21 pennies.
Now, let's highlight the entire column.
I want to add percentage number formatting.
But I'm going to use Control-1.
Percentage and increase, let's say five decimals.
Click OK.
Now, we are expressing parts for every 100.
About 21 parts, about 12 parts for every 100, and we
know, of course, that 100 parts we're talking about
are pennies.
Now, our goal is to round this to the nearest
hundredth of a percent.
All right, so I'm going to look at this one right here.
That 2 right there, that's the percentage point position
or percent position.
The 3, that's the tenths.
The 4, that's the hundredths.
So that's the position we want to round to.
Now, if you're not sure, here's a trick.
Come over to the side.
Equals sign, I'm going to click on that cell, Control-Enter.
It pulls the number formatting, but no problem,
I can wipe it away with the general.
Double-click and send it down will not
work because there is nothing to the left, to the right,
or below.
So I'm going to have to drag this one.
Now, I'm picking this one.
I've already decided I need the 4 right there.
So I'm counting on my fingers.
1, 2, 3, 4--
without the formatting, it's easy to pick
the correct position to use and round.
So now, I come up to the top--
F2.
Now, I round after the equal sign,
as we've seen so many times in this class.
Number, that's almost always some formula.
Come to the end, comma, number of digits, we type a 4.
Close parentheses, Control-Enter, double-click
and send it down.
And that is beautiful.
We have 12.35%, 9.8, 21.22%.
Now, I want to come to the last cell and hit F2,
make sure those cell references are looking good.
Actually, I didn't do that over here either.
F2, that change in value formula is looking good.
Now, when we look through this, normally we
would decrease the decimals, right?
I'm going to highlight all of these.
It was convenient for us as we're
learning to show many decimals so we can see
that the round function work.
And of course, we could increase or decrease as we see fit.
I'm going to leave it like that.
Now, look at this.
We created rate of change, percent of change,
or rate of return--
number of pennies.
Well, even though Target only gave us
11.55 total amount of change for every $1, we got 17.7 pennies.
But of course, Amazon was the one
with the most pennies per $1.
Right now, there are some homework problems over here,
four awesome homework problems.
And in this video, we definitely saw how to round percentages.
We saw how to round to the hundredth of a percent.
On this sheet, we saw a great stock market example.
And we learned change in value and rate of change formula.
We'll see lots more of that throughout the next couple
of weeks.
[? Back around ?] tenths of a percent,
of course we saw how to round to a tenth of a percent,
making sure to use three and round.
Over here on percentage point, if you're
running to the percentage point, be sure to use two and round.
And of course, if you like that video,
be sure to click that Thumbs-Up, leave a comment,
and sub, because there's always lots more videos
to come from Excel is Fun, including
next video, video number 20.
We'll talk about the famous percent formula.
All right, we'll see you next video.