Tip:
Highlight text to annotate it
X
Hello Students! This is Anna.
I'm going to walk you guys through the steps
for case problem 1 in tutorial 3.
So, hopefully after watching this video
everybody should get 100% on this assignment.
So, to begin it gives you a little bit of
information; it tells you that you're going
to be completing a worksheet for a professor
who's going to use Excel to calculate the final
grades for her students.
It tells you that the final score is actually
a weighted average of the scores for three
exams, and the final exam.
And then it explains to you how to calculate a
weighted average.
It tells you, you can do this by multiplying
each students exam score by the weight given to that exam,
and then totaling the results.
And it gives you an example:
It shows you four exam scores, so it tells you
if Exam 1 score was 84, Exam 2 score 80,
Exam 3 score 83, and the Final Exam score 72
that you would use a formula, that is shown below
where it says, 84 times 0.2 plus
80 times 0.2 plus 83 times 0.2 plus
72 times 0.4. So, obviously the 0.2 and
0.4 those represent the percentages or
the weight given to each Exam.
So, I'll show you how to do this when we
go through, but I'm just going to work through
the entire project, steps 1 through 13, so
you see every single bit of it and there's
no questions at the end.
Alright so to begin, it says "Step 1"
open the workbook. You can see that I
already have the workbook open here.
And then on the documentation sheet it
tells you to enter your name (excuse me)
in cell B3 and the date in cell B4.
So, I'm gonna put in my name, and the date.
And, you can use whatever format you'd like.
In step 2 it tells me, or excuse me Step 3,
it tells me to go to the First Semester Scores
worksheet, and in cell F17 enter a formula
to calculate the weighted grades.
So, I'm gonna go down here to F17,
and I'm gonna create this formula.
So, to do this I'm actually going to use
a SUM function, and you can either type it
in or use the formula tab, or use the formula
entry dialog box, which ever way you want to
do it. I'm just gonna, again, type it in.
So, I begin my formula - I type =SUM
and an open parentheses [(] and then
I'm going to actually construct a multiplication
formula inside of my sum function.
This is kind of like nesting functions, which
we'll talk about later.
So, to begin I'm going to choose the score,
which from Exam 1, and then I'm gonna put
in an asterisk [], which is the multiplication
symbol, and choose the Exam's weight.
Ok, so you can see that I have B17 times
C8, put in a comma, and I'm gonna go and
do this exact same process for the rest of
my exam scores. So, I choose the box that
represents the Exam 2 score, put in an
asterisk for times, and then choose the
Exam two weight, comma, choose cell D17
which is the Exam 3 score, times C10 which
is the Exam 3 weight, comma, E17 which is
the Final Exam score, times, C11 which is
the Final Exam weight. And then I put a close
parentheses [)] to close out my function.
And you'll see when I press enter, it calculates
the value there of all my cells, coming to an
overall score of 81.2. So once I do that I
should be able to just use my autofill to
drag this all the way down. But, before I do that
I have to do one more step, and that is that I
have to make my weights absolute so when I
use the autofill handle it maintains that same
multiplication process. So,I'm gonna double click
on my formula to see my, um, cell reference
and for each one of my exam weights I'm
going to select it and press F4 to make it
absolute. And, remember that the dollar symbol [$]
annotates that those cells are absolute.
So, now I have all my cells references as
absolute values and press enter.
And, now, I can use my fill handle and drag
this all the way through all of my scores.
And you can see that they all calculate.
And if I click on any one I can see that it
does, in fact, represent the Exam scores for
that row, and all of my weights are still multiplied
using that, again, absolute value.
Ok, so that is, um, step 4 is the auto fill.
In step 5 it asks you to use a count function
to calculate the total number of students in the
class, and we're going to put that in B5.
So, again I'm just going to type in my formula -
but you can use what ever method is necessary.
So, I'm just gonna start typing and you'll see
that I get a list of formulas that I can actually
choose from here, and if I wasn't sure exactly
which one I wanted to pick, I could click on
them and it gives me a brief summary of what
that formula does. So, you can see in COUNT
it counts the number of cells in a range that
contain numbers. Now, I could use this formula
here, because if I scroll down and see all
of my student IDs are numbers, but for my
purposes I'm actually gonna use a different
formula, which is the COUNTA function.
And, this function counts the number of cells
in a range that are not empty.
So, if any of my student IDs actually contained
a letter, or something of that nature, I could
still use this function and it would still give me
the right result because it would tell me, um,
how many cells have anything in them, whether
it's a number, or a letter, or anything else -
so, um, by default I just use this one, but
you can use either COUNT or COUNTA either one,
it's up to you.
So, I double click on it and you see it puts in
the actual formula name for me and that start
parentheses [(] to get it going, and then
here it gives me a screen tip which tells me
what values I need to actually calculate this formula
and here, um, it says mandatory I have to have
at least one value or at least one reference to count
and that optionally I can add additional values
to it, so any, um, information that's provided in
a screen tip like this, that has the square brackets
around it means that it's optional, and the
little ellipsis at the end, or the three dots here
that means that you can continue to add
optional values.
So, for my value I'm actually gonna go down
here and I'm gonna select all of the range that
includes all of my Student IDs, so I'm gonna
start at A17 and go all the way down to A52.
And now, close my parentheses [)] and press enter
and you can see that I have 36 students in this class.
Um, so that completes step number five.
In, uh, step number six we're gonna go to cell D8
and we're actually gonna calculate the median
score for the first exam. So, to put in my, um,
function, again, I'm gonna type it in - and as I
start typing, again, it brings up a list of values
and I can see that there's actually a function called
MEDIAN, so that's the one that I'm gonna want
to use. Double click on it, and again I get
my, um, beginning of my function, and I can just
select the cells that I want.
So, here I'm gonna go and I'm gonna select all
of the scores for Exam 1, so I"m gonna start at
B17 and go all the way down to B53,
or 52, excuse me, and close parentheses [)]
and press enter. Now, I'm gonna show you guys
a little Excel Magic that can speed up your process.
I can do this exact same thing for the Maximum
value, which is the next step, step number 17,
where I could type in the function and select
the cells, but, because I already know that it's
going to be the exact same cell reference as
my Median function, I can actually go back over
to my Median function, double click on it, and I
can copy the cell reference. Just the cell reference,
the values that are in between the two parenthesis,
so the range of the cells and press enter.
Now, I can go over to my Maximum box - I can
type in my formula, and I'm gonna choose MAX,
and then I can paste my cell reference.
Close, parentheses [)] and press enter.
So, you can see that just saved me a lot of time, and
if I double click it I can look and see that the
cell reference matches the area that I need.
And, again, I can go over here and
do the same thing here for the Minimum.
And, for the range, what I'm gonna do is take
the difference between the maximum and
the minimum. So, use a, start out with a basic SUM
function, and then I'm gonna choose Maximum
and I'm gonna put in a minus sign and choose
minimum, close my, um, function and press enter.
So, that completes my information for Exam 1,
which is actually step 6, 7, 8 and 9.
But, only for Exam 1, and I have to do that for
Exam 2, 3, the Final Exam and the
overall calculations. So, I could walk through that
whole process again and it would be pretty quick,
but, I'm gonna show you even more Excel Magic.
So, what I'm gonna do now is I'm gonna actually
copy, or highlight all of the values here and use
my fill handle to fill those formulas down one row.
Now, obviously this is not gonna have the correct
data for all of these functions, but what I can do
is I can double click on the cell, and you'll see
that it highlights the range down here that it
corresponds to, and if I put my cursor right over
the edge of the range, you can see the, range area
turns bold, and I can actually click and drag that
range over to where it's going to correspond to
my Exam 2, and press enter. And I can do that
for each one, which is a little bit quicker than
typing it in.
And, then obviously you can see here that my
range function is still correct, it's stil
taking the difference between the maximum and
minimum values, and so, I'm just gonna do that
again for Exam 3, and you can see that I missed
it, when I went to click on it and drag it I didn't
actually get it, that's fine, I can just go back and
double-click this and then drag it to where I need
it to be. And, if at any time you forget to
press enter after you move your range, so say
you click on, you go, "OK ya, that's it, I'm done"
and you click over here and it changes something
in your value, you can always just hit the undo button
at the top and make sure that you go back
to where you were. Ok?
So, that's for my Exam 3, press enter.
So, now do Exam 4, same way, again I'm just
pressing enter after I make the change
on each one, or tab to go to the next area.
And then for my last one, I'm gonna drag it down.
Now, you'll notice that when I do that, if I click off,
you'll see that I no longer have this color
that's right here, that's alright, we're gonna fix that in a minute
but, I'm gonna go back and just go ahead and
correct my selection for the range first.
Alright, then what I'm gonna do is I'm gonna
click on this and I'm gonna look at the backround
fill so I can see which color it is, so I can see
that it is the Olive Green, Accent 3, Lighter 80%
and so, then I can just go back and select the
range that I want to color, go in here and then
choose it, now it looks exactly like it did before.
Alright, so, I'm all the way through step 10
So, only three more steps to go!
So, in step 11 it asks you to use
Conditional Formatting to highlight the top 10
scores in column F. So, I'm gonna highlight
everything in column F, obviously not the entire
column, but everything in the grade part
of the column. Then, I'm gonna go over to
Conditional Formatting, and go to Top/Bottom Rules
and choose Top 10 Items... and then if I wanted
to I could change the fill options here and I could
change how many, but in this case, again, it's asking
me for the top 10 scores, and it tells me to use a
light red fill with dark red text, which is the default
so, I just press OK, and I'm done with that step.
In step 12, it tells me to put in a page break at
cell A14, so I'm gonna click on cell A14 and
I'm gonna go to page layout, and then where it
says, in the page layout group, under breaks
click on that, and then choose: Insert Page Break.
And you can see, I now have a page break here.
And, it wants me to repeat the first three rows of
the worksheet in any print out, so, I'm gonna go
up here to where it says Print Titles and then one of
my options in the sheet tab is to choose
Rows to Repeat at the top, so I'm gonna click on
this little box here, which is going to collapse my
dialog box and allow me to navigate in my worksheet
to select the rows that I want.
So, I'm just gonna pick the first three rows here,
Alright. And then I'm gonna expand my dialog box out
excuse me, my dialog box back out by clicking on
the icon again. And, then it also asks me
to verify that the worksheet is in portrait orientation
so, I can actually print preview from here also
and I can see that, in fact, my worksheet is
in portrait orientation and if I click to the next
sheet, you can see that my Chemistry 303,
First Semester Scores, shows on both the first and
second page. So the last step is to save it
and submit it. So, again you can just click on
the save icon and save it to where ever you have
your data files, or your homework saved and
then submit it in Blackboard. So, I hope that
shows you how to get, complete, the Case 1
problem for Tutorial 3, should take you about
fifteen, maybe twenty minutes to complete the
entire project. I hope everyone get a 100%,
let me know if you have any questions.