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/

Another excel question: best of

mtsmts Dr. Robot KingRegistered User regular
So for my grade book I have it automatically use the best of three exams.i don't recall exact formula but it basically takes the highest three point values and then divides by my summed total points value. This works when I make all the tests the same value which I stupidly did not. Is there a way to somehow link that total point value to the best of statement. Like if it uses it in the best of then use a cell for a sum? I can probably just calculate it slightly different and do it based on %of grade (since that is the same) but that would require more effort I think

camo_sig.png

Posts

  • m!ttensm!ttens Registered User regular
    I played around with this a bit and I think I came up with something that works:
    Max score	75	50	100	120	100	Average of top 3 scores
    Adam		75	26	55	92	76	=SUMPRODUCT(LARGE(B2:F2/$B$1:$F$1,{1,2,3}))/3
    Betty		57	43	84	94	88	=SUMPRODUCT(LARGE(B3:F3/$B$1:$F$1,{1,2,3}))/3
    Charlie		51	41	88	85	97	=SUMPRODUCT(LARGE(B4:F4/$B$1:$F$1,{1,2,3}))/3
    Doug		42	47	53	96	67	=SUMPRODUCT(LARGE(B5:F5/$B$1:$F$1,{1,2,3}))/3
    

    kqehfuctptax.png

    This looks at the three largest values of the students' score divided by the total value to get the grade from each exam. It then sums up those three highest grades and divides by 3 to get the average score. This is assuming that despite total point values being different, each exam is weighted to make up 33.33% of the students' final grade (with the two lowest scores for each student in my example being dropped).

  • mtsmts Dr. Robot King Registered User regular
    yea they are all weighted the same. I mean thinking about it, I could calculate it based on percent of grade first then take the 3 biggest. or even calculate it as the average of 3 largest percents

    camo_sig.png
  • mtsmts Dr. Robot King Registered User regular
    Ok, I think i came up with an easy solution

    basically
    instead of this using point values of the tests and this
    =((SUM(LARGE(BH3:BK3,{1,2,3}))))

    I calculated each test as an average rather than a point value, then used

    =((AVERAGE(LARGE(BH3:BK3,{1,2,3}))))

    This actually saves me a column since essentially I was calculating the average based on total pts of the exams anyways.

    camo_sig.png
Sign In or Register to comment.