Time
8 hours 33 minutes
Difficulty
Intermediate
CEU/CPE
9

Video Transcription

00:01
uh, we have started this lesson by explaining how the race can be used in calculations and medical functions.
00:11
But but the use of her raise is best for, and what makes most powerful is when you combine it with the logical or look up functions like IFOR nested if
00:25
and also combined mathematical or statistical functions.
00:30
Also, just a mentioned that formers with the race can be single, so or multi cell.
00:35
And in this lesson, we have bean so far dealing on Lee with formulas that use a raise that are single. Cell four equals the multi cell formulas. We're going to address that later,
00:49
so let's go to the first example
00:53
here. We have a list of numbers in one cone, and, uh, we just want to calculate some off only those numbers that are not negative.
01:02
So it's going to be very simple.
01:04
It's just going to say some
01:07
off, and then we'll going to have a condition
01:11
if
01:14
and then
01:15
we're going to select these numbers
01:18
and say their greater or equal zero,
01:21
and in case they are,
01:23
you're just going to return
01:25
this number that is greater than zero.
01:29
And in case it isn't so if it's negative,
01:33
we're just going to return zero.
01:34
Which means that in that case,
01:38
uh, it's not goingto impact this sum.
01:42
And then we're going to close this function if function, and then some function.
01:48
Now he function usually returns just one number.
01:53
But because we're using a race, it's going to return array of numbers, which is then going Toby some together with some function
02:01
course. We press control shift entered and
02:06
and we get 17.
02:07
And if we look ATT, the numbers that we have here, which are positive or greater, equals zero
02:15
and we look att, the automatic some that Excel makes it is 17. So you are correct.
02:23
So let's go to the next list of numbers.
02:27
And, uh, we have again
02:30
some proprietary list of numbers, and we want to calculate the sum for top three. So three largest numbers.
02:38
So again we're going to use some function.
02:42
Then we're going to see some
02:44
off, and then we're going to use the function large
02:49
by definition, returns, array,
02:53
and, you know, we have
02:57
on their way in which we are looking for.
03:00
And then we're adding array Constant, which consists of 12 and three saying These are
03:07
street up numbers largest once
03:10
you're closing large, be a crow closing some.
03:15
We're pressing control shift, enter
03:20
and
03:23
you have for gotten to type the comma.
03:24
So you see that even if you are very better experienced with
03:30
excel, sometimes you make
03:32
hyping mistakes. So be very careful of what you were doing again. We're going to press control shift, enter
03:39
area, getting the number of 1 229 now It's easy for us to do this by ourselves. Menu away. So we say 122 90 and the next number is 87
03:53
because there are 2 80 sevens, but we're looking just a tough three.
03:58
And if you look at the auto, some that Excel may have been respected. History Cells is 229. So it is it.
04:06
And let's go to the final example early this lesson.
04:11
So we're having a yearly data off sales in one company or for just one person.
04:18
We have year we have quarter and we have number for each quarter,
04:25
and we have here
04:27
the cells
04:29
with drop down lists for year
04:30
and for quarter,
04:32
and we want to select. Sorry, we want to find out what is the sales forthis year in this quarter. Now this is ridiculous when we have just two years. But imagining who had historical data's for sales for,
04:47
Let's say,
04:49
10 years
04:53
back. And let's say it's not just porters for months, so it's going to be a huge table. So looking at its manually is going to be some kind of issue,
05:03
and here we're going to type the
05:06
function,
05:08
which is going to be against some off.
05:12
And then we're going to have a logical if
05:15
and then if any off numbers from this range is equal,
05:21
this one,
05:24
then we're going to have again if
05:27
and then we're going to test
05:29
if there's any number from this range
05:31
is equal
05:32
to this one.
05:35
And then, in that case,
05:39
we're having these numbers here. Okay, so
05:44
hurry
05:46
and then we don't need to close it. We just need to put a comma,
05:51
and then we have this range here from which we are picking up the sails,
05:57
and then we close the
06:00
second if we closed first. If and we closed some
06:03
just to explain why we need to have some.
06:06
The reason why we need to have some. Because if function is going to return
06:13
in each of the every cases of these cases, if we match the year and quarter is going to return
06:19
the sales number from calm sea.
06:21
But if it's not, the case is going to return false, which is in some representative zero.
06:29
Um, otherwise he dysfunction without the sun would be able this formula without, some would be able to find, ah, sales number just for first year in the list. And first quarter
06:43
no into press control. Shift, enter
06:46
and we get the correct number. And now, if you change the quarter,
06:49
it's going to be 49,899 91 which is this number here.
06:58
So it's returning the correct number.
07:00
Now let me just show you what happens if we leave
07:04
this sum.
07:08
And, uh,
07:10
we just leave the ifs Andrea again, press control shift enter.
07:14
We get this false. So when we do get the true
07:18
is when we pick here, just one,
07:21
then we get the correct. If we change the year
07:27
we get the false Why? Because they're they function will go through these numbers, and we'll check just the 1st 1 Now if we put this a raid,
07:36
nested if
07:39
in some,
07:40
then it's going to look at each and every case
07:44
that we need. So let's just do the undo.
07:48
And with some we have the correct formula.
07:54
So this is how you use
07:56
some
07:58
or any other
08:00
function combined with
08:03
nested if
08:03
toe get
08:05
the results when using a raise.

Up Next

Intermediate Excel

Being an intermediate level user of Excel can help save a user hours of work time and take full advantage of the Excel application. Taking an intermediate Excel training will help students advance their skills and pass an intermediate Excel exam.

Instructed By

Instructor Profile Image
Milan Cetic
IT Security Consultant
Instructor