Welcome back. In this video, you'll learn more about formulas, as we recreate the following spreadsheet. Let's take a little bit. Look at this. So you can not only with the
Imaginary course we've got five students here we've got some exams exam wanted to give to a final four do regular average do an average on the way to the
Final is worth twice as much as exam one or two and then we'll go ahead and even do a formula where to get a drop the lowest exam score. So in this case I got 170 80
I dropped the lowest exam score 70 I'd have 189 point then we'll do some more review of average min and max on the number students past and number students failing and let's get started. So the first step is to do is go ahead and open up Microsoft Excel.
And let me just start entering some data here. So at the very top up there, that's going to go through G one, I'm going to type information systems.
1003 I'm now going to highlight that and go through g one another head my Merchant Center and then maybe I'll come along and add a full color.
And I'll put a nice thick, thick parks announced
Okay, let me go ahead and get a little bit larger font on everything, because if I go up here at about
18
And I'll have to probably readjust the width of these cells as we go along here.
Okay, so next thing we need to do is type in a student
Type
Every
Down precedent her moment type in went to funnel.
And said, Come along, come over here, we can resize it so that here. I'm in the G to on the type of average what the open down. Press Enter dropping
Lowest
Okay, let's go ahead and resize. It's just a little bit here.
We're room there a second all fit on one one and the same thing here.
Okay, yeah, maybe I'll kick it off center.
If I take a look here. There was a big border line here that will put this one over here on the left his phone. So I've got this information selected. And now I'm going to come up here and choose a Nick Bottom order. Okay, time to enter a few students will then or Adams.
Anchor
Class.
Walker and now we can average that are average
Minimum
Maximum
Number since passing
Students
Okay, we're starting ready to actually read the under some exam scores and they'll be ready to start working on formulas. So let's just sound interesting scores here 190 1960 and living exam to seven to seven to
160 and 80 Gemma formal
Maybe
Someday 48 and nine days. Okay, that is all the data that is kind of a recall it hard coded manually entered. Everything else is going to be computed. You're saying
Foremost and we'll go ahead and start right off here without the average Australia sort of ways of doing that. So one way. What we could do is from a sibling. This is just simply going to be a regular average
And I could go along and go okay people's some I can specify the individual cells. The three
C three and d three and then simply divide that by three.
Another way I know typically I would grab this and drag on down. Let me show you a couple other ways I could specify a range. I could go some p three colon D3 and divide that by three.
Hoops
to rekindle the red cells here. It's going to be a form. So if I'm
Coming down here. I'm now on the forums.
And yet, one other way to do it. I should be just maybe I'll just delete that. So don't mess up here. Again, another way would simply be the average function like ago coverage specify V3 Colin D through
So I'll show you three different ways to compute just a regular average this point, I'm going to end position my cursor on the film handle and track that on down.
At some point, let's go ahead and it says, I'm gonna keep on going down further me come up here. We'll go to a format. Number two decimal points and press OK.
OK, the first formal lecture tricky here. And so we are going to, let's assume the funnel is worth twice as much as example or exam to it sounds like Trump chickie chickie but it's really not. We're just going to go some
Will take a look at to be three.
And we'll go see three. Now we're going to do a D3 twice.
And then now we're divided by four. So in this case, the finals were twice as much as the other exam. So the three youngest and one C three D3 twice and divide that by
It looks like there's about a 2.5. Okay, so let me go ahead and drag that on now.
And maybe I'll go ahead and format that two decimal points and wine that
Okay, the next one, what's going to happen as we're going to drop the lowest exam scores again as I mentioned earlier, we're going to hundred 7980 will drop the 70 that have 180 this would add up to be 90
Well, how we're going to do that Microsoft Excel. We're going to simply go some and we're going to add up V3.
23
So we're starting with that, we're going to add up these three numbers.
Now, what we're gonna do is we're going to subtract using them in the smallest number two, three colon. The true
Okay, now what I want to do is divide all of this by two.
OK.
So again, what we did was we added them all up against the trial attracted the smallest using the men function and then what was left with two of them to this point, I can go ahead and check that on down as well.
In this number. So the pretty good here. Okay. For the average and it's going to find the average of all these on exam one. I could go average
Now I'm going to go be three colon be seven.
The point, the man say range. The three would be seven.
And the max.
3.7
Okay, at this point about we're doing CMT I repeat that, or I can just highlight these three songs and I can actually just drag right on over this one here, I will have to format. Again, if I were to go back to two decimal points and press OK.
OK, so let's look in a really good there. The only thing we've got left to do is the number of students passing if I've got one more, just go ahead and use
Just the regular average determine who's passed since I've got 1234 students passing. I'm gonna go sit in the same
Like a cash business technology thing about seven is passing below 70 would be on. So I've got four cents passing in one gallon
until maybe I'll have like this information on Merchant Center it online. That's right. Now repeat that this Merchant Center and aligned to arrive. Okay, so I'm gonna do is I'm going to count of how many of these numbers are greater than or equal to 70 simply go count if
Take a three.
Colon, he's seven. That's my range next year or the next comes the criteria. And that's going to be there some quotes Gregory go to 70 and close my parentheses and sure enough, I've got 1234 students
Hanson or repeat this process kalkan
Same range in three
Seven.
Now no differences rather being greater recorder 70 it's going to be less than seven.
Okay, I've got one students. So again, the big power. We don't clip them on. It's just so all my data is currently displaying. Let's take a look at Moda. Maybe there was, for some reason, a part two on the final. We can see the student
And I'm going to change this. And by doing so, we have 12345678 I'm going to change in large enough to make sure that this that the middle of this passage. So by changing that from a 40 let's say to an ad
Let's go.
Take a look here.
Never students passing
Okay, I need to give you a little bit higher me up to 98
Ok now we go so to get to a 98
The students got a 72 the average. That's the one I'm actually looking at what's going to look in your license. So that changing
Okay, and which point by changing that reporting to a 98 12345 it's actually now even the highest Max was going to change. I've got five cents passing in zero feeling
Well, hopefully this video you learned even a little bit more about Microsoft Excel, in particular the power of those formulas. So again, just watching this I change that back to 40
I can see the average all of this information is search engine. I guess the next video. Thanks.