Search code examples
google-sheetsgoogle-sheets-formulagoogle-sheets-query

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


Solution

  • =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