Drop Dynamic Lowest Grade(s)

[tsroque]

Is there a way to state how many grades I want dropped? Building a spreadsheet to track my grades and each professor drops different quantities (or none) of grades. I have this formula but it assumes I know how many each time. I would like to share the spreadsheet with the assumption that user doesn't know how to use Excel like I do.

=SUM(B2:H2)-SMALL(B2:H2,1)-SMALL(B2:H2,2)

Thank you!!!

 

[GraH]

If you have an input cell, called 'drop_score', it might be small( range, sequence(drop_score) ).
If you are on 365?
I could not detect it from your profile (it does not show up on the cell phone app?)

 

[tsroque]

If you have an input cell, called 'drop_score', it might be small( range, sequence(drop_score) ).
If you are on 365?
I could not detect it from your profile (it does not show up on the cell phone app?)

Hi!
I used =sum(range)-small(range, 1 * (drop_score)) and it subtracts only the lowest score and not 2 or 3 or 4...whatever number I have in drop_score. Did I type it incorrectly?

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[GraH]

Hi!
I used =sum(range)-small(range, 1 * (drop_score)) and it subtracts only the lowest score and not 2 or 3 or 4...whatever number I have in drop_score. Did I type it incorrectly?

Not exactly what I meant. First two options are possible if you are on 365. Second one works even if scores to drop is zero. The first one will yield an error. This can be fixed with iferror. The third one should work on older versions (2010 and later).

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1		Grade 1	Grade 2	Grade 3	Grade 4	Grade 5	Grade 6	Grade 7	Total (without Dropped Scores)			Drop Bottom Scores	3
2		8	7	5	8	6	4	9	32
3									32
4									32
5
6
Sheet1

Cell Formulas
Range	Formula
I2	I2	=SUM(B2:H3)-SUM(SMALL(B2:H2,SEQUENCE(Drop_Bottom_Scores)))
I3	I3	=SUM(LARGE(B2:H2,SEQUENCE((COUNTA(B2:H2)-Drop_Bottom_Scores))))
I4	I4	=SUMPRODUCT((B2:H2)*((RANK.EQ(B2:H2,B2:H2,1)>Drop_Bottom_Scores)*1))

Named Ranges
Name	Refers To	Cells
Drop_Bottom_Scores	=Sheet1!$M$1	I2:I4

 

[tsroque]

I was going for the 3rd option since I'm making this under the assumption it could be shared to someone with an older version. #2 works great but #3 sums up to ZERO if all the remaining grades are the same.

Thank you!!!

 

[GraH]

but #3 sums up to ZERO if all the remaining grades are the same.

Oups, indeed did not anticipate that scenario.
Maybe in this case the legacy rank would work. Did not test it.