Teacher - Gradebook - Assign a value to a String

[MathDreamer]

I am trying to do something that I have no idea how to approach with Excel.

I want to assign a value to a text string.
Actually, i want to assign a formula to a text string, which will be used in another formula. If a student hands in a homework late, then I want to type an "L" as his grade instead of a number grade.
The "L" would calculate 50% of his possible points for the assignment. The possible points would be listed on the top of the column.
I could just do the calculation in my head and type the earned score. But, the "L" would let me see which students have a pattern that I need to take action on. For example, if I need to call a parent.
I would have other codes also.
"dns" - 0% - Did not submit HW
"nc" - 0% - Work is unacceptable
"inc" - 50% - Incomplete.

Any suggestions?

 

[MikeWx]

The Excel 2003 version for the first student (Cell L3) is

=(SUMPRODUCT(IF(ISERROR(LOOKUP(B3:K3,$N$2:$N$5,$O$2:$O$5)),0,LOOKUP(B3:K3,$N$2:$N$5,$O$2:$O$5)),$B$2:$K$2)+SUM(B3:K3))/SUMIF(B3:K3,"<>",$B$2:$K$3)

This is also an array formula.

Mike

 

[MikeWx]

In Excel 2003, you can choose Tools, Formula Auditing, Evaluate Formula to step through the formula and see how the pieces of the formula are resolved.