On Sun, 20 Sep 2009 22:41:42 -0400, "Rick Rothstein"
wrote:

My previous formula can be shortened. The UPPER function is not
necessary.

=MIN(4,INT((MAX(0,B2-11)+(B2=20))/10))+C2+3*(D2="X")+
IF(ISNA(MATCH(E2,{"E","D","C","B","A"},0)),0,
MATCH(E2,{"E","D","C","B","A"},0))

I think it can be shortened even further to this...

=MIN(4,INT((MAX(0,B2-11)+(B2=20))/10))+C2+3*(D2="X")+
MATCH(E2&" ",{" ","E ","D ","C ","B ","A "},0)-1

I like your suggestion. Also, it will return an error for an illegal entry in
column E, which I believe is preferable.
--ron