CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formulagoogle-sheets-query
Radek
Radek

Reputation: 23

Google Sheet formula to find the minimal sum of pairs in array

I'm looking for solution for my problem. I have a sheet to summarize lap times for some competition. We make 3 laps in each qualification. We are qualifying to finals by 2 best laps one after another. So we sum first and second lap or second and third lap and then choose the smallest one sum. I've managed to get array of pairs and filter out empty cells (run not finished). Number of pairs may vary form 1 to 20.

Now is my question. How to find the smallest sum of pairs from my array in one elegant formula?

Here is my sample sheet: example sheet

Upvotes: 2

Views: 78

Answers (2)

Tom Sharpe
Tom Sharpe

Reputation: 34275

I know this isn't exactly your question and fair play if it gets marked down, but in your quest for an 'elegant formula', I was wondering if there was a more general way to get the pairs in the first place.

You can do it with by using two ranges offset by one cell together with the mod of the column number:

=ArrayFormula(query(
query({transpose({A17:H17;B17:I17;mod(column(A17:H17),3)})},"select Col1+Col2 where Col1 is not null and Col2 is not null and Col3>0")
,"select min(Col1) label min(Col1) ''"))

enter image description here

Upvotes: 0

player0
player0

Reputation: 1

=QUERY(QUERY({A17:B17;B17:C17;D17:E17;E17:F17;G17:H17;H17:I17};
 "select Col1+Col2 
  where Col1 is not NULL 
    and Col2 is not NULL"); 
 "select min(Col1) 
  label min(Col1)''")

0

Upvotes: 1

Related Questions