Are you tired of copying and pasting the MIN function in Google Sheets to find the minimum value in each row? Well, you’re in luck! In this article, I’ll show you how to use MIN in Array in Google Sheets in a customized way to get expanded results.
Why MIN in Array is the Way to Go
The MIN function is commonly used to find the minimum value in a range of numeric values. However, when you have a large dataset with multiple rows, manually applying the MIN function in each cell becomes tedious and time-consuming. Not to mention, it can easily break when you insert new rows.
But fret not! I have a solution that will save you time and hassle. By utilizing a customized MIN array formula based on QUERY, you can automate the process and find the minimum values in each row with ease.
MIN in Array Using QUERY
Here’s the master formula you can use to find the minimum values in every row in your Google Sheets:
=query( transpose( query( transpose(A3:G), "Select "& REGEXREPLACE( join("",ArrayFormula(if(len(A3:A),"Min(Col"&ROW(A3:A)- ROW(A3)+1&"),","")) ), ".z","" )&"") ), "Select Col2" )Simply replace the range reference in the formula based on your data range. It’s that easy!
Understanding the Formula
You might be wondering how this MIN array formula works. Let me break it down for you. The QUERY function in Google Sheets can find minimum values in columns. By using the Transpose function, we can change the data orientation from rows to columns.
In a nutshell, the formula transposes the rows to columns and uses them as the columns in the QUERY Select clause. This allows the function to return the minimum value in each column, effectively finding the minimum values in each row.
Alternative Formula for Limited Rows
If you have a limited number of rows in your data range, you can use the following alternative formula:
=query( transpose( query( transpose(A3:G), "Select Min(Col1), Min(Col2), Min(Col3),Min(Col4)" ) ),"Select Col2" )However, keep in mind that this formula is limited to the existing number of rows in the range. If you need to automate the number of rows, I recommend using the master formula mentioned earlier.
Automating Column Numbering in QUERY
If you’re curious about automating column numbering in the QUERY function, I have a tutorial that covers this topic in more detail. Check out “How to Find Max Value in Each Row in Google Sheets” on Crawlan.com for a step-by-step guide on using Join, Len, Row, and Regexreplace to automate the number of columns in the QUERY function.
An Alternative with BYROW
Another approach to get expanded results using MIN in Array is by utilizing the BYROW function. Here’s the formula you can use:
=byrow(B3:G,lambda(r,if(counta(r)=0,,min(r))))This formula requires a numeric range (B3:G) and uses the lambda function to iterate through each row and find the minimum value. It’s a handy alternative if you prefer a different method.
Conclusion
Now you know how to use MIN in Array in Google Sheets to get expanded results effortlessly. Whether you choose to use the customized QUERY formula or the BYROW function, you’ll save time and avoid the hassle of manual copy-pasting. So go ahead and give it a try in your own Google Sheets projects. Happy calculating!
Image Source: Copy the Google Sheets Data
