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

Similar Content