Help me create a function! URGENT!

[imaTA]

Hi,
I am a teaching assistant, and my professor changed the grading policy. Previously, from a row of quizzes I was to drop the lowest one like so: (SUM()-MIN())/(COUNT()-1). Now I need to drop the lowest TWO! Can anyone help me write a function, or maybe a macro, that can accomplish this? I am trying to avoid sorting and selecting row, because that is manual and I have over 100 students.

Thank you so much!!!!

[Tommy Bak]

Hi
here is a solution using small()

=(SUM(ref)-Small(ref,1)-small(ref,2))/(count(ref)-2)

regards Tommy

[Asala42]

Hey,

The SMALL function will give you the Nth smallest value in a range. so you could likely use something like:

=(SUM(A2:A20)-MIN(A2:A20)-SMALL(A2:A20,2))/(COUNTA(A2:A20)-2)

->assuming A2:A20 contains your quiz scores (tweak where applicable).

Adam

[Asala42]

ya beat me to the draw, Tommy!

[Tommy Bak]

Yes Asala, but your explanation was also better/longer than mine
regards Tommy

[imaTA]

OH WOW! THANK YOU SO MUCH! IT WORKS PERFECTLY!!!!!!