Modifying Formula

[Kimmer]

I still have not gotten this to work. My book says Modify the formula in
column E of each section worksheet so the weight applied to each exam in
computing the overall student score is based on data obtained from the range
B10:B12 in the Grading Criteria worksheet. I tried what you told me to Tom
but it does not work. It is highlighting the B10:B12 cells in the section
worksheet not in the grading criteria worksheet.
The orininal formula in column E of the section worksheets was not a vlookup
formula. This book has been very hard to follow, obviously or I wouldnt need
to come to another website to find answers.
First Row and Columns in the first section worksheet look like this
StudentID Exam1 Exam2 Final Overall Grade
----------- 100 93 79 87.75 B
this was putting in the formula=SUM(B2*0.25+C2*0.25+D2*0.50) in the overall
column. Now they are asking the above. Which in the Grading Criteria
worksheet.
you got the original info from Exam Average Grade
0 F
50 D
60 C
75 B
90 A
Then we had to put this in and this is the cells A9:B12
Exam Type Weight
Exam1 15%
Exam2 25%
Final 60%
this is where they want to get the weights. This is what I am having the
problem with. When I try to change the formula in Column E it picks up
B10:B12 from the other worksheets not this here with the 15%,25% 60%. I get
some crazy number and it makes the Grade an A. It would be a lower B than
what the 87.75 overall grade is now. I just dont understand what they want
us to do. If the original formula had been a vlookup maybe it would work the
way it is asking. The book I am using is Microsoft Office Excel 2003 Second
Edition. New Perspectives.
thanks to anyone who has any ideas

 

[Per Jessen]

Hi

With grades in B22, in the grading Criteria sheet we got weithts in
B10:B12 and exam average and corresponding grades in A2:B5 and weights
formatted as %, the formula for the overall score in E2 can look like this:

=(B2*'Grading Criteria'!B10)+(C2*'Grading Criteria'!B11)+(D2*'Grading
Criteria'!B12)

To find the corresponding grade, use this formula in F2:

=OFFSET('Grading Criteria'!A1,MATCH(E2,'Grading Criteria'!A2:A6),1)

Be sure to use absolute references to numbers in Grading Criteria sheet if
you want to copy formulas down.

Best regards,
Per