How to find 2nd Lowest

Question asked by dudley@jps.net on Oct 27, 2015
Latest reply on Oct 27, 2015 by dudley@jps.net

This question is about contest scoring for annual awards.  Nine monthly contests are held throughout the year.  Each contest has it's individual winner.  For season standings, we exclude the individuals two lowest scores.  Annual awards are based upon the best seven out of nine contests.  This allows for a bad day and/or missed contest.

I use the "Min" function will identify the lowest score out of the 9.  That number is a calculated result in the Field "Min 1".  I currently use a very  elaborate series of comparisons (shown below)  to identify the 2nd lowest number which is stored in Field "Min 2".  The Best 7 is then determined by a summary of all nine scores minus Min 1 minus Min2.  (Sum9 - Min 1 - Min 2)

It works but it ain't pretty.  I wondering if anyone has a simpler way to find "Min 2"?

If (Contests Flown < 9 ; 0 ;

Max(

Min(Apr;May;Jun;Jul;Aug;Sep;Oct;Nov) ;

Min(Mar;May;Jun;Jul;Aug;Sep;Oct;Nov) ;

Min(Mar;Apr;Jun;Jul;Aug;Sep;Oct;Nov) ;

Min(Mar;Apr;May;Jul;Aug;Sep;Oct;Nov) ;

Min(Mar;Apr;May;Jun;Aug;Sep;Oct;Nov) ;

Min(Mar;Apr;May;Jun;Jul;Sep;Oct;Nov) ;

Min(Mar;Apr;May;Jun;Jul;Aug;Oct;Nov) ;

Min(Mar;Apr;May;Jun;Jul;Aug;Sep;Nov) ;

Min(Mar;Apr;May;Jun;Jul;Aug;Sep;Oct)

)

)

Dudley