Penny Arcade Forums› Help / Advice Forum
As was foretold, we've added advertisements to the forums! If you have questions, or if you encounter any bugs, please visit this thread: https://forums.penny-arcade.com/discussion/240191/forum-advertisement-faq-and-reports-thread/

Excel Calculation Assistance [Solved!]

EncEnc A Fool with CompassionPronouns: He, Him, HisRegistered User regular
edited October 2017 in Help / Advice Forum
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

  • EncEnc 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.

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

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

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

  • TofystedethTofystedeth 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.

    steam_sig.png
  • EncEnc A Fool with Compassion Pronouns: He, Him, HisRegistered User regular
    I think I got it:

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

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

    kZKcAss.png

    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
  • OrogogusOrogogus 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.

  • Space CoyoteSpace Coyote 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.

  • OrogogusOrogogus 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
  • SmasherSmasher 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.

  • AiouaAioua 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
Sign In or Register to comment.
Penny Arcade Forums› Help / Advice Forum