Welcome to Highline Excel 2016 Video Number 7.
If you want to download this workbook Business218 Video 07,
and follow along, click on the link below the video.
Last video, Video Number 6, we talked all
about making calculations with conditions or criteria.
Now in Excel 2016, if you have Office365
and you've signed up for the automatic updates,
or you're in any version after 2016,
we have two new great functions, MinIFS and MaxIFS.
Now last video we talked about what
to do when you have an aggregate calculation with conditions
or criteria and there's not a built-in function.
We can simply string IF functions together
with an array calculation.
And we did a couple examples of that.
So before this new function, if we
wanted to do Min with multiple conditions, no problem.
We combined the two, did an array formula,
and used Control-Shift-Enter.
Same with Max.
IF function, Array operation, and Control-Shift-Enter.
But in 2016, if you have the MinIFS watch this.
This is simply amazing.
There it is.
The IF of course, means we can have conditions
for a Min calculation.
And the S means you can have one or more conditions.
Now earlier versions of Excel always
had duplicate functions for aggregate functions
with the IF, like SumIF.
And then there was also a SumIFS.
But now moving forward, Microsoft
realizes they do not need the straight MinIF without the S,
because the S can do everything.
It can do one calculation or one or more.
So tab.
Min Range.
We're looking through the sales.
Control-Shift down arrow, F4.
Comma.
No If or anything.
We just put the criteria range, just like we learned
with CountIFS and SumIFS.
So I'm going to start with Product.
Control-Shift down arrow, F4.
Now I do a comma.
And there's the criteria 1.
I match the criteria 1 with criteria range,
since we're looking through products.
Here is our row header.
Now we need to lock this.
As we move across the columns, it needs to be locked on G.
But when we copy it down it needs to move to 6 and then 7.
So I hit the F4 one, two, three times.
Lock the column but not the row.
Comma.
Criteria Range 2.
It's going to be Sales Rep.
Control-Shift down arrow, F4.
Comma.
And there is criteria 2.
Up arrow, there are Sue.
I need to lock it when I copy down,
but not when I copy to the side.
Because I need to move to Joe and then Chuck.
So I hit the F4 key one, two times,
lock the row but not the column.
And that's it.
No array formula.
Close parentheses.
I'm going to use Control Enter.
There's no curly brackets.
This is an automatic built-in function,
just like SumIfs and CountIfs.
Double click and send it down, copy it over.
Go to the diagonally furthest one away, F2.
And there we go, the cell references are working.
Now MaxIFs is going to have the same exact range
here, except for instead of MinIFs it will be MaxIFs.
So watch this.
I'm going to cheat.
I'm going to copy this cell, paste it down here.
Right now it's giving me the Min.
Not only that, but when I put it in Edit Mode,
notice that the one cell reference that's not right
is H4.
So I'm going to point to the corner and with my Move cursor,
I'm going to click and drag down to Sue.
Then I'm going to get rid of the Min and put Max.
And there it is.
There's MaxIFs.
Control Enter.
Double-click and send it down.
Copy it over.
Go to the last cell and hit F2.
And sure enough, look at that.
It's got all the right ranges and cell references.
So MaxIFs and MinIFs make conditional calculations
when you're trying to find the Max or Min, much easier
than earlier versions.
Now let's go over and we want to talk
about another amazing new function in 2016 or later,
the IFs Now the IFs, that's the IF function with an S,
will allow us to list multiple logical tests
and multiple results without nesting multiple
IF functions like we had to do in earlier versions.
When you have to use this is when you have not
one of two things to put into a cell, like the IF function,
but it's one of three or one of four.
You're selecting amongst more than two items of something
you want to put into the cell based on logical tests.
Now I want to look at this situation here.
This is a common situation.
We have an income statement.
And net income is simply F2, Revenue minus Expenses.
Now one of the conventions in accounting
is that we have the word Profit or Net Income over here.
But when the revenues become less than the expenses
and I hit Enter, we do not want to show the number
as a negative.
We want to show it as a positive,
and then indicate that it's a negative by changing
the label to something like Loss or Net Loss.
So we have to fix this formula.
And then we're going to add a nested IF to show you
how we did it in earlier versions,
to either put Profit Loss [? over ?] Break Even.
And then we'll see how to do it with the new function.
Well, fixing this one is easy, because we're always
going to get the same number.
But it will either be negative or positive.
We use the ABS function.
That shows the absolute value.
That means the distance from 0.
So if it's minus 5000, that's 5000 away from zero.
If it's positive 5000, that's also 5000 away from 0.
So that will solve that problem.
Now over here.
And I'm going to change this back to 50,000.
And sure enough, that is working right there.
Now we come over here.
And we have not one of two things we need in the cells,
we have one of three things, Profit Loss or Break Even.
So I'm going to start by saying Equals If.
And we're going to do it the old way first.
Logical test.
Now I'm going to choose to check if Revenue
is Greater than Expenses.
If Revenues are Greater than Expenses
I type a comma, and the value If True
is going to be N double quotes Profit, end double quotes,
comma.
Now the rule when you're using IF and nesting IF functions
together is, when you get to the value of false,
if there are still more than one thing we can put in the cell,
then you have to nest an IF.
So now I do a second IF.
And now the logical test, I'm going
to check to see if there is a loss.
So I'm going to say If Expenses are
Greater than Revenue, comma.
If that comes out true, then I want loss
in double quotes, comma.
Otherwise notice this is IF IF.
And in the IF function there's a value If False.
When we learn the new function IFs with an s,
there is no value If False.
But here value If False, that means
if we didn't have a profit or a loss, what's left?
[? In ?] double quotes, Breakeven [? end ?] double
quotes.
Now we have to close parentheses.
And notice our parentheses is not black.
Close parentheses.
You keep putting parentheses until you see your black one.
Now we're done.
We hit Enter.
And now it's a profit.
Now we're going to check all three situations.
40,000.
And boom, I have a positive number here.
But the sign is indicated by the label.
And I want to have exactly 45,000.
Boom.
There's that little accounting dash.
That's Number Formatting, of course.
And there is Breakeven.
So let's see how to do this formula with a new IFs I'm
going to do equals ABS.
And I'm going to take Revenue minus Expenses.
And I'm actually going to change this back to 50,000.
Let's check this out.
Equals IFs.
And there it is.
There's an IF with an S. Logical test 1, value If True, 1.
If we keep putting new logical tests and value in If True,
we will never get to that value If False.
Let's try this.
Logical test.
I'm going to say, hey, when Revenue
is Greater than Expenses, there's
a logical test 1, comma.
Value If True 1.
This is going to be in double quotes, Profit.
End double quotes.
Comma.
And there it is, Logical Test 2.
And by the way, we can do this in any order we want.
I'm going to say, hey, when this is a loss,
so expenses have to be greater than our revenue.
Comma.
Value If True 2, in double quotes.
We've got a loss, end double quote.
Finally, here's our last value.
It doesn't say Value If False.
It says, hey, give me the other test.
Now the other test says, hey, expenses
are exactly equal to revenue.
Comma.
Then the value If True 3, in double quotes, break even,
end double quotes.
Close parentheses.
And that will do the same thing as our nested IF up here.
If I change this, we've got to test all three.
I'm going to check to see if revenues less than expenses
gives us a loss.
Yes, and 5000.
Greater.
Exactly correct.
And then 45.
And then we're going to be optimistic.
Hey, look at that.
Our revenues were 75,000, so our profits should be 30,000.
Now there is one caveat since there is no value If False.
If you want a default value you actually
have to do something kind of strange here.
You'd have to just type True here.
So if it runs through all of the previous logical tests,
and it gets to this one, it will just say True.
And then you put a default value.
Now we're not going to go through that in this video.
If you want to see a comprehensive video
about this new function, here it is.
ExcelMagicTrick1286 Excel 2016IFS.
Next video we're actually going to start
talking about Text functions and Date functions,
before we jump in to Lookup functions.
All right.
We'll see you next video.