HELP fixing GPA formula's

[JohnG]

This is my formula but what I should be getting for: B's, C's & D's is not
what I am getting:

=LOOKUP(D18,{"a","a-","b+","b","b-","c+","c","c-","d+","d","d-","f";4,3.7,3.3,3,2.7,2.3,2,1.7,1.3,1,0.7,0})*E18

As you can see from my spread sheet I am getting the wrongs numbers:

A 1 4
A- 1 3.7
B+ 1 3.3
B 1 3.7 (should be 3)
B- 1 3.7
C+ 1 2.3
C 1 2.7 (should be 2)
C- 1 2.7
D+ 1 1.3
D 1 1.7 (should be 1)
D- 1 1.7
F 1 0

Can someone please tell me where my formula is going wrong. Because for the
rest of the sheet there should be nothing showing if there is no grade to
enter.

 

[Toppers]

Use:

=VLOOKUP(E18,$F$1:$G$12,2,0)*F18

Data in F!:G12 as shown below

a 4
a- 3.7
b+ 3.3
b 3
b- 2.7
c+ 2.3
c+ 2
c- 1.7
d+ 1.3
d+ 1
d- 0.7
f 0

 

[Toppers]

sorry ... should be ....


=VLOOKUP(D18,$F$1:$G$12,2,0)*E18

 

[Toppers]

OR change order of data in LOOKUP to be:

a,a-,b,b-,b+ etc

 

[JohnG]

I tried changing the order as you suggested, but now I receive an error
message:

"The formula you typed contains an error." I still have the same
information just changed the order:
=LOOKUP(D18,{"a","a-","b","b-","b+","c","c-","c+","d","d-","d+","f";4,3.7,3,2.7,3.3,2,1.7,2.3,1,1,0.7,1.3,0})*E18

Can I send you a copy of the spreadsheet with coloums heading and all the
information that I have so that you can see what can be done.

 

[Toppers]

Try:

=LOOKUP(D18,{"a","a-","b","b-","b+","c","c-","c+","d","d-","d+","f"},{4,3.7,3,2.7,3.3,2,1.7,2.3,1,1,0.7,1.3,0})*E18

 

[JohnG]

I tried this formula and received a "N/A"

sorry ... should be ....


=VLOOKUP(D18,$F$1:$G$12,2,0)*E18

 

[toppers]

Not sure which formula caused the error but #N/A error is due to data. For
example, you have " b " i.e. a blank before and/or after the letter.

You could try (TRIM(D18) .... in the relevant (or all ) formula.