8 hours 33 minutes
uh, we have started this lesson by explaining how the race can be used in calculations and medical functions.
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
and also combined mathematical or statistical functions.
Also, just a mentioned that formers with the race can be single, so or multi cell.
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,
so let's go to the first example
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.
So it's going to be very simple.
It's just going to say some
off, and then we'll going to have a condition
we're going to select these numbers
and say their greater or equal zero,
and in case they are,
you're just going to return
this number that is greater than zero.
And in case it isn't so if it's negative,
we're just going to return zero.
Which means that in that case,
uh, it's not goingto impact this sum.
And then we're going to close this function if function, and then some function.
Now he function usually returns just one number.
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
course. We press control shift entered and
and we get 17.
And if we look ATT, the numbers that we have here, which are positive or greater, equals zero
and we look att, the automatic some that Excel makes it is 17. So you are correct.
So let's go to the next list of numbers.
And, uh, we have again
some proprietary list of numbers, and we want to calculate the sum for top three. So three largest numbers.
So again we're going to use some function.
Then we're going to see some
off, and then we're going to use the function large
by definition, returns, array,
and, you know, we have
on their way in which we are looking for.
And then we're adding array Constant, which consists of 12 and three saying These are
street up numbers largest once
you're closing large, be a crow closing some.
We're pressing control shift, enter
you have for gotten to type the comma.
So you see that even if you are very better experienced with
excel, sometimes you make
hyping mistakes. So be very careful of what you were doing again. We're going to press control shift, enter
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
because there are 2 80 sevens, but we're looking just a tough three.
And if you look at the auto, some that Excel may have been respected. History Cells is 229. So it is it.
And let's go to the final example early this lesson.
So we're having a yearly data off sales in one company or for just one person.
We have year we have quarter and we have number for each quarter,
and we have here
with drop down lists for year
and for quarter,
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,
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,
and here we're going to type the
which is going to be against some off.
And then we're going to have a logical if
and then if any off numbers from this range is equal,
then we're going to have again if
and then we're going to test
if there's any number from this range
to this one.
And then, in that case,
we're having these numbers here. Okay, so
and then we don't need to close it. We just need to put a comma,
and then we have this range here from which we are picking up the sails,
and then we close the
second if we closed first. If and we closed some
just to explain why we need to have some.
The reason why we need to have some. Because if function is going to return
in each of the every cases of these cases, if we match the year and quarter is going to return
the sales number from calm sea.
But if it's not, the case is going to return false, which is in some representative zero.
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
no into press control. Shift, enter
and we get the correct number. And now, if you change the quarter,
it's going to be 49,899 91 which is this number here.
So it's returning the correct number.
Now let me just show you what happens if we leave
we just leave the ifs Andrea again, press control shift enter.
We get this false. So when we do get the true
is when we pick here, just one,
then we get the correct. If we change the year
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,
then it's going to look at each and every case
that we need. So let's just do the undo.
And with some we have the correct formula.
So this is how you use
or any other
function combined with
the results when using a raise.
Data Analyst Excel
The Data Analyst Excel Test is a premium Cybrary assessment created by iMocha and intended ...
Monitor a Web App
In this IT Pro challenge, participants get hands-on experience activating server logging and using Application ...
Learn On Demand