Search code examples
google-sheetsgoogle-sheets-formula

how to find the minimum product of two rows

I want to find the minimum of the products of each number with the number next to it

enter image description here

How do I find the minimum of all the products of each number in each row? For example here, the minimum would be 2 because 2x1 = 2 is less than 6x1 = 6 and so on. I could do this by making a separate column of all the multiples and find the minimum of that, but I don't want to unnecessarily make another column.

Solution

  • If I understood your question correctly you want to find the minimum value of the product of Column A and B.

    Try:

    =ARRAYFORMULA(MIN(FILTER(A2:A12,A2:A12>0)*FILTER(B2:B12,B2:B12>0)))

    Result: enter image description here

    Explanation:

    To explain simply you just need to use multiply the columns and use the MIN() function with ArrayFormula() like this =ARRAYFORMULA(MIN(A2:A12*B2:B12)), but this will return 0 if there are blank rows in the range since it is the minimum value. So you have to use FILTER() to both columns to exclude the blank rows and 0 values first before you multiply them.

    Take note: Both the columns have to be equal size otherwise it would throw an error.