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.
