1
00:00:01,250 --> 00:00:06,260
Now let's take a look at getting the max and min values of a data set

2
00:00:06,370 --> 00:00:11,920
and we're also going to take a look at how we could get these by criteria.

3
00:00:11,950 --> 00:00:17,580
We have here the list of our employee names with their entry date, yearly salary, and previous year salary.

4
00:00:17,590 --> 00:00:22,480
Our first task is simple, we just want to get the highest yearly salary.

5
00:00:22,480 --> 00:00:28,420
This means we just need to use the "Max" function. Max is a very simple function just like the "SUM" function

6
00:00:28,810 --> 00:00:35,230
we can give it one range of values or multiple ranges of values. Since we want to get the max of the

7
00:00:35,230 --> 00:00:36,260
yearly salary.

8
00:00:36,280 --> 00:00:40,080
I'm going to give it that column, close bracket, press enter.

9
00:00:40,270 --> 00:00:44,220
That's the highest number in this data set.

10
00:00:44,230 --> 00:00:51,460
In a similar way I'm going to get the lowest yearly salary. This time I need the "Min" function, highlight my range, close

11
00:00:51,460 --> 00:00:52,920
bracket, press enter.

12
00:00:53,110 --> 00:00:56,400
That's the minimum value in this data set.

13
00:00:56,400 --> 00:01:04,810
Now how about if I wanted to get the maximum yearly salary after the entry date below. This means I need

14
00:01:04,810 --> 00:01:07,750
to use max based on a condition.

15
00:01:07,840 --> 00:01:16,130
Now since Excel 2019 or if you have Office 365 you're going to see you have access to the new "MAXIFS"

16
00:01:16,170 --> 00:01:17,420
function.

17
00:01:17,450 --> 00:01:23,800
There is no "MAXIF" function because this function never existed before and when Microsoft introduced

18
00:01:23,800 --> 00:01:29,400
it in Excel 2019 they decided well we're just going to allow for multiple conditions.

19
00:01:29,470 --> 00:01:35,740
So they added the "IFS" version. This works in a very similar way to the other "IFS" functions we saw before,

20
00:01:36,100 --> 00:01:38,510
we first need our max range.

21
00:01:38,560 --> 00:01:45,370
In this case it's our yearly salary. Next argument is the criteria_range1.

22
00:01:45,610 --> 00:01:52,960
Our actual criteria is the date, the range it's sitting on is right here and we want to make sure that

23
00:01:52,960 --> 00:01:55,680
it's greater than this date.

24
00:01:55,690 --> 00:02:01,780
So what do I need to do right after the Excel argument separator? I need to add quotation marks because

25
00:02:01,780 --> 00:02:09,430
the greater than sign needs to go in quotation marks. And then I'm going to combine this with my date

26
00:02:09,460 --> 00:02:11,490
which is sitting in this cell.

27
00:02:11,650 --> 00:02:17,450
If I want to include that date I can put an equal sign right after the greater than sign.

28
00:02:17,530 --> 00:02:23,710
In this case I'm just going to go with a greater than, close bracket, press enter. So that's the highest

29
00:02:23,710 --> 00:02:28,390
salary after this date.

30
00:02:28,390 --> 00:02:32,560
Now I could easily change this to another date.

31
00:02:32,560 --> 00:02:39,460
Let's update this to 2018 and this number is going to reflect the highest salary after this date.

32
00:02:39,460 --> 00:02:46,020
Now let's practice with the "MINIFS" function. We want to get the minimum previous year's salary and

33
00:02:46,020 --> 00:02:51,790
we want to exclude zero values because right here in our data set, we have a bunch of missing numbers

34
00:02:51,820 --> 00:02:57,590
probably because these people are new but the cells aren't empty. They actually contain zero

35
00:02:57,610 --> 00:03:01,440
and we want to exclude that when we calculate the minimum value.

36
00:03:01,450 --> 00:03:03,570
So I'm going to go with "MINIFS"

37
00:03:03,610 --> 00:03:06,970
My Min range is now my previous year's salary column.

38
00:03:07,030 --> 00:03:11,650
The criteria_range1, what column is that?

39
00:03:11,650 --> 00:03:13,210
It's actually the same column.

40
00:03:13,210 --> 00:03:13,470
Right?

41
00:03:13,480 --> 00:03:20,020
Because I want to exclude the zero values and zero values are sitting in the same column that I want

42
00:03:20,020 --> 00:03:25,450
to look for the min value in. My actual criteria should be bigger than zero.

43
00:03:25,450 --> 00:03:30,820
So again, I'm going to add the greater than sign in quotation marks. And since I'm not going to have a

44
00:03:30,820 --> 00:03:38,560
negative salary I'm fine with just adding the greater than sign and combining it with zero. Or another

45
00:03:38,560 --> 00:03:47,080
way of writing this is just putting greater than zero and closing the bracket. My value is 15,086.

46
00:03:47,170 --> 00:03:54,430
Let's just double check this, that looks like the smallest value that comes right after zero.

47
00:03:54,520 --> 00:04:01,240
So that's how easy it is to use the "MAX" and "MIN" functions and you can also use the "MAXIFS" and

48
00:04:01,240 --> 00:04:05,310
the "MINIFS" functions to account for multiple conditions.