New to Excel, have to do a grade sheet!

[Jean S. Barto]

Hello folks--

I have only very limited experience with Excel (just opening documents I've
gotten by e-mail, and have never made an Excel document before myself), and
I have a course assignment for a *intro to computers* class. I have to make
up a grade sheet where different grade types are weighted differently. For
example, the Quiz grades are weighted 25%, Unit Tests 40%, and the Final
Exam 35%. Also, workbook grades are added into the unit test grades.

There are grades listed for 6 people, and of course there are a number of
quiz, unit test, and workbook grades for each person, along with one final
exam grade for each person.

I missed part of the class due to a prior appointment, and so I missed the
part about how to set up the spreadsheet and figuring out the *equation* to
figure out the final grade for each student. I'm asking here because other
folks I've asked about it today haven't been very helpful. I spoke to the
instructor earlier in the week and told her I would work on the spreadsheet
this weekend and see her Monday if I was having trouble.

Does anyone here on the newsgroups know how to do this, or where I would
look in Excel to figure it out? I'd really like to have something to take
to the instructor Monday for her to check.

I have both a Dell laptop PC and an iMac. The Dell has Excel 2000, the iMac
I think has Excel 2004for Mac OS X.

Thanks in advance,

Jean in VA

 

[Jim Gordon MVP]

Hi,

Since Google exists, give it a try on the expression:
weighted average calculation

-Jim

 

[Fabian]

I'm a bit busy this weekend, but I can probably have your homework done
for you by tuesday. Is that any good?

As a hint, look for weighted averages.


--
--
Fabian
Visit my website often and for long periods!
http://www.lajzar.co.uk


Jean S. Barto hu kiteb:

 

[Earl Kiosterud]

Jean,

I think you're asking for a composite grade for each student, factoring Quiz
@ 25% etc. If so, and if for a particular student the quiz grade were in
B2, the unit tests grade in C2, and the final exam in D2, the following
formula would give you that score:

=B2*0.25+C2*0.4 + D2*0.35

This presumes that all scores are on the same scale, such as 0-100. This
formula might be in E2, and could be copied down for other students, using
the fill handle.

 

[Ken Wright]

Hi Jean - We don't generally tend to do homework on here, although we will
usually advise you how to find appropriate resources. That having been said, I
honestly feel that your note sounds like you are genuinely interested in
learning how to do this, ( And have also been in the situation you are now :-> )
so I'll happily lay out the basic principles for you:-

As long as you understand the principle of a weighted average then it really is
just a case of translating that into a spreadsheet. With your example data, if
a student had gotten 50% for the Quiz grade, 60% for the Unit test, and 80% for
the final exam, then you would simply sum each of those but factored by the
relevant weight, eg:-

50.0% * 25.0% = 12.5%
60.0% * 40.0% = 24.0%
80.0% * 35.0% = 28.0%
===================
64.5%

Now, all you have to do is put that into a spreadsheet. Looking at an empty
sheet in Excel you will see

A B C D E F
1
2
3
4

so if you start putting the raw data from your table into this sheet you will
get something like (Either literally type 50.0% in cell A1 for example, or
format all the cells you will be using as percentage with 1 decimal place and
then just type 50 in cell A1)

A B C D
1 50.0% 25.0%
2 60.0% 40.0%
3 80.0% 35.0%

All you now need to do is to replicate the calculations that you need, so in
cell C1 what you are looking for is the sum of A1 * B1, so literally just put
that in, but precede it with an = sign to tell Excel that you are entering a
formula, such that in C1 you will put

=A1*B1

Now you could just type this in every cell but change the references, eg =A2*B2,
=A3*B3 and so on, but Excel has features called 'copy and paste' and 'relative
referencing' to save you having to do all that, and what this really means is
that you can copy the cell C1 and paste it into C2 and C3 together and it will
make the formula relative to the original, so it will automatically adjust the 1
to be 2 and 3 in the respective cells. So select cell C1 and do Edit / Copy
(From the menu at the top of the sheet), now select cells C2:C3 by clicking on
cell C2 and dragging the cursor down to C3, and now do Edit / Paste.

This should give you the factored values in C1:C3, so all you now need to do is
add them all up, or in Excel speak you need to SUM them. Therefore, in cell C4
you can either put =SUM(C1:C3) which will sum all the values from C1 to C3, OR
you can select cell C4 and hit the shortcut key combination ALT+= (That is ALT
plus the = sign) and it will do it automatically, or you could type =SUM( and
use the mouse to select C1:C3, type the last ) and then hit enter, but all roads
to lead to Rome here.

You should now see your weighted average in cell C4.

Calculations in Excel are no different to those on paper, and so if I wanted to
add the values in say E1 and E5 I could simply put

=E1+E5

If i wanted that multiplied by the value in F1 then I would enclose the original
in brackets to ensure that the whole thing got multiplied and it would become

=(E1+E5)*F1 or even =F1*(E1+E5)

If I wanted that divided by say 3, then I would use

=((E1+E5)*F1)/3 or even =(F1*(E1+E5))/3 and so on and so on.


The real trick is to have the spreadsheet do what you already know how to do.
If you don't know the principle of a weighted average then you are likely to
blindly trust what the spreadsheet tells you and that is a bad thing to do. The
spreadsheet will only do what you tell it, and the slightest error on your part
can cause wildly unpredicatable results. Now I don't need to do all the maths
to know that the answer I would have expected would be circa 60%-65%, so
regardless of it being a spreadsheet, had the answer been much different to that
then I would know I had made a mistake somewhere. That last little sanity check
can save you a LOT of grief, but unfortunately is all too rare in this day and
age.

 

[Anese]

Just wanted to thank you for the post. I was stranded at the same
function/formula. Simple but not easy. I will keep trying. Got it from
your post though.