grade sheet formula

[Guest]

im a teenager trying to do a project for school on excel. i need to re-do my
grade history sheet on excel. this is fine, i've done it all, and i'm fine on
that part. where i'm lost at is the points part. the thing is for every class
i have a grade. for every grade there are points (A=4, B=3, C=2, D=1, F=0).
that and also, if one of my classes is honors gifted, a bit higher in level,
you get an extra point (A=5, B=4, C=3, D=2, F=1). and if you're in an AP
class, advanced placement, it's 2 more points (A=6, etc.). what i need to
know is a formula so that whatever grade i get it automatically puts 4 or 3
etc. and then i need it to check if it's honors or AP and add the points
needed. the only formula a think that is "close" to help me, is the IF
formula, but am breaking my head on it, and it's not working. someone please
let me know if this is possible. these cells need to have a formula in order
for me to get a good grade. thanks in advance.

Ex. English II Honors AP.......B..... formula:B=3, 3+1+3=7
hope that explains a little bit, thank you!

 

[Don Guillett]

finish this formula to do what you want
=LOOKUP(B14,{"a","b"},{1,2})

 

[JE McGimpsey]

This should get you going:

=LOOKUP(B1,{"A","B","C","D","F"},{4,3,2,1,0})+COUNTIF(A1,"*Honors*")
+ 2*(COUNTIF(A1,"* AP*"))

However, I get 6, not 7: B + AP + Honors = 3 + 2 + 1 = 6. Am I
misunderstanding?

 

[Guest]

thank you both, alot, this was alot of help. though i forgot to put in a
little detail that i've tried to fix myself but no luck. no matter what class
i get an "F" in, it should always end up a "0". but in my Honors AP class
where i have an "F" i get 3 points, which is not true. can you help me fix
this? thanks again in advance, i appreciate it.

 

[JE McGimpsey]

One way:

=LOOKUP(B1,{"A","B","C","D","F "},{4,3,2,1,0}) + (B1<>"F") *
COUNTIF(A1,"*Honors*") + 2*(COUNTIF(A1,"* AP*"))

 

[Guest]

i really appreciate your help, and am sorry for asking for too much but, i'm
getting a one... algebra 2 honors.... F..... 1. can you further assist me?
thanks alot. and if it's not too much a hassle, i need another formula. if
it's a half year course, i only get half the grade. so i'm guessing a formula
that helps you see the .5 credit earned and then divides the total by 2. im
new at this and am trying to do it on my own but, no luck. thanks again!

 

[Guest]

ok i got the F=O formula working, thanks. but, any luck on the other one?

 

[JE McGimpsey]

How can one determine if the course is a half-year course?