Options

Excel Calculation Assistance [Solved!]

A Fool with CompassionPronouns: He, Him, HisRegistered User regular
edited October 2017
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?

Enc on

Posts

• Options
A Fool with Compassion Pronouns: He, Him, HisRegistered User regular
Right now, I'm working from an idea for the bonus points calculation using if then statements, such as:

=(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.

• Options
San DiegoRegistered User regular
Do you mean something like this?

Column 1:
=(IF[ASSIGNMENT1]<26, [ASSIGNMENT1], 25)

• Options
A Fool with Compassion Pronouns: He, Him, HisRegistered User regular
But what if they have a 24?

• Options
Registered User regular
When you sum up the cells use
Sum(min(a1,25)|min(b1|25)...min(e1|25))

Sum can also take a range of cells, but i haven't yet figured out a way to apply the min() to each item in the range instead of as a whole.

• Options
A Fool with Compassion Pronouns: He, Him, HisRegistered User regular
I think I got it:

=SUM((IF(N3>25,(25),N3))+ (repeat per cell)

• Options
Ora Occidens Ora OptimaRegistered User regular
I brute forced it

F2:
=IF(A2>25,25,A2)+IF(B2>25,25,B2)+IF(C2>25,25,C2)+IF(D2>25,25,D2)+IF(E2>25,25,E2)


G2:
=IF(A2>25,A2-25,0)+IF(B2>25,B2-25,0)+IF(C2>25,C2-25,0)+IF(D2>25,D2-25,0)+IF(E2>25,E2-25,0)


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

life's a game that you're bound to lose / like using a hammer to pound in screws
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
• Options
San DiegoRegistered User regular
Enc wrote: »
But what if they have a 24?

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.

• Options
Registered User regular
=SUMIF(A1:A5,"<25")+(COUNTIF(A1:A5,">=25")*25)


Adds all the values less than 25, then adds 25 times the number of results 25 or over.

• Options
San DiegoRegistered User regular
edited October 2017
Aioua wrote: »
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

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.

Orogogus on
• Options
Starting to get dizzy Registered User regular
An alternate way would be

Course completion:
=ARRAYFORMULA(SUM(IF(A1:E1>25, 25, A1:E1)))

Bonus points:
=ARRAYFORMULA(SUM(IF(A1:E1>25, A1:E1 - 25, 0)))


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.

• Options
Ora Occidens Ora OptimaRegistered User regular
arrayformula! That's the thing I was hoping existed but had no idea if it did!

life's a game that you're bound to lose / like using a hammer to pound in screws
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