IF and VLOOKUP Functions

[Guest]

I am trying to write a calculation that will give me a letter grade or an F (fail) using IF and VLOOKUP and the data below
Each module (lab %, project %, and exam %) must be compared to the % grading table below. This is where VLOOKUP must recognize that each modul
must be passed. Only one student, Bart Simpson, receives an "F" final mark. I can get a letter grade but it is not reading true. My calculatio
is giving Bart Simpson a "B" in stead of failing as he failed the lab % module. If you can help me I would muchly appreciate it. Here's m
formula =IF(G8>=F19,IF(I8>=F20,IF(K8>=F21,VLOOKUP(L8,Grade_Table,2),F))) Where did I go wrong
Hope someone can help

Underwater Basketweaving 572.
Course Marks

ID name lab 1 lab 2 lab 3 lab % project project % exam exam % class % final mar
285532 Walter Cronkite 20 22 24 73% 76 76% 29 48% 64% #NAME
834441 Albert Einstein 29 26 30 94% 92 92% 59 98% 95%
175642 Larry King 25 24 29 87% 53 53% 51 85% 76%
129312 Bart Simpson 12 7 0 21% 99 99% 60 100% 76%
692711 Jill Smith 24 20 19 70% 87 87% 58 97% 86%
349875 Martha Stewart 15 16 18 54% 95 95% 44 73% 74%
235198 Sigourney Weaver 21 29 23 81% 61 61% 55 92% 79%
maximum 30 30 30 100 60

Grading Grade Table
Labs 30% 0%
Project 30% 50%
Exam 40% 65%
75%
85%
note:
Students must pass each component to pass the course


Formula for lab % SUM(D11:E11:F11)/90
Formula for project % SUM(H11)/100
Formula for exam % SUM(J11)/60
Formula for class % SUM((G11*0.3)+(I11*0.3)+(K11*0.4))
Formula for Final Mark

 

[Max]

If I have read your grading logic & intent correctly,
a suggested solution might be along these lines:

Layout

In Sheet2
------------
You have in A1:B5 (named as: Grade_Table)

0% F
50% D
65% C
75% B
85% A

(I've corrected the table to reflect "F" in the 1st row, ie assumed grade%
<50% as "F", ie Fail.)

In Sheet1
-----------

Col A : ID
Col B : name1
Col C : name2
Col D : lab1
Col E : lab2
Col F : lab3
Col G : lab% (calculated)
Col H : project
Col I : project% (calculated)
Col J : exam
Col K : exam% (calculated)
Col L : class% (calculated)
Col M : final mark (calculated)
Col N : Lab Grade (calculated)
Col O : Project Grade (calculated)
Col P : Exam Grade (calculated)
Col Q : Class Grade (calculated)

Typical formula to put in cols G,I,K,L,M,N,O,P,Q
(all formulas to be copied down the cols)

In G2: =SUM(D2:F2)/90
In I2: =H2/100
In K2: =J2/60
In L2: =SUM((G2*0.3)+(I2*0.3)+(K2*0.4))

(the above formulae is basically what you stated in your post,
but with some suggested "corrections" made)

In M2: =IF(OR(N2="F",O2="F",P2="F"),"F",Q2)

In N2: =VLOOKUP(G2,Grade_Table,2,TRUE)
In O2: =VLOOKUP(I2,Grade_Table,2,TRUE)
In P2: =VLOOKUP(K2,Grade_Table,2,TRUE)
In Q2: =VLOOKUP(L2,Grade_Table,2,TRUE)

Col M will return the "final mark" which is desired

And if you want to collapse the formulae in cols N - Q into col M
(I'd suggest against this, though, albeit collapsing allows you to
do away with cols N-Q altogether)

Put in M2:
=IF(OR(VLOOKUP(G2,Grade_Table,2,TRUE)="F",VLOOKUP(I2,Grade_Table,2,TRUE)="F"
,VLOOKUP(K2,Grade_Table,2,TRUE)="F"),"F",VLOOKUP(L2,Grade_Table,2,TRUE))

Copy down col M

--
hth
Max
-----------------------------------------
Please reply in newsgroup

Use xdemechanik
<at>yahoo<dot>com
for email
-------------------------------------------

I am trying to write a calculation that will give me a letter grade or an

F (fail) using IF and VLOOKUP and the data below.

Each module (lab %, project %, and exam %) must be compared to the %

grading table below. This is where VLOOKUP must recognize that each module

must be passed. Only one student, Bart Simpson, receives an "F" final

mark. I can get a letter grade but it is not reading true. My calculation

is giving Bart Simpson a "B" in stead of failing as he failed the lab %

module. If you can help me I would muchly appreciate it. Here's my