Welcome to Excel
Basics video number 12. Hey, in this video,
we got to talk about formula types,
formula elements, and excels calculation
order, and we have 12 amazing examples. In fact, here are all 12
of the different formulas we'll see in these videos, and
here's the goal of each one. Now to start this off,
there are different types of formulas in Excel. So far in this class,
we've seen number formulas. That means a formula
that delivers a number, like a budget number for
January, a tax deduction, but there's also other types
of formulas, like text formulas and logical formulas. All right, so we're
going to see one by one each one of
these amazing formulas. Let's go over to
the sheet FT and FE. That's formula types
and formula elements. So in this video, we're going
to see you one, two, three different types of formulas. We'll see a number of formulas,
and number formulas simply deliver a single number
answer, like a tax deduction or budgetary expense amount. And then we're going to see for
the first time a text formula. And just like you might
guess, a text formula delivers text, just
like a number formula delivers a number. So text formulas
deliver text items, such as of first and last name
in a single cell or a category. Logical formulas is
a new formula type that we haven't seen before. The fancy name is
Boolean formulas, but logical formulas just
deliver either a true or false. Those are the only
two possibilities when you do a logical formula. So for example, we
might ask the question, has the employee earned a bonus? True or false. Now with these different
formula types, in this video we're going to talk more about
the different formula elements. Now most of these we've already
seen, like the equal sign that starts any formula, cell
references like relative cell reference absolute or relative
range or an absolute range. Math operators, we've seen
some of the different math operators. Numbers, if they will change,
you can type a number like 12 into a formula and not
violate Excel's golden rule. For example, 12 months,
we saw in our last video, we had to divide
annual rate by 12. Well, we just typed the
number into our formula, because that number was
never going to change. We also have seen built-in
functions, like SUM, ROUND, and other built-in functions. These are all examples
of things that we can put into formulas when
we're creating our formula. Formula elements, we've seen
1 through 5, but look at this, we're going to learn about
the join operator today. Ampersand-- that's the fancy
name or the and symbol, Shift 7. We are allowed to
use this operator to join things together. For example, we're going to
have a first name in one cell and last name in another cell. And instead of
copying and pasting, we'll just join the two
cells with the ampersand, and it will join them
together in a single cell. We'll also see in this video
for the first time text within double quotation marks. Any time you want to
use text in a formula, you have to put it
in double quotes. So for example,
our example would be we want to join last
name and then first name, but we're going to
have to put the comma space between last name and
first name in double quotes. So join operator and
text in quotations, those are new formula
elements we haven't seen. And then we'll also
see for the first time comparative operators. We're allowed to use greater
than, less than, greater than or equal to. Yeah, you have to use
two symbols, which is totally different than math. Less than or equal to equal,
and even this strange one which means not, two
things are not equal. The example we'll
see in this video-- we'll actually
see a few videos-- but imagine we had
your sales, and we need to ask if your sales
is past the hurdle of 50,000 to determine whether
or not you get a bonus. True or false. All right, so we're going to see
all three formula types and all these different formula
elements in our 12 examples. Let's scroll down and
see our first example. Our goal here is add calls made
by a service center last week. So here is our dates,
here's our calls. Well, we already
know how to do this even without worrying about
what formula elements are in the formula, so
let's just do it. The key board for
the SUM function is Alt equals, but
now let's stop, and let's just look at
all the different things that went into it. Absolutely equal size. Every formula you
ever create will have an equal sign as
the first character, then there's a built-in
function called SUM, and there we have
a range of cells. All right, so I can hit Enter. We just formally named
each one of those elements. This is a number formula,
because it delivers a number answer. And there is the list
of our formula elements. Let's look at our
second example. Our goal here is to calculate
the monthly insurance expense. This will be a
number for me also. We're starting with
an annual insurance, and I simply need
to divide it by 12. Equal sign, that's
a formula element, up arrow to get my
cell reference, that's another formula
element, math operator forward slash for division. Now I simply type
the number 12 in. Since number of months
in the year won't change, we simply type it in and Enter. Let's look at our third example. Our goal is to calculate the
deduction for each employee. Gross pay-- I need to
calculate deduction here based on this tax rate. Now notice, as soon
as we see a tax rate where we have some decimals
past the penny position, I know I run the risk
of extraneous decimals when I multiply the two. Not only that, but I need to
take each one of the formula results-- that means multiply this
decimal times this decimal with extraneous decimals. And I'm dealing
with money where I have to around to the penny,
because if I didn't round this, there would be
extraneous decimals. If I were to use the so
function here to add these up, if I don't round, I might
get the wrong answer. So, I'm definitely
going to start off with equal sign, that's
the formula element to tell Excel we're doing a formula. I'm going to use the
built-in function ROUND. I type ROUND in, and when I
see it highlighted in blue, I hit Tab right in
the number argument as we have done so often. I'm going to either click or
arrow, that's a relative cell reference, times,
that's a math operator, I'm going to click on
cell F46, and hit the F4 key to put those dollar signs
in to make it an absolute cell reference. So as I copy down, it's locked. That's an absolute
reference, yet another type of formula
element, something we can put into our formula. Now, I type a comma, that
gets us to the next argument and number of digits. Since we're around to
the penny, we put a 2. Now, that's the number
we type into the formula, but for money, and we're
doing with pennies, that's not going to change. So now I close
parentheses, Control Enter, I'm going to copy down. I'm immediately going
to come to the last cell and click F2 to verify that
the cell references are looking in the correct location. Now I'm going to
hit Enter, and we're going to add all of these
properly rounded numbers using the keyboard Alt equals. I see the SUM function
equal sign built-in function in a range, I hit Enter. Now we can add some currency
number formatting or counting if we want. I'm going to select
all those cells, hold Control, click
on the SUM cell, go up to the dropdown
for currency or county, or I can use the
keyboard Control 1. Format Cells allows
complete control over how you want the number
formatting to display. I'm going to accept
the defaults, click OK or hit Enter,
and we've added currency. So we did one, two
different formulas. Both of these are
number formulas, because their delivery
numbers, and there are the formula elements
for that deduction formula. Now, let's go look at
another number formula. We're going to scroll
down to example four. This will be our
fourth number formula. We need to calculate what's
called cost of goods sold-- COGS. So when you work in accounting
where people are selling items like shoes, well, the shoes
have a sales price, that's the money coming in, but how
much did the shoes cost you? Cost of goods sold. If you sold a pair of shoes
for 60, and it cost 30, the $30 bucks would be
the cost of goods sold. Now we need to calculate this. I'm going to Control
and roll my wheel, because I want to
zoom out a bit. Now in order to calculate
cost of goods sold, that means for the whole, we
have a beginning quantity, that's how many items
we had on the shelf. We're talking
boomerangs here, so we have Aspen, the Quad, Carlotta. We started with 114, and
at the end of the period, whether it's a
week or a month, we counted how many we
had on the shelf, 45. Or, we looked in our computer
records and said we had 45. So I need to subtract
these two, that's the beginning minus the end
count to get how many I sold. Then I need to multiply how
many times the value for each. All right, so we're going
to do a formula here. Equals, well, I need to
take beginning quantity and subtract the
ending quantity. Let's just do that part. I'm going to Enter
it and copy it down. Control Enter, so
there's 69 of those left. double click the fill
handle with my angry rabbit, and send it down. Go to the last
cell F2 to verify. Sure enough. 54 minus the 51 still on
the shelf that means we sold only 3. Now I'm going to
control and a roll. I should have had
this little thing. I'm going to move it. I highlighted it, and I'm going
to point with my move cursor right there, click and drag. F2 to put that in edit mode. What I want to do is
multiply that 69 times the 10, because the cost of
goods sold should be sold 69. Each one was worth
10, it be $690. So times, and I'm
going to get that $10. Now when I control
Enter, whoa whoa, wait a second, minus $336-- that's not how much
it's supposed to be. Well, we have to remember our
order of operations in math. Parentheses calculate first,
then an exponents, then multiplication and
division left to right, and then adding and
subtracting left to right. Please excuse my
Dear Aunt Sally. That's one memorization trick. There are other ones also. What did I do? I'm going to hit F2. I forgot to tell my
formula to make sure and do the subtraction first. Now I'm going to close
parentheses, open parentheses, and now I have an
equal sign as a formula element some parentheses, two
relative cell reference, a math operator subtraction, then on
the outside, a math operator multiplying and another
relative cell reference. Now I can Control Enter,
and there's my 690. I got the cost of
goods sold correct. I can double click
and send it down. Go to the last cell F2,
that is looking good. A bunch of different
important formula elements, don't forget those
parentheses if you want to force a particular
math operation to occur before another math operation. Now I'm going to click Escape. Let's go look at our
first text example. We want to go down
to example number 5. Our goal is to join text and
an item number into a cell. So here's our data set. We actually have
products in one column, and we have the item
number in another. Well, what we really
need is not this item. For each product, I
need actually the text. ITEM a space, the pound
symbol, another space, and then the number. So instead of actually
copying and pasting, I'm going to copy and type
out, and then Control V, and then do that for each one. Instead of doing that, I'm
simply going to join some text. So I'll type out
that text one time, and I'll join it to this number
in a formula and copy it down. Now this will be the
first time that we've seen the join operator. Ampersand is the fancy word. It really is just the in symbol. The keyboard of
course is Shift 7. This will also be the
first time that we've seen how to put
text into a formula, and we always have to
remember to use double quotes. All right, so let's
see how to do this. Equal sign, that's
a Formula Element, and I'm going to type-- and this is a mistake-- item, space, pound sign space. That will not work. And in fact, I'm going to enter
this and show you what happens. Notice there's an equal
sign in some text. The only thing we've seen so
far that would work like this is if I had equal
sign and there was a name of a function
like SUM or ROUND. If I Enter-- well, it's not
going to even let me do that. Let's see if I can
backspace, backspace. If I were to enter
this, it's polite. It says, I have no idea what
the name of that function is. If I hit F2, notice
there's no item function like there are SUM and ROUND. I'm going to put the space,
the pound sign in the space. So, no problem. Anytime you want
text in a formula, you have to remember to put a
double quote at the beginning and a double at the end. Also notice, I
put a space there. And even though we usually
don't think of the spacebar as a character, it really is. There's an i, a t, a em,
a space, a pound sign, and a space. So they're all in double quotes. Now, I do Shift
7, the ampersand, and that allows me to join
one item with another item. Now notice, that's
hardcoded in as text, and we're always
going to join it to the relative cell reference. So when I Control Enter, and
double click, and send it down, go to the last cell,
F2, that's beautiful. We have told he created
the required item number for our system. And notice, this is text. The default alignment
is to the left. All right, F2 and
the formula elements we used in this text
formula, equal sign in double quotes we put our text,
we use the join operator, and a relative cell reference. We joined two items together
in our text formulas. Now any time you use
the join operator, you will always get
as a result, text. Now, let's go look
at another example. All right, example 6. We need to see how to join first
and last name into one cell. Now, here's a great
example, a common example. We're given first and last name. And we simply need to have both
of those names in a single cell so instead of copying
and pasting-- oh, that would take too long-- let's use a formula and
simply join the first name and the last name. All right, so I'm in cell D82. Equal sign starts all
formulas, and then I use my arrow keys to get
that relative cell reference, the join operator
ampersand, and then that left arrow key to get
a relative cell reference. Now if I Control Enter,
there's going to be a problem. And the problem
is we didn't need to just join first
and last, we actually needed to join three things-- first name, then a
space, then a last name. So we need to edit
are formula, F2. And after the first ampersand--
notice the blue cell, that represents the
first name, but we need to join in the
first name with a space. So right after the ampersand,
I do not just type a space. Any characters have to
be in double quotes. Backspace. So I put double quotes
space double quote. Now notice, here's a great trick
when you're creating formulas. Whenever you see a
black cell reference, you know that there's something
wrong with the formula. And what's wrong with
this is that we need to join one, two, three things. And if we're joining
three things, that means we need
two ampersands. I now need to join the
space to the last name. So now ampersand,
and there we go. That formula contains
the formula elements equal sign, relative cell
reference, the join operator, in double quotes some text-- and in our case, it's a space-- join symbol, and a
relative cell reference. Now we can Control Enter,
double click and send it down, go to the last cell F2. That is beautiful. Now over here in our next
example, we don't want first and last, we actually
want the last name, then join comma and a
space, and the first name. So this also will
be a text formula. We'll start with an
equal sign, relative cell reference, last name. And now we need to join the last
name to and comma and a space. So ampersand, that's our join
symbol, double quote comma space and double quote. So far, I've just joined
first and some text, but now I need to join
the text to the last name, so I use another
ampersand, and then arrow over to get the first
name, not the last name. And that formula will work. Equal sign, relative
cell reference, join operator, text
in double quotes, another join operator,
and another relative some reference. Control Enter, double
click, and send it down. Go to the last cell, F2. So, two more great
examples of when we need to create a
text formula and we're using the join operator and
some text in double quotes. Enter. Now, both of these examples,
and the one we did up here, all of these are text formulas
where we are joining items together into a single cell. Sometimes we need text formulas
to, in essence, do the reverse. So we're going to look
at example 7 and 8. And in example number 7, we
need to extract the state abbreviation. And this is a product
code, and this is common in
transactional data sets where you have dollar
amounts for certain sales. There's all sorts of different
types of product codes. And in this business,
they use the product ID, the name of the
product, and at the end, they have the state where
the product came from. So we have a column of
these product codes. And all we want is the
state abbreviation. So form each one of
these product codes, we need to get the state and
bring it over into the cell. Now, a bit of terminology here
in databasing and computer terminology, whenever
we're dealing with text, we usually refer to the
text as a text string. So, each one of these is
considered a text string. So, we have a bunch
of product codes that are really text strings. And for each one of these we
need to extract the state. Now, any time you're extracting
text from a larger text string, you have to first
recognize a pattern. What is the pattern
that's going to allow us to build a formula to always
get the state abbreviation? Well, this example is
relatively straightforward if we were counting each
one of these text strings as a different
number of characters. But for us, we're always
trying to get the last two characters, the
last two characters from the right of the text. So anytime you're trying to get
the last number of characters from a text string you can
use the RIGHT function, so equals RIGHT. Now RIGHT simply requires
that you give it text, and then nom characters,
that's how many characters from the right. So the text, I'm going to click
on relative cell reference comma, and since state
abbreviation is always two characters, we can simply
hardcode this in by typing a 2. Now this formula has equal
sign, then a built-in function, the RIGHT function, relative
cell reference, and then a number typed in. Close parentheses. And watch what happens
when I enter this. Control Enter. The RIGHT function, and
other text functions like the LEFT
function, will always deliver the result as text. And so when I double
click and send this down, go to the last cell, F2,
I have extracted the state abbreviation with
a text formula. Now, let's get a look at our
next text formula example. Our goal is to extract
the product number, but not using the
RIGHT function, we're going to use
the LEFT function. Now, this company
has a product code where they have the
product number and then the product name. And we simply need,
with a formula, to extract each one of
the product numbers. Now this company always has six
digits in their product number, so that makes the pattern
recognition easy to figure out. Six characters from the left. So, we're going to
come over to cell C108. Equal sign starts all formulas. A built-in function, we'll use
the LEFT function for the text. I'm going to left arrow to put
in a relative cell reference comma, number of
characters, we're going to hardcode 6 in, because
it's always going to be 6, and that number is not
going to change, 6. Now we can close
parentheses, Control Enter, and look at that. Just as we said, text
functions like LEFT and RIGHT will always deliver text, and
we can tell because that's a line to the left. Now, that's never going
to be a problem for us, because these numbers
are never going to be used in math
formulas, like for adding. They are simply do I identify
the product with this product number. So we can double click and
send that formula down, go to the last cell
in F2, and there's text formula using equals, left
function, a built-in function, relative cell reference, and a
number typed into our formula. All right, so those are some
good examples of text formulas. Now we got to scroll down
and look at example number 9. And we're going to see for the
first time a logical formula. Now, logical formulas only
deliver one of two things-- either true or false. So our goal in this
formula is to determine if debits equal credits. Now, in accounting,
this is a big deal. We always have to
verify if the debits are equal to the credits. It's one of two
formulas in accounting that help us keep all of
our records in balance. So, I can clearly see that
these two cells are equal. The total for debit is 448,
the total for credit is 448, so I'm good to go. My system is in balance,
but not so fast. Formulas are sometimes
smarter than we humans. Visually verifying
doesn't always guarantee that those two
numbers are in balance. So off to the
side, this is going to be our first logical formula. Will, watch this. Equal sign, as we've seen so
many times that in this class so far, all formulas
start with an equal sign as the first
character in the cell. Now, I'm going to click on the
cell B127, that has debits, and now we're going to
type a second equal sign. This is the first time we've
used a comparative operator in a formula. Equals sign is going
to ask the question, are the two things equal? We're going to compare
two things on either side of the equal sign. All right, so now I'm going
to click on credit total. Now equals sign, that's
the character that tells Excel this is a formula,
two relative cell references, and a comparative operator-- in our case, an equal sign. It's going to ask if these
two things are equal. And remember, formulas don't
seem number formatting, so this formula will see
whatever is under the cell and tell us true or false. So when I hit Enter, false. What is going on? Yes, in accounting, and
budgeting, and reconciliation, we do this all the time. We always want a
formula to verify that things are in balance. When it says false, that tells
us we have to investigate. Now, as we've seen a lot
of times in this class, probably number
formatting is the culprit. So I'm going to highlight all of
the cells and I'm eyeing here. I see there's a 0.7 there,
a 0.7 there, there's a 0.49, 0.5 there. So I'm eyeing just before I
apply the general formatting. Now, we can use the
dropdown general or we can use our keyboard
Control Shift tilde or grave accent. And just like that, I
can see that someone-- it looks like these two
things should be equal, but someone typed
0.5 for some reason. And instead of trying
to track it down, they were just
hiding the decimals to make it look like
it was in balance. So I'm going to increase
the decibels back up here, and I'm going to come
and fix the problem. Now actually, I wouldn't be able
to fix the problem just looking at this. I'd actually have to go look at
the source documents and see, was it 49 or 50? So I went and looked at
the source documents, and sure enough, it
wasn't 49, it was 50. So I'm going to
type 0.5 and Enter. And now everything looks like
it's in balance over here, but our formula is the
one that really knows. And yes, it's true. Debits are equal to credits. Now, let's go look at
another great example for a logical formula using
comparative operators. Now, I'm going to copy this
and paste it down here. So in this class, we'll get to
see the comparative operator equal, greater than,
less than, and will see greater than or equal to,
and less than or equal to. In this class, we won't
use not, but in the class after this one, Business
218 Spreadsheet Construction or the Highline Excel
Class at YouTube, we'll definitely get to see not. But the symbols we want
to think about for example is greater than or equal to
or less than or equal to. Now in math, we would
have a symbol like this-- greater than or equal to
and less than or equal to. That's a single
symbol that represents that comparative operator. That's a single
symbol that represents that comparative operator. But In Excel, and many other
computer languages, Access-- when we get to Access,
this will be true also-- we have to type two characters,
one after the other. Greater than the
equal sign, or less than and then the equal sign. Now, that's going to
be important here, because this example-- very common in payroll. We have a column of
employees, their sales. And we just need the column
to tell us whether or not they got the bonus. Over here, that's the hurdle. You have to either get exactly
55,000 or more in order to qualify for the bonus. So in every cell, I need
to be asking the question, are the sales for this
employee greater than or equal to 55,000? Down here, I need to
ask, are the sales greater than or
equal to the hurdle? So, let's try to do this. D135 equal sign-- that tells
Excel we're doing a formula-- relative cell reference,
that is the employee's sales. And remember, we're asking, is
that bigger than or equal to? So I'm going to type
greater than or equal to. The bigger sign is pointing
towards the employee sales, so only one that's bigger than
or equal to this amount right here will we get a true. When it's not, like
this employee right here, they'll be a false. Now, we definitely need to
lock F135, so I hit the F4 key. And that is a
logical formula where are the formula elements
are an equal sign to start the formula,
relative cell reference, the comparative
operator greater than or equal to, and an
absolute cell reference. Now we can Control Enter,
double click and send it down, go to the last cell in F2. That is working. And We have a
relative reference, are locked on the
hurdle, and we have our column of trues and falses. So only Emma and Abdi
qualify for the bonus. So that's another example,
F2, of a logical formula-- equals sign, relative and
absolute cell reference, and our comparative operator. All right, let's go look
at a few more examples. I'm going to scroll down. For example 11, our goal is to
count how many of each product were sold. So here's product and sales. We're only interested in
this column right here. Down here, we have
our criteria-- Aspen, Quad, and Sunset. And we want a formula that
will count with one condition. Well, we've used this a
few times in this class. Since we're counting with
one or more conditions, we use the COUNTIF function. Now, criteria range one, that's
the entire column containing all the product names. So I highlight and be
sure to hit the F4 key. That needs to be
locked as we copy down. Then a comma. Criteria-- that's a
relative cell reference. Close parentheses. Before we Enter, there
are four formula elements. Equal sign starts all formulas,
built-in function, COUNT IFS, we have a range of cells
that are locked to make in an absolute range of cells. And then we have a
relative self-reference. So we can Control Enter,
double click, and send it down. Be sure to go to the
last cell and hit F2 verifying that
the range is locked and the criteria is a
relative cell reference. And of course that
is a number formula, because it's delivering
a number answer. Let's go look at our
two more examples. Example 12. Our goal is to create
a formula to determine whether we need to reorder We
have the beginning quantity-- that's how many units we had on
the shelf before the period-- and we tracked how
many units we sold. So if we subtract 121 units
sold from the original start count of 146, we'd
get 25 units on hand. That means how many units
we have sitting on the shelf today. Our hurdle for reorder is 25. Anything less than 25,
then we have to reorder. That is not less than
25, so that's a false. False. We do not need to reorder. It's not less than
25, it's exactly 25. 6, that's true, we
need to reorder. False and true. So, let's create
our formula, but I want to talk about
this list over here. This is actually the list of how
Excel calculates any formula. Now earlier in this video,
we talked about the math the order of operations,
parentheses, exponents, multiplying, dividing
left to right, and adding and
subtracting left to right. But as we've seen in
this video, there's other operators that we
can put into our formula, like ampersand and
comparative operators, so this is the entire list. Now parentheses
are always first. Reference operator, you don't
need to worry about that. That's just for a
range of cells or when we put multiple ranges in
the colon and the comma have to evaluate, so we
get all of the numbers, then the sum can add. Negation-- any time
you put a minus sign in front of a number, Excel
considers it a negative number, so that would be negative 2
raised to the fourth power. The fourth thing that
Excel calculates-- and we're never
going to do this-- is if you type the
percentage into a formula, Excel would have to convert
it, because it doesn't know what to do with that symbol. That's just a formatting thing. So Excel is programmed,
if you did type this in, to convert it to
an actual number then I can use for whatever
the calculation is. Then finally, we
get to exponents. Multiplying and dividing left
to right, adding and subtracting left to right. And look at this at the bottom. Ampersand, and at the very
bottom, comparative operators or comparative symbols. Now, I need to compare each
one of these to the hurdle. But for this formula, I
don't want to use this cell. I want to build that
calculation, which F2 is just simply beginning
units minus the units sold. I actually want to use
that in our formula over here to help us illustrate
Excel's calculating order. Now I'm going to
type in equal sign, and then I need to compare
each one of these amounts to the hurdle. Now as I copy this down
this, needs to be locked, so I'm going to have
to hit the F4 key. And then I'm going to use the
comparative operator greater than, greater than,
because I need to ask the question is a
hurdle greater than 69? Well, that would be false. Is no hurdle 25 greater than 6? That would be true, and
so that's what we want. So I'm going to create
the formula again, beginning quantity, and then
the math operator minus, and click on units sold. Now, if Excel calculated
this left to right, it would get the wrong answer. But we're going to enter
this, and then we're going to watch step by step
how Excel calculates it. And of course, it will
calculate the subtraction before the comparative
operator just as we see over in this list. Subtraction comes before
the comparative operator. Now I'm going to Control Enter,
double click, and send it down. And sure enough, I can see I got
the two trues for reorder here. But, let's go to the
last cell and hit F2. All the cell
references are working. The hurdle is locked in those
relative cell references, but now I'm going
to click escape. And with that cell selected,
let's learn a great trick. We can go up to the
Formulas ribbon tab, over to Formula Auditing,
and there's a great button, Evaluate Formula. So if I click on the
Evaluate Formula, it will step through this
formula step by step, and see how it calculates. All right, you can
either click Evaluate, or I'm going to
use the Enter key. Notice the hurdle
cell is underlined, so when I hit Enter, Excel
evaluates that and says, OK, that's 25. Then, Excel looks
inside of B170, Enter, then inside
of C170, Enter. And notice, Excel
is totally going to subtract before it does
the comparative operators. So now I hit Enter, and
there's our logical formula with two numbers. Is 25 bigger than 3? True. And when I hit Enter,
there's the result. We do need to reorder. Now I'm going to close
this or hit escape. F2. So the formula elements
we had here-- equal sign, absolute cell reference,
comparative operator-- in our case, greater than-- relative cell reference,
math operator, relative cell reference. All right, I'm
going to hit Enter. Now, we have one last example. So in this example, we
have a simple calculation, and we need to
calculate net income, but we were given a
strange situation. Normally, when we're
calculating net income, we have on total expense row. So type total expense, and then
we add up all of the expenses, but that's not what
this report needed. It just wanted to list
everything and just the bottom line, so
watch this formula. Equals, I definitely
need to, for net income, take total revenue, so
equal that cell reference. And then I need to
subtract, that's a math operator, the total
of all of the expenses. And right in the formula,
I'm going to type out S-U-M. I see SUM highlighted in
blue, so I hit the Tab key. Now the number argument,
I'm in a click and drag to highlight just the expenses,
then close parentheses. And look for that formula-- equal sign, cell
reference, math operator minus a built-in function. And then inside the built-in
function is a range of cells. And when I hit Enter, that's
the correct net income. All right, in this video we saw
a lot of different examples. I'm going to Control Home
to jump up to the top. We saw a bunch of
different number formulas, we saw a bunch of different
text formulas, and even a few logical formulas. And we went over multiple times
all of the different formula elements that we can have in
formulas-- equal sign, cell references, math
operators, numbers, if they won't change, built-in
functions, the join symbol or join operator ampersand,
text in double quotes, and even comparative operators. Now, our next video,
we'll talk about a specific logical function
called the if function. All right, if you
like this video, be sure to click that thumbs
up, leave a comment and some, because there's always lots more
videos to come from ExcelIsFun. We'll see you next video.
