I am making a tracking sheet for a particularly tricky assignment and could use some help. Here is the gist of what I am trying to do:
I have five columns, each are a different assignment which is scored to a maximum value of 25 points. However, students may do things on these assignments to earn up to 30 or so points with the overflow going into an extra credit pool used for another purpose. What I need for tracking are two columns that calculate from a single score how many points they have out of 125 and how many bonus points they also accumulated. In essence it should look like this (only in columns instead of rows):
A1: 26/25
A2: 22/25
A3: 25/25
A4: 27/25
A5: 25/25
Course Completion: 122/125 - (because they scored 25 on all but one assignment, and lost 3 points from assignment 2)
Bonus Points: 3 - (because they scored a 26 (+1) and 27 (+2) on assignments 1 and 2)
I could calculate this manually, but the program that will be using this sheet has students in the thousands so that would be a considerable time investment over the instructors sending in their scores export from webcourses.
Thoughts on what calculations might do this for the course completion and bonus points cells?
Posts
=(IF([ASSIGNMENT1]>25,([ASSIGNMENT1]-25),0)) + (Repeat for each assignment) to calculate the bonus points, which seems to work out, my hangup is with the course completion conditional.
Column 1:
=(IF[ASSIGNMENT1]<26, [ASSIGNMENT1], 25)
=SUM((IF(N3>25,(25),N3))+ (repeat per cell)
F2:
G2:
I'm not sure if there's a better way of iterating across all those ifs
I was looking to see if there was a SUM type of function where you could add in the extra processing (ie, perform this IF on this range of cells and then add those results) but I couldn't find it
fuck up once and you break your thumb / if you're happy at all then you're god damn dumb
that's right we're on a fucked up cruise / God is dead but at least we have booze
bad things happen, no one knows why / the sun burns out and everyone dies
I might be confused; don't you want their score to be 24 (i.e., [ASSIGNMENT1])if that's the case? I think the one you came up with is basically the same thing flipped around.
Incidentally, this is a situation where I would probably use additional columns, to make it more transparent if a formula has messed up somewhere.
Adds all the values less than 25, then adds 25 times the number of results 25 or over.
I think you could do a mildly convoluted SUMIF using "<26" combined with a COUNTIF multiplied by 25. And then for the extra credit you could do a SUMIF on ">25" and subtract out the COUNTIF * 25.
EDIT: Yes, like that.
Course completion: Bonus points:
The nice thing about these formulas (in addition to being concise) is that you can add a new assignment by just changing E1 to F1.
If you want to get fancy you can also allow for distinct maximum scores by replacing all the 25s with a range of cells (say, M1:Q1) which are filled in with the max score for each respective assignment.
fuck up once and you break your thumb / if you're happy at all then you're god damn dumb
that's right we're on a fucked up cruise / God is dead but at least we have booze
bad things happen, no one knows why / the sun burns out and everyone dies