How do I fix a GPA formula with a null value

[JohnG]

I had posted a question on how to fix a GPA formula with a null value. This
was the formula given:

=IF(D17="','",Lookup(D17,{"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})*E27

But the problem is that it is not multiplying in whole numbers for b,c,d. I
get the answers of b=7.4, c=5.4, d=3.4 so can someone please help me. Also
somewhere in the formula I have lost part of the formula because now I get an
"FALSE" statement. Can someone please help me.

Can you also send replies to: (e-mail address removed)

 

[Peo Sjoblom]

=IF(D17="","",LOOKUP(D17,{"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})*E27)

you has an extra apostrophe at the start of the IF function

to get whole numbers for b, c and d you need to change the numbers in your
hard coded formula, look at the second position of the numbers, that is for
b. If you don't want 3.7 change it to whatever you want. The same goes for
the rest

 

[Harlan Grove]

=IF(D17="","",LOOKUP(D17,{"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})*E27)

you has an extra apostrophe at the start of the IF function

to get whole numbers for b, c and d you need to change the numbers
in your hard coded formula, look at the second position of the
numbers, that is for b. If you don't want 3.7 change it to whatever
you want. The same goes for the rest ....

....

The problem not yet addressed is that LOOKUP expects its lookup row or
column to be sorted in ascending order. The text values

"a","a-","b+","b","b-","c+","c","c-",>"d+","d","d-","f"

are NOT in ascending text order. They'd need to be ordered as

"a","a-","b","b-","b+","c","c-","c+","d","d-","d+","f"

and the formula would need to be rewritten as

=IF(D17="","",LOOKUP(D17,{"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,0.7,1.3,0})*E27)

or you'd need to switch from LOOKUP to HLOOKUP.

=IF(D17="","",HLOOKUP(D17,{"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},2,0)*E27)