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?

 

[VoG]

You could set up a table of codes and their numeric equivalents then use VLOOKUP http://www.contextures.com/xlFunctions02.html

 

[MathDreamer]

I see what are saying.

But, I have a couple of questions.

The "L" would not just be 50%. It would be 50% of the total number of points the assignment is worth. Not all assignments are worth the same number of points. Can I do this with VLookup?

Also, I would be totaling up the earned points for all assignments divided points possible for all assignments. Wouldn't the formula just ignore "L"?
Wouldn't it ignore the VLookup table?

 

[VoG]

You can use the results of a VLOOKUP in another formula. Simple example

Excel Workbook
	A	B	C	D	E	F	G
1	Scores	Code	weighted score			l	0.5
2	7	nc	0			dns	0
3	8	l	4			nc	0
4	5	l	2.5			inc	0.5
5	4	nc	0
6	1	inc	0.5
7	6	dns	0
Sheet2

 

[buxtongt]

Having the L show up as the grade should be fine, it is going to represent a % just like any other number would. You would just have to use the vlookup to pull the 50% number when you calculate the total grade (out to the right, I would assume). This could be done by an if statement along the lines of:

=if(a1="L",vlookup(a1,range,column,FALSE)*possible.points,a1*possible.points)+other.grades

Hope this helps.

 

[MathDreamer]

I am very impressed by the help from all of you. Screenshots and everything. Wow! I appreciate it.

I still have a few barriers though. I don't want to create extra columns. Having a weighted column for every assignment would be hard to work with after several assignments. I don't need to see the weighted score. I need the code to work no matter where it is entered in the table. The total points would vary with each assignment. I want to insert a screenshot, but I don't know how to embed an image in this post without it being an url. So, I am going to try typing it.

Assignment Name HW1 HW2 HW3 %
Total Points 2 4 2
Student1 2 nc 2 See note below for formula
Student2 inc 4 See note below for formula
Student3 2 4 2 See note below for formula

sum(Studentscores)/sumif(Only add total points for entries with scores)
As it stands, the formula only works if I have numbers or blanks in the entries.
Notes:
1) Student2 was absent for HW3 so his total points are different from everyone else. The sumif formula takes care of that. He is not penalized for being absent.
2)Student2 for HW1. I need the inc to calculate 50% of 2 points in this case and be used in the sum formula.

 

[MikeWx]

Is this similar to what you are looking for?

Sheet1


<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 96px"><COL style="WIDTH: 36px"><COL style="WIDTH: 36px"><COL style="WIDTH: 36px"><COL style="WIDTH: 36px"><COL style="WIDTH: 36px"><COL style="WIDTH: 36px"><COL style="WIDTH: 36px"><COL style="WIDTH: 36px"><COL style="WIDTH: 36px"><COL style="WIDTH: 43px"><COL style="WIDTH: 59px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD><TD>I</TD><TD>J</TD><TD>K</TD><TD>L</TD><TD>M</TD><TD>N</TD><TD>O</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD>NAME</TD><TD>HW1</TD><TD>HW2</TD><TD>HW3</TD><TD>HW4</TD><TD>HW5</TD><TD>HW6</TD><TD>HW7</TD><TD>HW8</TD><TD>HW9</TD><TD>HW10</TD><TD>Average</TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD>TOTAL POINTS</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">4</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">6</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD>DNS</TD><TD style="TEXT-ALIGN: right">0%</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD>Student1</TD><TD style="TEXT-ALIGN: right">2</TD><TD>NC</TD><TD style="TEXT-ALIGN: right">2</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: right">1.33</TD><TD></TD><TD>INC</TD><TD style="TEXT-ALIGN: right">50%</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD>Student2</TD><TD>INC</TD><TD style="TEXT-ALIGN: right">4</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: right">2.5</TD><TD></TD><TD>L</TD><TD style="TEXT-ALIGN: right">50%</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD>Student3</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">4</TD><TD style="TEXT-ALIGN: right">2</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: right">2.67</TD><TD></TD><TD>NC</TD><TD style="TEXT-ALIGN: right">0%</TD></TR></TBODY></TABLE>


Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

The forumla in Cell L3 is an array formula (Press CONTROL SHIFT ENTER after entering it) and is listed below. Copy it down in Column L for each student.

=ROUND((SUMPRODUCT(IFERROR(LOOKUP(B3:K3,$N$2:$N$5,$O$2:$O$5),0),$B$2:$K$2)+SUM(B3:K3))/COUNTA(B3:K3),2)

You can enlarge the table in N2 through O5 for additional values, but make sure the values in column N are sorted in ascending order.

Mike

 

[MathDreamer]

The table is exactly what I am looking for. I need to analyze the formulas to make sure I understand them. I am just starting to learn to use the mor e powerful functions. Thanks so much.

 

[MathDreamer]

I changed it a lit bit. I made it
=(SUMPRODUCT(IFERROR(LOOKUP(B3:K3,$N$2:$N$5,$O$2:$O$5),0),$B$2:$K$2)+SUM(B3:K3))/SUMIF(B3:K3,"<>",$B$2:$K$3)

The sumif makes it so that I don't add up "Total Points" for students with empty cells. Say a student transferred into class or never did the HW and it was my fault. This way I don't penalize him/her. I took the rounding out. I like the more exact number. I formatted the average column to be a percent with no decimals.

I am still thinking about the Lookup and sumproduct formula. It works, but I am still trying to figure out why it works. I need to read more about Lookup and sumproduct as it pertains to arrays.

 

[MathDreamer]

We use Excel 2003 at work. Is there a work around for the IfError function. That function does not exists in Excel 2003.