Excel Finance Class 03: Math Operators and Order of Operations


Welcome to Excel and
Finance video number 3. Hey, if you want to download
this workbook and follow along, click on my YouTube channel,
then click on my college website link, and then you can
download the workbook Finance and Excel chapter 00. If you’re in the class, just
go to our class website. Hey, in video number 3, we want
to talk about math symbols, and then we want to talk
about the order of operations for calculating formulas
in math and Excel. Hey, we’re going to have
to use lots of parentheses in this finance class. Exponents, caret, which is Shift
6, multiply, which is asterisk, on the number pad–
most convenient– forward slash is
division, add, subtract. We may even have to use
comparative operators. We already use equals
sign to create a formula, because equals sign is the
first character in the cell. And that tells
Excel that you want to do a calculation
or a formula. But you can also
use an equals sign– which we’ll see in this video–
as a comparative operator. You can ask the
question, is this thing equal to this other thing? Here are some other
comparative operators. Important to notice that greater
than and equal to, less than and equal to, are two
characters next to each other. It’s not like in math. Not, which probably won’t
get to use in this class, is those two symbols. And join, which we probably
won’t get to use either. All right. Let’s see some math examples. Adding. We already know by now– this is
the third time we’ll see this– Alt equals. What does Alt that
plus equals sign do? It is sum. So don’t even chance it. Sometimes people want to go
like this for a small one. But I’m sorry,
that takes longer. And if for some reason you
did a structural update, like we saw in our last video,
that formula doesn’t work. Alt equals. And it guessed right. So now, last couple of videos
I’ve been hitting Enter. But if my goal is to create
formulas and go to the side, I do not put the formula
in with an Enter. I use Tab, the Tab key. Subtracting. I’m going to say equals. Now I’m going to
use my arrow keys. Remember we talked
about last video, you can use your mouse
or your arrow keys. I’m going to click Escape. Arrow keys are much faster
when you’re close in like this. So I hit up arrow
twice, and then minus, and then up arrow once. I’m going to hit Tab. So we get negative 3. 2 minus 5, negative 3. All right, dividing. Important thing about dividing. Equals up arrow, up arrow,
division, up arrow once. Notice this is
called the numerator, and this is called
the denominator. This is the top. This is the bottom. If the top, the numerator, is
bigger than the denominator, the number will always
be greater than 1. The opposite is true. If I make a formula where
the numerator is smaller than the denominator,
this number will always be less than 1. If they’re both the
same, then they’re 1. So that’s division. Multiplying. We could do this times– use the asterisk on the
number pad, and then get that. However, similar to
the sum function, if we have a bunch of
things we’re multiplying, you can use the product
function Whoops, product. Product. The word product means in
math, that’s the answer. The product– 6 times
6, the product is 36. So you can highlight
it like that. I don’t think we’ll get
much chance to use this. We’ll usually be using
the multiplication symbol. I hit Tab. Exponents. Now we’re going to talk about
the whole sheet over here about– we’re going to
talk about exponents. But just an exponent. If I take 6 squared or six with
an exponent of 2, equals 6, and you have to do Shift 6. That’s a caret symbol. That’s the exponent
symbol in Excel. And that means the base is
6, and the exponent is 2. That means take the 6, repeat it
two times with a multiplication symbol in between. So it’s 6 times 6. Again, we will have a bunch
more examples of exponents in just a moment. Now let’s look at a
comparative operator, because a few times
in this class, we are going to
have to make what are called logical formulas. They don’t give us
a number answer, or they don’t give us a word
answer like text formulas do, which we don’t do a lot
in this class either. But logical formulas,
occasionally we will do them. We’ll need to ask the question– make a formula. So we type in equals sign. Then we want to say, is this
equal to-_ the equals sign is not the first character. That tells Excel, I’m a formula. This is a comparative operator. So this formula says,
is this equal to this? And this is very
important sometimes, because we may
have discrepancies which we can’t detect
because it’s so far out in the decimal place. Now, a logical formula
or true false formula only has two results. It’s either true or false. Now, before we investigate
why those aren’t equal, let’s do another one. Equals, is the value in M3
greater than the value in M4? Well, it better say true. Yeah. Now, let’s go over here. Almost always– and
this is actually extremely common in Excel,
because number formatting can disguise the number. And we’ll talk about this
in a video coming up. But right now, let’s
just investigate this. Now, if you click
in the cells, you can look up in the
formula bars and tell that there is a difference. It means that somehow
that decimal is hiding. So on the surface
of the spreadsheet, our eyes see 10, 10, and
we immediately say ah, the accounts are in
balance or something, or the two dollar interest
amounts are the same. But what’s happening here? Well, in this video,
we’re just going to see how to
increase the decimals. Now, we added a
decrease the decimal. I’m going to come over to Home. In our first video, we
saw how to add buttons to the [INAUDIBLE]. Now notice what’s nice about
the quick access toolbar is if we use increase decrease
decimal or borders or font color all the time, it’s
convenient to add these. So I’m going to come over here. Oh, I can’t even see this. So I’m going to have to
come to the side and expand. Click the Home. And I’m going to right click
Add to Quick Access Toolbar. In fact, I think I’m
going to do a few more. I’m going to– well,
anyway, not now. For now, we’ve got those two. All right. Let’s go ahead and use it. Let’s increase decimal. And if you point your cursor,
and you can’t figure out which one is which,
the screen tip will come up,
increase, increase. And then we can see in
fact what was happening is, the number
formatting– this is called number formatting–
was disguising our numbers. Guess what? And this is one of the
most common mistakes in Excel on the planet earth. People think that what
they see is actually there, and it’s not. And that’s why this formula is
so good, because it’ll never get tricked by formatting. It never looks at the
formatting numbers. Formulas don’t look
at the number format. They look at the
underlying number. And that’s why that formula
told us there’s a problem. So if it was a
problem, we would now know that they’re not equal. If they were interest
amounts, and we’re saying, OK, this is the greater
interest amount– if it was a mistake, then
you’d go ahead and fix it. Like this was actually
a 0 or something. And then it would
immediately change to true. I’m going to Control Z, because
those are in fact different. All right. We’ve got to go look
at order of precedence. Now, Please Excuse
My Dear Aunt Sally. In finance, we have
big, huge formulas that we’re going to do. A lot of times
we’ll have functions like PMT FVNPT and rate and
all these great built-in Excel functions that
will do the big formula calculations for us. However, even though
there’s some cool functions, there’s still some big formulas
we’re going to have to create. And if we don’t know our order
of operations– or in Excel there’s something called
order of precedence, which we don’t get to
cover in this class– but here’s the complete
list of everything of how Excel calculates formulas. So if you download
this, you can see. You can see that
comparative operators are way down at the bottom. Right? All right. So let’s ask this question. All our question is,
is 2 plus 2 times 3 raised to the second
power equal to 144? Or is 2 plus 2 times 3 raised
to the second power equal to 20? Which is it? If we didn’t have these
order of operations, people all over the
planet earth would be getting different answers. Right? All right. I’m going to just take a
guess here, wild guess here. I’m going to say that it’s 144. OK, so here’s my logic. 2 plus 2 is 4. 4 times 3 is 12. And 12 squared– which
means 12 times 12– is 144. Right? So that’s what I think. I’m going to try this. Excel knows the
order of operations. And I got lots of
great questions over the years of people saying,
how come Excel’s not working? It’s not calculating right. It’s really that
the person doesn’t know their order of operations,
just like me right here. I don’t really know my
order of operations. I’m thinking that the order of
operations is left to right. But lo and behold, when I
type it in, when I hit Enter, Excel will give you
the right answer. User error here. If I really wanted to
go from left to right, I would have to do something
completely different. Meaning, I’d have to know
my order of operations. All right. So in this case, it’s not. The order of operations is
do everything in parentheses first, then all exponents,
then multiplication, division, left to right, and addition
and subtraction left to right. Now watch this. Really, what happens? There’s no parentheses,
so we skip on this one. There is an exponent. So what’s 3 squared? That means 3 times 3. It’s 9. So we get a 9 there. And then we do multiplying next. So 2 times 9 is 18. And then finally, we
do our plus and minus. 18 plus 2 is 20. So sure enough, it did– Excel gave us the right answer. Because Excel knows this
order of operations. Now, sadly, many of Americans
don’t know order of operations. So here’s the deal. There’s only four things. So go and memorize it. Now, I’m going to copy this. Now, here’s something cool. We’ll get to see this in a
couple of videos coming up. That little thing
in the corner there is called the fill handle. When you move– this
cursor right here is called the selection cursor, right? But if you move your cursor
right near the fill handle, you see that black crosshair? Bill Gates calls it a crosshair. I like to call it
an angry rabbit. If you click and drag,
it will copy the formula. All right. Now, I’m going to
change this one. And we’re going to make it 144. If you really want
the 2 plus 2 first, that’s where the
parentheses come in. I put parentheses right here. that’ll force that one. But then I need to
force the multiply next. So I have to put another
set of parentheses. And notice what Excel does. This is cool. It gives you a color coding. I wish that worked when I
drew it on paper like that. So now we have a color coding. The black ones are
on the outside. Well, wait a second. When I hit Enter, that will
show the correct color. Here, I’m going to– here’s a keyboard shortcut. Control Enter puts the
formula in the cell and keeps the cell highlighted. And now I’m going to hit F2. And now you could see– for a moment there, it wasn’t
showing the right color. But when I F2 to
put it in edit mode, the green ones are calculated
first, and then the black ones, right? So 4. And then the 4 times 3 is 12. And then the 12
raised to the caret 2, or raised to the
second power, is 144. Now let’s learn how
to evaluate a formula. If you go up to the
Formulas ribbon, formula Auditing, and
then Evaluate Formula. Oops, that’s off to the side. Uh oh, my video has been off
the screen this whole time. Formula, Formula Auditing. And you can’t see it. It’s off the screen. So I’m going to
scoot it over here. Formula, Formula Auditing,
Evaluate Formula. It’ll open up this dialog box. And you can click Evaluate. And this will show you
exactly how it calculates 4, and then 4 times 3 is 12,
and then 12 caret 2 is 144. All right. So the lesson here
is, you’ve got to know your order of operations. So if you don’t know
and you’re in the class, just four things to memorize. There’s our 20 right there. I know that was off the screen. That’s too bad. All right. Order of operations. One last topic. Exponents. All right. We talked about this
just a moment ago. If I take 2 raised
to the 6 power– this is the reverse of
the one we did before– the base always means
that’s the big thing. The 6 means repeat– that’s the exponent– repeat the
big thing, the base, six times and put a multiplication
symbol in between each one. So six– 2 times 2 times
2 [INAUDIBLE] is 64. Here’s an example
of calculations we’ll be doing when we get to
cash flow analysis, discounting cash flow analysis, chapters
4, 5, 6, 7, and onward. We’ll have formulas like
this, where we have a 1 plus the annual
interest rate divided by the number of compounding
periods per year raised to the compounding
period’s time total years. That whole thing we’ll
have to calculate. So not only will we
have to do exponents, but we’ll have to know
our order of operations. Exponents. Also in this class, we’re
going to have square roots. And square roots–
there’s a function in Excel called
square roots, which we’ll look at in just a moment. But there’s not a
root function where we can do the fifth
root and the sixth root and the seventh root. These two examples are square
roots and how to express them. You can express a square
root as 2 raised to the 1/2. But if it was the
fifth root, you would have to be 2
raised to the 1/5. And we’ll see an example
of that in just a moment. Exponents. When you have a
situation like this, x squared divided
by x squared, you know there’s one thing
divided by the other. So that means it’s
just 1, right? But in exponential denotation,
you take the 2 on the bottom, and you bring it up to the
top, and put a minus sign in between it. So you get x raised
to the 2 minus 2. So that means any time you see
x to the 0, you know it’s 1. So x to the 0 is going to be 1. Again, if you have an x to the
third divided by x to the 3, you could think
about it this way. Cancel, cancel, cancel, cancel. And what’s left is an x. But if you do it in
exponent notation, you bring the 2 up here,
and you take 3 minus 2. So 3 minus 2 is x to the 1,
which of course is just x. Finally, x– anytime you see
a negative exponent means you take the big
thing right there, and just put it underneath. Even if it’s x raised
to the minus 10, you take the x to the 10 and
put it underneath a division bar and 1 on top. So it’s really the
inverse of whatever it is. So x to the 1, when
you see a negative 1, it means it takes the
inverse of x to the 1. Inverse just means
put a 1 and a slash, and then whatever the
thing is underneath. All right. Let’s see some examples here. 2 caret 6. That should be giving us 64. So I’m going to say 2,
and then caret that 6. And we should get 64. Let’s go ahead and do
this factor over here. We don’t need to know
the meaning of it. We’re just practicing
the math of it. All right. Equals, and then 1 plus. And we take our
annual rate divided by our number of periods
per year, close parentheses, and then caret. And now in math class,
we wrote it like this, and we could get away with it. We could just put the exponent
right next to the parentheses, make it small– a little small
and up like this– and then just write 12 times 30. And everyone knew to
calculate it correctly. But that doesn’t work in Excel. And the reason why, is because
the order of operations. If you do– and
multiplication is commutative, which means
you can do it in any order. 12 times 30 is the
same as 30 times 12. But I’ll keep it the same here. 12 times 30. The problem here
is that it’ll do everything inside of the
parentheses, and then the exponent next. So because multiplication
comes after exponent, and we want it to come first,
we have to go like that. We have to say, hey, no, no, no. Do that multiplication
before the exponent. So really, the last thing
calculated in this formula is that exponent. If we do Formula Evaluator,
I’m going to click Evaluate, and you can see,
it’s calculating everything inside that
first parentheses first. There’s the plus. It gets that right there. Now it’s going to do
everything inside of there. OK. And finally, the
last thing it does is that exponent right there. All right. And exponents. Square root, I mean. And what we want is
the square root of 16. Now, what does square root mean? If you say, what’s
the square root of 16? What you’re really asking is,
what times itself equals 16? Well, if you know your
multiplication table, you know it’s 4 times 4. Because 4 is the
itself part, right? So what times itself equals 16? So 4 times 4 equals 16. So here’s how you do it. You go equals whatever
that cell is, caret, and then we have to, in
parentheses, 1 divided by 2. Right? That’s the longhand way. Now, when we’re
doing square roots– and in chapter 11 or something,
we have to do square roots– we can use the
square root function. And we’ll learn
about that there. And that’s just because square
roots are pretty common, right? But if we have to take the
third root of 8, which means– and I wrote it up here– if
you say the third root of 8, third root of 8,
which should be– I don’t have it written out. It should be like this. 8 caret 3. It means what times itself
three times equals 8? Well, 2 times 2 times 2 is 8. So the answer should be 2. But how do you do
a formula for this? The base is 8 caret,
and then in parentheses, 1 divided by– and I’m
going to click on this– 3. All right. And that should tell us 2. And the reason why is
because 2 times 2 equals 8. All right. Exponents. One last topic in this order
of operation and math symbols. We got to talk
about the number 1. The number 1 is a magic number. Anything divided by itself– whoops– anything
divided by itself is 1. 43 divided by 43, 16 divided
by 16, whatever. x squared times y divided by x
squared times y is 1. In this class, we’re going to
do financial statement analysis, and we’re going to do
lots of ratio analysis, and something called
DuPont analysis. And we’ll see things
like assets divided by assets, which means we’re
looking at the balance sheet, and we’re going to see
the total asset amount. So what is it? What’s assets times assets? Usually we only do numbers. Well, anything divided
by itself is 1. And so in this class, we will
see debt divided by equity, assets divided by net
income, or whatever it is. Sometimes we will see this
strange setup like this. And just realize, it’s 1. Anything divided by itself is 1. Look at this. 1 raised to any number is 1. 1 raised to 10,000
or a million is 1. 1 raised to any exponent,
or any root here, equals 1. So 1 has an interesting
characteristic. We’ll see two examples
right now of the use of 1. When we get to
chapter 3, I think it is, we’ll do assets
divided by equity, which gives us our leverage. That means how much debt. It’s a measure of how
much debt we have. And we’ll learn that assets
equals all the debt plus all the equity. So we can have this calculation
right here, D, debt, plus equity, D plus
equity, divided by equity. Well, we can break that apart,
because we’re doing addition. And it has a common denominator. So we can say D over
E plus E over E. And immediately
when you see this, you realize this is a thing
divided by the same thing. So we can convert it to 1. And it’s very convenient in
our ratio analysis to do that. But it all stems from
the fact that anything divided by anything is 1. And then, of course, if we
have our debt to equity, we can just add 1,
and immediately we will know our
assets over equity. Another important
example of the number 1– this is for percentage
change formula. And we’re going to have lots
of these in this finance class. Let’s imagine our stock
on January 1 is $98, and on December 31 it’s $102.50. And we want to figure out,
what is the percentage change? Well, we’re going to
call the end value– that’s the one at the end– end, and the begin value begin. All right. So the percentage
change formula is– now, this is the long way. And I’ll show you both
ways in a video coming up. And this way is a lot longer. But if you know the number
1 trick, we can change this. But here it is. You take the end minus
begin, which just gives us the difference, right? 102.50 minus 98. That’s the change. It went up, right? If it went down, it
would be negative, and it would indicate
a reduction in value. But there it is. That’s the change
in the numerator. And you divide it
by the begin amount. So in this case, it
would be 102.50 minus 98, end minus begin, divided by 98. And that would tell us
the percentage change. But look at this. That’s a subtraction. And this is a
common denominator. So we can take this and take
end, divide it by begin, minus begin divided by begin. Immediately when we
see that, we say 1. And in statistics
and finance, this is the formula you use for
calculating percentage change. Very rarely do you see this. This is what you usually see. And it’s because we know
anything divided by anything is 1. All right. So that’s just a
little bit about 1. We’ll see those actual
calculations coming up. In our next video,
we have to talk about the ever elusive and
troubling number formatting. If you know about it, it’s easy. And then we’ll
talk about percents and a couple other topics. All right. See you next video.

Leave a Reply

Your email address will not be published. Required fields are marked *