8 hours 33 minutes
Okay, Uh, this lesson.
We're going to talk about multi celled array formulas. So previously you have seen a lot of examples how a raise congee used in formulas and functions. But in all of these cases, we have bean,
creating a formula with the ray in one single. So
now, sometimes it is needed to create a group of cells with formulas
that use race.
And to do that, it's
It's very simple. It's just slightly different than what you have seen before.
So let's go to this example here. So we have a group of cells
and we essentially want to
as an array. So this is simple data entered cell by cell and excel,
and we select the group of cells we want to turn to array,
and then we just type equals. And then we referenced the cells were going to use. And of course, we press control shift Enter.
Now we have got
an array. You see, this is an array which references to these four cells.
It cannot be changed. So if I want to type eight here, I will get the usual error message, which is typical for
trying to meddle with the ray
and we have to press escape to buy that. But we made a mistake and we didn't select all the cells when it did.
So we're just going to select the
*** selection we need.
And we're going to just type B three instead of two
and then press again. Control, shift, enter.
And now we have the Arabia ones.
Now, having that done in the same worksheet is usually something you won't be doing.
But if you have RO dating one worksheet and you want to do something with it in another worksheet, you two are so burkas. Well, is this one? So you will just references to another worksheet.
imagine now that we actually want to turn this into array constant. So this is now a formula. So this is references to this. So if you change, hear something by mistake,
it will be changed. Here is Well,
we don't want that.
So we're going to select our array and press F two, which will turn us into edit moat.
And then we will press F nine, which will recalculate
the formulas and put them in as Constance.
You see this? We now got an array.
And now when we press control shift, enter,
we got an array which again behaves like an array. So we cannot change
just one so
cannot do that by accident.
very good thing to do.
And if you want
to leave your data unchangeable
or not accidentally changeable
Now, I also if you don't need the original data,
you can know simply
select the array.
His control X
go, Pete here
and now you have the selection, which is the road data, which is now
constant, which is very nice. If you want to preserve your
decorative data from accidental change.
Now let's move to the next example.
So this is very simple calculation. Some students are doing measurements there. They have a path. The object is moving there, measuring time in orderto determined the every speed.
So we know that speed is equal path over time.
So we're going to just do this calculation here
and say that speed is equal
and of course, we're going to get their message. Now. If you want this to expand everywhere,
we'll just drag this little
black box and get it here.
And now if I'm a teacher and I want to give this to my students, I don't want them to be able to mess with this
because they will do that on purpose. But because they will do that by accident, because maybe they don't know how to use Excel Well,
so in order to do it as a Raymond formula multi celery formula, it's just going to delete this.
And then we're going to leave these self selected because we want all the formulas in these cells
and then we're just going to say, OK, this divided by
and we're going to press control shift. Enter the usual thing, idiot of Henry, right formula.
And now when we enter here,
there were three in two,
and here we had
and here again we have the same path and it's going to be 2.2
We're going to have different results, but these cells now cannot be changed by it accident like any other. Hurry.
So this shows you the example off multi cell array formula
and the main purpose of multi celebrate formula would be to have it
protected from accidental or intentional change. Of course, if you want to protect it against intentional change, you also have to protect these cells with password the things we're going to talk about much later.
But this is something that can help you create worksheet that is going to be used by somebody else, and you want to make sure that it stays
as it was, so so that the formulas remained with their integrity.
also, the use off multi cell formulas can be
interesting for people who are creating something just for viewing,
also against accidental change. And they don't want to go into the whole process off protecting the worksheet, protecting cells with password. You just want to keep them protected from somebody accidentally pressing on the keyboard.
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