Welcome to Office 2013 Class Video Number 31.
If you want to download this workbook--
and this is the workbook for videos 28 to 34--
click on the link below the video,
or go to our class website.
In this video, we want to talk about what
can go into formulas.
We want to see examples of formula elements.
So let's scroll down here.
Now a formula-- we've already been
doing lots of formulas-- formulas
just make calculations in the cells and sometimes
other locations.
Examples, adding a column of expenses,
adding with one condition, joining first and last name--
we haven't seen a formula like that before-- or even checking
whether two numbers are in balance.
We haven't seen a formula like that before.
Now, there are different things you can put into formulas.
But all formulas start with an equal sign
as the first character in the cell.
Then after that equal size, here's
a list of the possible things.
Things like equal sign; cell references;
math operators; comparative operators
like greater than or less than; numbers, if they don't change,
like 12 months in a year or 24 hours in a day,
as we saw a few videos ago; built-in functions.
We've seen a few of those.
Comparative operators-- oh, I have that listed twice.
I better come up here and delete this.
Right click the number.
Delete.
You won't see that when you download this.
The join symbol and even text in double quotes.
We're going to see an example of most of those here.
So we have five examples just to look at some different formula
elements.
Now, this one here, we already have
seen this a lot-- Alt equals.
But notice what's in the formula.
There's an equal sign that says, hey,
this is a formula, the sum function and a range of cells.
So three different formula elements.
Enter.
Now, we have a column of type and amounts.
And sometimes this is a case.
You get revenues and credits in the same column.
And you want to just add the credits.
Well, we have an indicator here.
This is a revenue.
These other ones are credits.
So we could use the SUMIFS function.
We're adding with one condition.
So SUMIFS, ah, there's the sum_range.
Those are the numbers you're adding.
We don't want to add all of them.
That's why we're using the SUMIFS.
Comma, the criteria range, we want the revenues.
So I have to highlight the whole column.
Comma, and then the criteria, the word Revenue.
Now we could click on a cell.
But this isn't going to change.
So I'm going to actually type this into the formula.
So this is an example of text inside a formula.
In the criteria argument we need to put the word "Revenue"
to tell SUMIFS to look through here for the word Revenue.
So there it is.
Text in a formula.
And this will add just the revenues, not the credits.
And of course, you can make a formula for the credits, also.
Now there's another way we could do this particular problem.
And in some cases, you're not going
to be able to do this because you just
get the column of numbers.
You need to add just the numbers greater than zero.
Now we could use the SUMIFS.
And that would work.
But here's a cool trick.
Notice if we're adding with criteria,
well, the sum range would be this.
And the criteria range would also be this.
Our criteria is going to be add the numbers greater than zero.
So any time the sum range and the criteria range
are the same, it's better to use the SUMIF, SUMIF.
Now, SUMIF has range.
It doesn't say criteria range, but that's what it means.
And then the next argument is criteria.
And the last one in SUMIF is sum_range.
Now, we've been using SUMIF.
So we're used to that.
But the one situation where you want to use SUMIF,
if the criteria range and the sum range is the same,
you just put it in one time.
Then type comma and your criteria.
All right.
So if you're doing this type of calculation a lot,
it saves time because you have to enter one fewer argument.
Close parentheses.
And it will work fine.
Now, another way we could do this, instead of pointing
to a cell-- and I want you to notice something.
That greater than or equal to-- if I type a 0 here,
it's a number.
Right?
As soon as I add a greater than operator with the number--
and notice the greater than is pointing,
I always think, to the column meaning all the numbers.
And then the less than is pointing towards the 0.
But any time we put that comparative operator
with a zero, remember our alignment.
It says that's text.
So actually we can simply come down here.
And since this is an example of something that's
not going to change, our revenues
are always going to be greater than zero,
and our credits are always going to be less than zero,
I can put SUMIF in the range.
And remember, sum_range and criteria range is the same.
So we only have to put it once in that range.
And then for criteria we'll put, in double quotes,
greater than 0.
End double quotes.
Close parentheses.
And so that will work also.
So this is an example.
We're using a comparative operator here.
And in this case, since SUMIF wants it as text,
we're putting it in double quotes right into our formula.
And there we have it.
Now let's look at another type of formula altogether.
We've been looking at calculating formulas
that calculate number answers.
But sometimes we can use what's called a text formula.
And here's the situation.
First and last name, and we need to join them both in one cell.
And imagine this is a template, like we
have a grading table or a payroll table.
And every single time we get our new set of names,
first and last are always in different columns.
And I need them together in one column.
Well, we can do that with a formula.
And by the way, we're getting new names regularly
for our template.
So that means we want to use a formula.
So we just put the names here.
And automatically it updates.
All right.
All formulas start with an equal sign.
Hey, I'm trying to join this cell and this cell.
So I'm going to click on the first cell, first name.
Now we use the Join symbol.
It's the ampersand, or the and symbol, right above the number
seven, so Shift 7.
And then we click on the other cell.
That ampersand, or join symbol, says,
hey, get whatever's in A56 and go and get whatever's in B56
and put it in the cell.
Now when we Control-Enter, there's going to be a problem.
Hey, wait a second.
We need a space before that.
F2.
Well, if we want a space between it,
then we need to put a space between that cell
and this cell.
So we click after the ampersand.
And even though a space seems like nothing,
it's actually a character.
When you hit space bar, it's a character.
So you have to put it in double quotes.
Double quote, space, double quote.
And now we have one, two, the text thing is a thing,
and three things to join.
So because we have three things, we need two ampersands.
You can also tell there's a problem with your formula.
As soon as your cell reference turns black,
you know it's not a correct formula.
But as soon as I type ampersand, I'm
joining one, two, three things with two ampersands.
And my cell reference gets its color back.
Control-Enter.
And double click, and send it down.
Go to the last cell, and F2.
That's amazing.
Now when next week we get our new names, we dump them here.
And this updates.
Now this is a text formula.
The Join symbol is common in text formulas.
Sometimes, however, you don't want
to keep it in the same order.
So we can put last.
And instead of just a space, we'll
put a comma and a space and then first name.
So equals, last, I need to join it.
And I'm going to put some text.
Notice, I need the last name and then comma space.
So any time I'm putting text into a formula, double quote.
Then I need what?
Oh, yeah, a comma and a space.
And then I have to close it off.
I always have to have double quotes at the beginning
and end.
And now Join symbol and the first name.
And that's our text formula to reverse the names
and add a comma between them.
Control- Enter.
And copy it down.
Click in the last cell.
F2.
That is amazing.
That's a text formula using the ampersand
to join things and text in double quotes.
Escape.
All right.
Now a text formula.
Let's look at our last example for this video.
And this is called a logical formula.
Hey, logical formulas only have two possibilities--
true or false.
So here's our goal.
We have debits and credits.
That's in accounting.
Right?
And debits and credits have to be equal.
But we're human.
We're fallible.
We make mistakes.
We humans sometimes just look at number formatting.
But formulas don't look at number formatting.
Right?
So a lot of spreadsheets for budgets or controlling accounts
that you have to check against other accounts or debits
and credits where you're checking two numbers to see
if they're in balance, you type an equal sign.
Now notice, equal sign says, I'm doing a formula.
It's the first character in the cell.
So then we click on the first, the debit.
And we type a second equal sign.
That equal sign is a comparative operator.
It says, are you equal to this other cell?
We're comparing these two cells with a comparative operator,
the equal sign.
Right?
Control-Enter.
FALSE.
That's why you do these types of formulas.
Because sometimes we're not paying attention.
If it says FALSE, that means you have to go investigate.
I'm probably going to investigate
by highlighting and going up and doing General or increasing
the decimals or something.
Sure enough, someone accidentally
typed a extra number there.
So as soon as I remove it, Delete,
I can see that now it says TRUE.
Now I'm going to Control-Z. So we
head back to the [? former. ?] The reason this formula exists,
it tells us we need to go check.
And so we found the extra decimal there.
I'm going to leave it like that for the time being.
All right.
So we learned something new.
There's other types of formulas besides just
number-calculating formulas.
We can have logical formulas.
They give true or false.
We can definitely have text formulas.
That's a fairly common formula.
And then we saw SUMIF function, when the sum range and criteria
range are the same.
We saw the SUMIF and the SUM in a bunch
of examples of formula elements.
All right.
We'll see you next video.