Hello! This is my first post so I haven't figured out how to do like, formatting. so bear with me.
I am trying to create a somewhat complex gradebook for use in automating some course grade calculations for my students. I am running into a hiccup at a stage in which I have codes to designate whether assignments or participation receive full, partial, or no credit (e.g. a late assignment receives 50% of the points they would have received if it had been on time.
It's a trivial exercise to create an array of the codes I will input; Where I am struggling is with creating the array of the desired values from the codes. I'm down to use the name manager if that will simplify things.
Also, because the prof is a strange fellow, I need this to be semi automatic with codes-> values, ie. if I end up adding more codes (late by an hour is 90% for example) that I don't need to go though and manually enter new criteria, but that I can just change the range of my code/value table.
In bad forum text, because I haven't figured out tables, I have a sampling of what I have and want (manually entered).
[Participation]
Code Value
P 1.0
T 0.5
U 0.0
Table 1 Arrays Codes Desired Array Values
1 2 3 4 5
Student1 P P P P P {P,P,P,P,P} {1.0,1.0,1.0,1.0,1.0}
Student2 U P T P P {U,P,T,P,P} {0.0,1.0,0.5,1.0,1.0}
Student3 P P P P P {P,P,P,P,P} {1.0,1.0,1.0,1.0,1.0}
Student4 P T P T P {P,T,P,T,P} {1.0,0.5,1.0,0.5,1.0}
Student5 P T P P P {P,T,P,P,P} {1.0,0.5,1.0,1.0,1.0}
What I've concocted so far is
=INDEX(Participation,MATCH(C11,Participation[Code],0),MATCH("Value",Participation[#Headers],0))
Which works well to catch individual cells, but I don't know how to get something that I can use on the array to produce an array of values in a single expression or cell (Which will need to be multiplied by points possible array, so yes I believe I need an array of values) Of Note here, I believe I would like a 1D array of values for each student to make the later sumproduct easier.
Anyway, any assistance is GREATLY appreciated!!
Bookmarks