Removing lowest scores in data

Is column G, ID the student ID?

Can you give what you expect as the result for ID 101?

Hi, yes column G is student ID.

For student 101, I will remove 2, 2.3 and one of the 2.7 grades. Then, SUM(Grade)/SUM(Units) = 3.46. That would be the outcome i'm looking for: Student 101 final grade = 3.46

Thank you!

Edit: sorry i had removed three grades instead of two. I should have removed only 2 and 2.3. Therefore the final grade is 3.43 instead.

With student ID in column L, so that
L2 = 101

then the following goes in M2 and thus:

Please Login or Register  to view this content.

This is an array formula (to take advantage of the SMALL(IF) construction to peel out the two bottom grades from Non-Ge classes), so it must be confirmed with CTRL+SHIFT+ENTER when put entered into the cell, not just by pressing the ENTER key.

Broad strokes, what it's doing is:
[SUM(all scores for student) - (1st lowest Non-GE score for student) - (2nd lowest Non-GE score for student)] / [SUM(count of all scores for student) -2]

Note this delivers 3.094 -- a cursory examination makes me think that is the correct answer, not 3.43 or 3.46 as given.

Hi Ben,

I think we do need the OP to explain how they come to 3.43 and the only thing I'm not sure of on your formula is the last part where you divide by 16. The 2.3 and 2 that are removed have a unit of 1 which seems like it should be removed from the sum of unit, but what if one of those two numbers to be removed has a unit of say 0.5. At that point maybe the Sum of Unit would be minus 1.5???

@kophanz,

SUM(Grade)/SUM(Units) = 3.43

Can you tell us what you have for Sum(Grade) and what do you have for Sum(Units)?

uhhhh

I was completely ignoring the units entirely, LOL; if we're going there (and looking again I think you're right to bring 'em up), then yeah we need follow up from OP on how to handle them.

How about this!

Custom sort your data by Grade (Smallest to Largest) then by Mapping (Z to A).

I put some new formulas in column K.

Place you cursor on K2 then evaluate formula to see what's happening...

Evaluate Formula

• Click on the cell with the formula >> Ribbon >> Formulas >> Formula Auditing >> Evaluate Formula

hi there. with jeffrey's method, you can add in a COUNTIFS to count how many non-GEs have zeroes. in K2:
=SUMIF(G:G,J1,E:E)-SUM(OFFSET($G$1,MATCH(J1,G:G,0)-1+COUNTIFS(G:G,J1,C:C,"Non-GE",E:E,0),-2,2))

in K3:
=SUMIF(G:G,J1,F:F)-SUM(OFFSET($G$1,MATCH(J1,G:G,0)-1+COUNTIFS(G:G,J1,C:C,"Non-GE",E:E,0),-1,2))

or if data is always unsorted, this array formula might work in K2:
=SUMIF(G:G,K1,E:E)-SUM(SMALL(IF($G$2:$G$109=K1,IF($C$2:$C$109="Non-GE",IF($E$2:$E$19>0,$E$2:$E$109))),{1,2}))
K3:

Formula:

Please Login or Register  to view this content.

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

or if you don't need so many breakdown, this monster will give a Grade Sum divided by units sum:

Formula:

Please Login or Register  to view this content.

Originally Posted by benishiryo

......or if you don't need so many breakdown, this monster will give a Grade Sum divided by units sum:

Formula:

Please Login or Register  to view this content.

Some of the ranges are incorrect in this formula. Instances of $E$2:$E$19 should, of course, be $E$2:$E$109.......but otherwise this works for me

My suggestion would be to leave the data unsorted but add a helper column which would show an "N" on rows to be excluded. That way you can explicitly see which rows are excluded and manually adjust if required.

To do that you can use this formula in H2 copied down

=IF(OR(E2=0,C2="GE",COUNTIFS(G:G,G2,E:E,">0",C:C,"Non-GE",E:E,"<"&E2)+COUNTIFS(G$2:G2,G2,E$2:E2,">0",C$2:C2,"Non-GE",E$2:E2,E2)>2),"","N")

Then to get the Final Grade for ID in J2 you can use this simple formula

=SUMIFS(E:E,G:G,J2,H:H,"")/SUMIFS(F:F,G:G,J2,H:H,"")

See attached where I also added conditional formatting to mark the excluded rows

Note: using this method if there are ties for lowest non-GE, non-zero scores then this will exclude the first based on position in the data, e.g. if the lowest non-GE, non-zero scores for one specific ID are 2.3, 2.7 and 2.7 then first 2.7 (positionally) will be excluded. If it's possible for the units to be different for each of the 2.7s (e.g. one is 0.5 and the other is 1) then it makes a difference to the Final Grade which one is excluded. If you want to have a rule for that, e.g. always include the higher units when there's a tie, then you could change my helper column formula to accommodate that.

We are happy to help. Best wishes for the rest of your project!