Welcome to Excel and
Business Math video number 6. Hey, in this video we got to
talk about formula elements and formula tips
for business math. Yes, 19 different
formula examples. Here's all of the examples we're
going to see in this video. We're going to talk about
math operators used in Excel, math order of operations,
comparative operators used in Excel, types of formulas
that we'll see in this class. And we're going to have this
great formula tip sheet that'll always help us figure out the
right set-up for our formulas. Now we want to start off by
going over to the MOO sheet. This isn't actually MOO. This is math order
of operations. And here's a list of our
math operators in Excel-- parentheses, exponents,
also known as caret, multiplying, dividing,
adding, and subtracting. Now when we use more than one
math operator in a formula, then we have to worry about
the math order of operations. If we didn't follow the
math order of operations everywhere on the planet
Earth, then everyone would be getting
different answers. So here it is. First, do everything
in the parentheses. Second, do all exponents. Third, do all multiplication
and division, left to right. And fourth, do all adding and
subtracting, left to right. Here's a little short
abbreviated version-- 1, 2, 3, 4. If you want, you can
have a little cheat sheet like this with you as you
evaluate to remind you which one to do in which order. Now there are some memorization
tricks, like, please excuse my dear aunt Sally. There's some other
memorization tricks also. But I like to just list
the operators like this. and as I'm evaluating,
make sure I'm doing it in the right order. Now let's go look at MOO2
for our first example. Now here's a simple example. 8 plus 2 times 2,
or 8 plus 2 times 2. Now here's our little
order, 1, 2, 3, 4. If we're looking at
this first example, there's an addition symbol
and a multiplication symbol. Well, looking at this list,
multiplying comes first. So I'm definitely going to
multiply 2 times 2 to get 4. Rewrite it, and now
I'm left with only one operation, adding. So I add them to get 12. If on the other hand, I
wanted to force addition before multiplying, that's
where parentheses come in. And there's lots of examples
in this business class, where we'll do exactly this,
force adding or subtracting before we do some multiplying. So parentheses are handy. For us, it means do addition
first, or everything inside the parentheses. So we add to get 10. Rewrite. We're only left with multiply. And 10 times 2 is 20. Now, before we do these
examples in Excel, let's go over to our PDF notes. This is page 2 of our notes. And we want to look
at example 3 and 4. And then we'll go do
them over in Excel. Now here's our math
formula or math expression. In parentheses, 8 plus 2
minus 1 and then exponent. That's a caret, meaning
exponent 2 times 2. Now parentheses,
those are first. So we have to do those first. I'm going to do the 8 plus 2 and
rewrite the whole expression. Then I'm going to
subtract, since we still have an operation left in
the parentheses, to get 9. Now we have from left to
right only two operations, exponent and multiplying. If we wanted to, we
look at our little list. Exponents come next, so
we will do exponents. Now what in the
world is an exponent? An exponent, or that caret-- that's what we'll use in Excel-- just means there's two things. The base, that's the big thing. And then the little thing,
that's the exponent. An exponent or caret means
take the base and repeat it. How many times? Two times. And then put a multiplication
symbol between each item. So that's what
we're going to do. We repeat the big thing,
the base, two times and put a multiplication
symbol between each. So now we're left with
left to right multiplying. 9 times 9 is 81. 81 times 2 is 162. Now here's our second example. 8 plus 2 times, in
parentheses, a 100 minus 25. So parentheses come first. We do that, we get 75. Two operators. Multiplication comes first. If you're not sure,
we look at our list. Multiplying comes first. So 2 times 75, that's 150. We're left with
only one operator. We get 158. Now let's go back over to
Excel and do this example. Now this is just a straight math
example to get some practice. But throughout the class, we'll
have lots of business examples. I'm going to see if I can type
this formula out, so you ready? Equal sign, open parentheses. And then I'm going to
arrow up to get that 8. Then use plus. That's our math operator. And arrow up to get the 2
minus another math operator. Up arrow to get that 1. Close parentheses. Now I do exponent,
which is Shift-6. Arrow up to get my
2, and then times. And up arrow to get the last 2. So that's how we do it in Excel. And guess what? Excel knows the math
order of operations. I'm going to Control-Enter,
and I get 162. That's exactly what
we got on paper. Now there's a cool
feature in Excel. You could actually go up
to the Formula ribbon tab. And since I have
that cell selected and there's a formula inside, I
can go over to Formula Auditing and click this great
feature, Evaluate Formula. So I'm going to click. And this Evaluate
Formula dialog box will step through
and show you how Excel calculates the formula. Now you can either click
the Evaluate button, or because it's highlighted, I
can simply use the Enter key. Notice something's underlined. And the thing that's underlined
is inside the parentheses. So when I hit Enter,
it evaluates it. Oh, look at that. In Excel, it has to go look in
all the cell references first. So Enter. It's got the 2. Now we can do the
first math operation inside the parentheses. So I hit Enter. It got 10. Now it's going to look in C12. It finds a 1. Now it's going to
do the subtraction inside the parentheses. Enter. Look at that. It's got to get rid
of the parentheses. Now it's going to look in C13. 9 caret 2, 9 times 9. 81. It's going to look in C14. And finally, 81 times 2. I hope it gets 162. And sure enough, it does. The Evaluate Formula can
be used not just when you have straight
math operators, but later in this
video, we'll see that there's other
types of operators, like comparative operators. And Evaluate Formula
will step through and calculate any formula, no
matter how simple or complex, for you. Sometimes you can use this
to track down an error. Where is the error coming from? Step through and find out. Now I'm going to close this. And we're going to do
our second example. 8 plus 2 times in parentheses,
100 minus 25, equal sign. Up arrow to get my
8 plus, up arrow to get the 2 times
in parentheses. Up arrow to get the 100 minus
my 25, close parentheses. When I hit Control-Enter, it
gets exactly the same number as we did it on paper-- 158. Now let's look at
a business example. We need to calculate something
called cost of goods sold. Any retail business has to do
this every accounting period, also known as COGS,
for cost of goods sold. Here's our product. At the beginning of the
period, we had 100 Aspens. Aspen is a boomerang. So there's the product. We had 100 of them at the
beginning of the period. And then at the
end of the period, we went and counted
on the shelf. The end quantity was
50, so we counted 50. That means if we're
trying to find out the value of how
many we sold, we have to take whatever
we started with, subtracted how many
we had on the shelf at the end of the period. And that will tell us
how many items we sold. Now we can do this in our head. 100 minus 50 is 50. So we actually had 50
on the shelf at the end. And we also sold 50. If the value of each is $10,
if we multiply 50 times 10, we would get the total
cost of all the goods sold. All right, so our
formula in accounting would be end quantity
minus beginning quantity times the value. Since we have to
force the subtraction before the multiplying, that's
where our parentheses come in. There's our formula. Let's go ahead and do it. Equals open parentheses,
and I'm going to up arrow to get begin quantity
minus up arrow to get end quantity, close
parentheses, times that $10. And when I enter, $500. So order of operations
in Excel, as we saw here. And we're not going to really
do it on paper in this class. But I had to show you
at least a few examples. Now we need to go talk
about something else. CO sheet here, that stands
for comparative operator. Comparative operators. Yes, we can ask a bunch
of logical questions. We can ask if two
things are equal. That means we'd use
the equal sign, not as the first character in the
cell, but in the middle of the formula. And we'll see an
example of that. Not, I don't think we get to
use that one in this class. But you'd have to type
less than and greater than symbol back-to-back. And then Excel would
know that that means not. That means you would
be asking the question, are these two things not equal? Greater than, you definitely
sometimes want to know, did you pass the sales
bonus hurdle to get a bonus? So you might ask, are
your sales greater than? You might also ask, are your
sales greater than or equal to? Now in math, we have a
single symbol for that. But in many computer
systems and here in Excel, we have to type two
characters, a greater than and then an equal symbol. And we'll have to use that one
a lot when we get to payroll, and we're figuring out
bonuses and things like that. We also have the comparative
operator, are you less than? And also, are you
less than or equal to? Now here's our two
examples on this sheet. And then we'll have at least two
other examples in this video. Now in accounting, when you
have a bunch of numbers, there is left and right. And in accounting, they call
left debit, and right credit. And in accounting, debits
always have to equal credits. Now we can clearly see with our
eyes that debits equal credits. But accountants
like to make sure. And since Excel formulas
do not make mistakes, but we humans do, we're going to
build a logical formula that'll deliver true when
the two numbers are in balance and false when
they're not in balance. Now any time you use one of
these comparative operators in a formula,
you're building what is called a logical formula. And the only result
from a logical formula is true or false. Now I'm going to click in
this cell, type an equal sign. I'm going to use my left
arrow to get the debits. And then I'm going to use in my
formula a comparative operator. Notice the equal sign as the
first character in the cell always defines
this as a formula. But if we use the equal sign
somewhere else in the formula, it's a comparative operator. And I'm going to ask
the question, debits, are you equal to arrow,
arrow, the credit side? So of course when I hit
Enter, it delivers True. What? False, that's why
we use formulas. The formula is always
going to get it right. When we see False, that means
we have some trouble over here. Now this trouble is simple. Someone just accidentally
decreased the decimals. Highlight both cells. Go up to the number group. And increase the
decimals, once and twice. And sure enough,
there's the problem. We can clearly see up here if
we're adding debits and credits in accounting, both
of these numbers would have to be the same. So what our formula
telling us False did is it alerted us as
accountants that we need to go investigate and find
out, is this amount really 149, or is it 150? So our logical formula was
helping us proof, in essence, if two numbers were equal. Now let's go look
at our next example. And this is a payroll example. And our table shows employee
name and their sales amount. These are sales rep. And these are their
sales for the period. And their employment
contract says if they have $55,000 or more
of sales, they get a bonus. That means for each employee,
we have to compare their number. So for [? Abdi, ?] I
have to ask the question, are the sales for [? Abdi ?]
greater than or equal to $55,000? Well, that would be true. Down here for Sue
is $37,251.06-- is that greater than
or equal to $55,000? False. So instead of doing it by hand
and risking error, or more to the point, if we
had 50 employees, I do not want to do
each one individually. So instead, we'll do
a logical formula. Equal sign tells Excel
this is a formula. Left arrow, employee sales. And then I need to do the
comparative operator, greater than or equal to. Now remember, there
is no such thing as a single greater than or
equal to character in Excel like there is in math when
you're writing it on paper. In Excel and many
computer systems, we have to type two characters. And the equal sign
always comes last. So greater than or
equal to, and then I'm going to click
on the hurdle. Now I need to lock that as I
copy down, so I hit the F4 key. Now notice what this is doing. The bigger side of the
greater than symbol is pointing towards the number. So it's saying, are you
bigger than or equal to whatever the hurdle is? Now I can Control-Enter. And I'm going to double-click
and send this down. I go to the last
cell and hit F2. The cell references
are looking good. Now I clearly have my
patterns of trues and falses. But wait a second. Poor [? Micki. ?] She says,
yes, but I got $55,000. And your formula didn't work. Well as we know, Excel
formulas don't make mistakes. It's us, the driver of
the Excel spreadsheet, who's making a mistake. So it looks like we simply need
to increase the decimals one, two times. And what a bummer,
one penny short. But sure enough, that
formula is 100% correct. That number is not bigger
than or equal to $55,000. Now I want you to
notice something. As I described
this problem here, I used three different phrases
to represent greater than or equal to. I first started off
by asking the question of a particular sales member. I said, is that number
$55,000 or more? Then I asked, is it greater
than or equal to $55,000? And just a moment
ago on this one, I said, is the number bigger
than or equal to $55,000? So any time we are required
to use a comparative operator, there is not going
to be consistent language, especially
when you get to things like employment
contracts, payroll contracts, tax tables. They all use all sorts
of different words to describe a particular
comparative operator. So I made a little
table over here. Let's go click on the
sheet, Comparative Operators and Words. Now for equals, it's
pretty much always equals. But when you see
greater than, you might see the word, greater
than, more than, or above. Those are all synonyms that
mean the greater than symbol. Now for the greater than or
equal to comparative operator, these are all the different
phrases, or words, or synonyms you might see. Greater than or equal
to, I like this phrase, because it describes
exactly what we have to type into Excel. But you might also see at least,
no less than, or x or more. So for example, if I had
5,000 as the number we're comparing to the hurdle-- and by the way, a hurdle,
that's just something you need to jump over
in order to get a true. But if we apply the
words, this number right here has to be at least 2,000. That means the smallest
it can be is 2,000. Notice it follows the same logic
as greater than or equal to, because this can
be at least 2,000. That means it could be exactly
equal 2,000 or anything above. Similarly, no less than,
that means this number can't be less than 2,000. If it was less than 2,000,
that would give us a false. Notice if it's not
less than, then it has to be exactly
equal to or above. We might also see x or more. In fact, I used that
phrase just a moment ago when we were doing
our payroll example. That means this
number right here has to be equal to 2,000 or more. All right, so this table
has phrases, or wording, or synonyms for each one of
the comparative operators. So if you get in trouble when
you're reading a description, especially like I said
and I mentioned earlier, tax tables are famous for
using language like at least and no less than. But if you get in trouble, just
come look up the word here. And then figure out which
comparative operator it is. All right, now we want to go
look at our next sheet, Formula Elements. And at the top of
this sheet in yellow-- and this is also
in our PDF notes-- is a complete list
of all the topics we should be considering every
time we create a formula. Now on this sheet,
if we scroll down, we're going to have nine
amazing examples that will show us all the
different types of formulas that we might use in this class,
re-illustrate and reconfirm Excel's Golden Rule. We'll see examples of all
of these formula elements. We'll go through the
math order of operations. We'll remind ourselves
how to use Round. And we'll even see two important
hints about the sum function. Now formula elements. Up to this point in
this video, we've seen a bunch of different
formula elements. That just means things that
we can put into formulas. Well, I've listed them all here. These are all the different
things that in this class we will see in our formulas. We're definitely always
going to have an equal sign as the first character. We can have cell references,
like relative cell reference or absolute cell reference,
a relative range of cells, or an absolute range of cells. We definitely can
use math operators. We can type numbers
into our formula if they don't change,
like 12 months in a year. We can use built-in
functions, like the Sum function or the Round function. And we can use
comparative operators. So throughout the rest of this
entire business math class, this sheet can be your
guide to creating formulas. Now let's go and look
at our nine examples. We're going to start
with example number nine, so far in this video. Now the goal here is to
calculate the average and maximum values
for each quiz. So we can see students
and a number of quizzes. I just need a formula that'll
calculate the average, copy it over, and the max, copy it over. This type of formula is
going to be a number formula. And in this class,
we just see basically formulas that calculate numbers. And we'll see logical formulas. The formula elements we'll see
in these formulas-- equal sign, built-in function,
relative range of cells. All right so in cell C45,
I'm calculating the average. So I know equals AVER. I see my function highlighted
in blue, so I hit Tab. I'm going to select all of the
scores for quiz number one. And this is actually the
first time we've seen this. That's a range of cells. But because there's
no dollar signs, it's a relative range of cells. That means when we copy
the average function over to each new column,
it'll properly look at all of
the numbers above. Close parentheses,
Control-Enter, copy it to the side. Click and drag. Go to the last cell,
and hit F2 to verify. And sure enough, our relative
range of cells is working fine. We've calculated the
average for each quiz. Now the max, we can use as we
learned a couple videos ago. Average and max are called
aggregate functions, because they look at
a bunch of numbers and calculate a single answer. Close parentheses,
Control-Enter, copy it to the side. Go to the last cell. Hit F2. So we've accomplished our goal. We calculated the average
and max for each quiz. Example number nine, we need
to calculate monthly insurance expense. This is a number
formula, because we're calculating a number. We'll see equal sign, cell
reference, math operator, and of course, a number. This formula is straightforward. Equal sign, up arrow to get the
annual expense, math operator division. And I'm typing the
number in the cell. Since 12 months in a
year will never change, we can simply type it in. And when I hit Enter, there
is the monthly allocation or the monthly
insurance expense. All right, let's go look
at example number 10. Oh goal, we already did this
once already in this video. We want to calculate
COGS, cost of goods sold. But here, we have a bunch
of different products. We're going to come over here. And I'm going to
type an equal sign. And I first need to take
whatever we started with and subtract the end quantity. So I'm going to arrow over. That's the begin
amount, minus, and I'm going to arrow over
to get end quantity. And that'll calculate
the difference, which we can do this one in our head. That means we sold 50. And I'm going to
multiply, left arrow times the value of each one. Control-Enter, minus 400, F2. Of course, the order
of operations for math performs multiplication first
and then does subtraction. So if we want the
subtraction to occur first, we'd better force subtraction to
calculate before multiplication with parentheses. Remember, Excel does know
the order of operations. Control-Enter, and
double-click, and send it down. Go to the last cell, and hit F2. There it is. For the Sunset Boomerang
product, we started with 54. We ended up with
51, which means-- I think I could do
that in my head-- we sold 3. So 3 times 12, when
I hit Enter, 36. Luckily, we didn't have
to do that in our head. We just, as the designer of
this worksheet, Excel Solution, we just had to make sure that
we created the correct formula. All right, and in
this example here, we saw equal sign, parentheses,
a bunch of relative cell references, math
operator subtraction, and multiplication. I'm going to hit Enter. Example number 11. Our goal is to calculate the
deduction for each employee, then calculate a
total for deductions. It tells us that the
tax rate is 0.1675. The type of formula, of course,
this is a number formula. And here's the list of
our formula elements. Now when we're thinking
about this list over here-- and by all means, you can print
that page out from your PDFs and always have it around. Well, we can read through this. And there's a
couple things we're going to need to think about. The first stop is if we're given
a particular number of formula input that can change, we have
to remember Excel's Golden Rule. Not only that, but
because we're going to be doing some calculation
and using the formula results in other
formulas, meaning we're going to add
the total deductions, we have to think
about whether we need to use the Round function. All right, so example number 11. I'm going to come off
to the side and type tax rate, Control-Enter. I am going to add
some formatting. So fill, red. Font, white. Some border. Next video, actually, we'll
talk about style formatting. We'll have a video just on that. And our tax rate, zero. Remember, we don't
have to type in the 0. I just have to type a
decimal, 1675, and then enter. It puts in the lead 0 for us. So that's us following
Excel's Golden Rule. Now we can already see
that this is a decimal. And we're dealing
with money, right? That's one of the
requirements for Round. When we're dealing with money,
we are required to round, because we only have pennies. And these are
employees' paychecks, so we need to pay out in
pennies and be accurate. So we have extraneous decimals. And we're going to add all
of these formula results in a subsequent formula. All three conditions for
requiring us to use Round are met. So I come up here, and I'm
going to type Round function. Right in the number argument,
I can build my formula. Left arrow to get gross pay. That's a relative
cell reference. So as we copy down, it'll
always see the right gross pay. Times, right arrow
to get my tax rate. And I need to lock it. So I'm going to use the F4 key. As I copy down, every
cell needs to be locked looking at tax
rate, comma, and our rule for number of digits. That is, which position
do we want to round to? We're going to count on our
fingers from the decimal, 1, 2. So since it's the
penny position, that requires that we put a 2. When we put number
of digits, 2, it tells Round to round
to the penny position. Close parentheses,
Control-Enter, and copy it down. Now, we can go to the last
cell and use our keyboard for the Sum function,
Alt-Equals, and Enter. And now we have
accomplished our goal, deductions for each
employee and the total. If you wanted to be safe,
if someone gave you this, this is what I do. Notice it was already
formatted with currency. So I'm going to come up and
click the dropdown for General, just to make sure there's
no extraneous decimals. And there's not. Remember, that Sum
function is looking at exactly whole pennies. So that total is correct. Now I'm going to
Control-Z, because I want to undo applying
the general formatting and go back to the previous
currency number formatting. All right, example number 12. Our goal is to calculate whether
or not each student earned an honors badge. Honors is assigned
when the average quiz score is greater than 22.5. I already see greater
than, so it sounds like a comparative operator. Here's our students. Here's all the quizzes. The average, we first have
to calculate the average before we can determine true
or false, they earned honors. So in this cell, I'm
going to type equals. And we're going to use the
built-in function, Average. When I type AVER, I hit Tab. Now I'm going to
highlight the range. These are the relative
cell references, four cells to the left for each student. Now earlier when we studied
aggregate functions, we actually don't have to
type that close parentheses if we have just a single range. So I'm going to Control-Enter. If I wanted to verify, I could
look up into the formula bar. I see the close parentheses. Now I can come and double-click
and send this down. Go to the last cell and F2. Now we need to check
whether each average quiz score is greater than
our hurdle of 22.5. Whoops, that's a formula input. We're definitely going to have
to follow Excel's Golden Rule. I'm going to type
hurdle for honors badge, and Enter, 22.5, and Enter. I better add some formatting. I'm going to highlight
those two cells, go up and add all borders. Then I'm going to
click in the cell. Click the dropdown for
fill, red, and font, white. Maybe I need to increase
the column width. Now I can build a
True/False formula. I use an equal sign. That tells Excel
I'm doing a formula. Left arrow to get relative cell
reference for the students' average quiz score. Greater than, notice it doesn't
say greater than or equal to. It's just it has to be bigger. So that has to be
bigger than the hurdle. And I'm going to hit
the F4 key to lock it. Control-Enter, double-click,
and send it down. Go to the last cell and hit F2. I see the cell references,
relative cell reference and absolute, are correct. And Enter. And now I see, looks like Tyrone
and [? Gigi ?] earned honors. All right, let's go look
at an example number 13. I'm using the wheel on
my mouse to scroll down. All right, our goal
for our formula, create formula to determine
whether we need to reorder. If end quantity is less
than 75, we must reorder. So it sounds like we have a
comparative operator, right? And we want a True/False. True would mean we
need to reorder. False means we do
not need to reorder. We definitely have
a hurdle here. And that is a formula input
that can potentially change. Remember what we
said in our video about Excel's Golden Rule. If you're not sure whether
a formula input will change, put it in a cell and label it. So 75 will be our hurdle. And then I'm going
to add a label. I did that backwards. Hurdle to reorder, and Enter. Look at that. It AutoFormatted, which is
a setting in our default settings. That's OK. I'm going to click here
and change it to red. So I'm going to say fill. Now I'm going to
highlight both cells and click the All Borders. Again, next video, we'll learn
more about style formatting. All right, but now I
have my formula input. Equal sign, the left arrow. Well, I need to check if
it's less than, because I want a True saying
I need to reorder. So that has to be
less than whatever this hurdle is in cell G104. I want that locked,
so I hit the F4 key. Control-Enter, double-click,
and send it down. It looks like we need to order
the Aspen, [? Bellon, ?] and Sunset. I want to make sure and
check the last cell, F2. Cell references looking good. Enter. All right, we have
a few more examples. I'm going to scroll down. The goal for this formula
is to calculate commission for each employee,
then calculate a total for commissions. The commission rate is 0.025. So it looks like we have an
employee sales, employee sales. And a commission,
which we'll get to learn in our payroll
section of the class, is when the employee contract
says, you go ahead and make all your sales. And the payment we'll give you
is a certain decimal amount or percentage amount. You can think of it like this. The contract says, for
every dollar that you sell, we're going to give
you 2.5 pennies. All right, that's
a formula input. It could potentially change. So I'm going to come off
to the side, type a label, add some formatting,
and then type my 0.025. Control-Enter, I'm going
to ad an all border. All right, we have
our formula input. Now let's create our formula. But wait a second. Do we have to use
the Round function? Well, let's check. Do we have to round? Yes, this is money. We have to round to the penny. Do we have extraneous decimals
in our formula result? We definitely have
the potential here. We're multiplying decimals. I already see
extraneous decimals there past the penny position. And third, each one of
these formula results, we're definitely
going to be using those in a subsequent formula. So I'm going to click in
cell D117 and type ROU. I see my Round
function, and Tab. Right in the number argument,
we can build our formula. Left arrow to get sales, as
a relative cell reference, times right arrow to
get the commission rate and F4 to lock it,
because as we copy down, we need every cell to be
locked on commission rate. I type a comma. Number of digits, we
need to tell Round to round to the penny position. 1, 2, so I type a 2. Close parentheses,
Control-Enter. And I want to show you
something important here. Sometimes we click
and drag manually. Other times, we double-click. Now this is a circumstance where
we don't want to double click. The reason why is,
if I double click, the formula will
automatically look to see if there's
something to the left and stop when it
sees an empty cell. I definitely don't want
it going all the way down to the total row. That's going to have
a different formula. In fact, if I do it,
double-click and send it down, I get an error, because
it's trying to multiply a word times a commission rate. So Control-Z if you
don't have anything, meaning the same numbers
all the way down, and there's nothing
in the last cell. Then you double-click
and send it down. But since we do not want the
formula to go all the way down, we have to do it manually. Copy it down. Click in the last cell, F2. The cell references are working. Enter. Alt-Equals to invoke
our Sum function. We see that it's got the
right range, and Enter. There is our solution. We are looking at
different formula elements. F2, equal sign
starts the formula, built-in Round function,
relative cell reference, absolute math operator,
and a hard-coded number, because that number's
not going to change. Enter. Down here, F2, equal
sign, built-in function. And that's just a range of cell. We don't think of this as
a relative range of cell, because we're not
copying the formula. All right, I'm
going to hit Enter. All right, we have
two last examples. And these have to do with
the Sum function, example 15 and 16. Now here are some amounts. And here's a formula that I do
not want you ever to create. F2, someone didn't know
about the Sum function. They used the plus
symbol many times. Now there's two
problems with this. One is, it takes a
long time to create. I'm going to hit Enter. And I'll show you
the second problem with that in just a second. Long time to create. Sum function with the
keyboard, Alt-Equals. That's quick and easy to create. I see that it got
the right range. I hit Enter. Here's the other reason we never
want to use plus, plus, plus, plus, plus. This is a list of expenses. And any time we build
an Excel solution, we want it to be updatable. Let's just imagine someone
forgot car expense. Well, if I come
to row 134, and I see that black, sideways,
medium-size arrow, I can right-click
that row header. And when I point to insert,
it will insert a row. OK, no problem. Now I type car, Tab. And I'm going to just put 500. And watch what happens
when I hit Tab. That does not update. Watch what happens when
I type 500 and Enter. That one totally updates. So something like
this, F2 is never going to pick up if we change
the structure of our table. It's not going to pick
up that extra expense. Enter, but this one right
here, F2, totally no problem. In fact, when we use
a range with a colon, it always says go from the
first one to the last one, and everything in between. So when we inserted a row and
Excel pushed everything down, it totally changed from D137,
which is where it used to be, to D138. I'm going to hit Enter. Now here's the other hint
about the Sum function. Almost every quarter
that I teach this class, or every couple
of quarters, F2, I get someone that hands
in formulas like this. They do all their
math calculations but put them inside
the Sum function. Now way back in the beginning
history of spreadsheets, you did have to follow
this convention sometimes. But this is no longer true. There's two problems
with doing this. One is it overcomplicates
the formula. So when you're looking at
it, it's too complicated. And it actually makes Excel
calculate an extra step, which takes longer. Now we're not going to see that. But don't do formulas like this. This Makes the formula
over-complicated. Enter. It will work, because
the Sum function is just adding a number that
was already there. If I'm going to make a formula,
equal sign, up arrow divided by 12, don't put it
in the Sum function, because it's unnecessary. And Enter. All right, now one last
example, example number 17. Now I just copied and
pasted this down here. But I got to show you
something about the arrow keys to get cell references
for formulas. All right, so I selected that
cell, And I'm looking down. This is called the Status Bar. These are the sheet tabs. And below this gray bar
is called the Status Bar. We looked at this for a
moment in our first video. But now I want you
to notice something. It says Ready down there. And what Ready means
is we're allowed to do something to the cell. Now I'm going to not
delete the formula. I'm just going to
create a new one. And watch what happens
when I type an equal sign. Now it says Enter down
in the Status Bar. And Enter means you
can use your arrow keys to get cell references. So if I up arrow, I'm totally
allowed to get cell references. Now as soon as I'm
using my arrow keys to get cell references,
now the status bar says Point, because I'm
pointing to a cell reference. Now I want to use the
Escape key to revert back to what was in the cell
before I created that formula. And now I want to
try this again. Notice it says Ready. That means it's ready for me
to do something to the cell. I type an equal sign. No problem. Enter means I can use my arrow
keys to get cell references. But watch this. I accidentally interfere
and click in there. Now it says Edit. Watch what happens when I
use my up and down arrow. Up, up, up, down, down, down. No way. It's not going to get
cell references now. If I use the left
and right arrow, Edit means that the arrow
keys will move left and right through the formula. So now I'm going to right
arrow to get to the end. If I accidentally
get in that mode, and I want to get
back into Enter mode so I can use my arrow keys,
I have to hit the F2 key. As soon as I hit F2,
now it says Enter. Now I can use my arrow keys. So once in a while,
that will happen. You'll be trying
to use your arrow keys to put cell references in. If it doesn't work, just hit F2. Then you're back in Enter mode. You can put in your
cell reference, build the rest of your
formula, and hit Enter. All right, that was a pretty
epic video all about formulas. On this sheet, Formula
Elements, we actually saw 10 examples of formulas
and formula elements. We saw all the
different possibility of things, four Excel
formulas that we will see in this business math class. Now Control-Home. Don't remember
this yellow sheet, which is here on
Formula Elements sheet, also in the PDF notes. This is like your cheat sheet. Every time you
make a formula, you can look at this as a
checklist to see if you're building an efficient formula. Now in this video, we saw all
the different formula elements on this sheet. Back on CO, we learned
about comparative operators for the first time
and logical formulas. Over on the sheet,
MOO2, we talked about order of operation. And we saw a few different
number formulas, or formulas where we practice our
order of operations. Hey, if you like that
video, thumbs up, leave a comment, and sub,
because there's always lots more videos to come from XLS
Fun, including our next video, in this class, video
number 7, where we'll talk about style formatting. All right, we'll
see you next video.
