yogi_From Table B:I Of FoosBall Scores Compute Row By Row Loser (with a numeric score of less than 10)

Google Spreadsheet   Post  #2116

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Feb-17-2017

question by Brian Bimschegler:

https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/pKWhsgqtWbw;context-place=mydiscussions
Within ARRAYFORMULA, find 2nd largest value in a row that has 7 columns

I'm looking to create a Foosball sheet for my office. We enter scores via Typeform. When a match is entered in Typeform, a new row is created at the bottom of the sheet with the appropriate scores. I'm looking to use =ARRAYFORMULA so that functions are run on any newly-created rows.

Since there are only 2 players in foosball, I need to calculate the winner and loser. The winner is easy: person with 10 points. The loser is not as simple. In theory, the Loser is the person with 0-9 points. I determined a way to calcucate this for our 3-person office (in the sheet:

=arrayformula(if(isblank(A2:A),, if(isblank(B2:B),if(C2:C<D2:D,"David","Twok"),if(isblank(C2:C),if(B2:B<D2:D,"Bim","Twok"),if(isblank(D2:D),if(B2:B<C2:C,"Bim","David"))))))

However, we are adding 4 new players. As we scale players up, IF/ELSE logic becomes exponentially unwieldy. I am not certain how to figure out the most efficient (see: not a billion =ISBLANK() and =IF() statements) way to calculate the Loser.

I was thinking about something like:

=ARRAYFORMULA(IF(ISBLANK(A2:A),,LARGE([somehow get an array of numbers in a specific row],2)))

Then somehow getting that column number, and running an HLOOKUP to get the name of the person who earned the Loser score.

I can easily work these formulas outside of an arrayformula, though my constraint of adding new rows automatically and needing formulas to run on them makes things difficult.

The sheet is in view-access here.