Row-Wise MIN Using DMIN in Google Sheets

Are you tired of manually finding the minimum value in each row of your Google Sheets? Well, fear not! In this article, I will show you how to use the powerful DMIN function to retrieve the row-wise min values in Google Sheets. Trust me, it’s easier than you think!

The Unfamiliar Formula

You might be wondering, what is this DMIN function? Don’t worry, you’re not alone. Many people are unfamiliar with this handy formula. But fret not, my friend. I’m here to guide you through it.

One of my readers, “Lightmare,” suggested using the DMIN function to find the maximum value in each row. And guess what? The same applies to finding the minimum value too! So let’s dive into the DMIN formula and unveil its hidden powers.

How It Works

To begin with, you need to place the DMIN formula in the very first row of your Google Sheet. This formula will then return the minimum value in each row. But here’s the exciting part: it’s not just a regular formula; it’s an array formula!

Now, you might be thinking, “What’s an array formula?” Well, it’s a formula that operates on multiple values instead of a single value. In this case, the DMIN array formula will work its magic and give you the row-wise min values you desire.

But there’s a catch. In a previous formula, I used the JOIN function, which has its limitations. It might work fine for a limited number of rows, but what if you have a massive data set? That’s where the DMIN formula comes in. It can handle even the largest data sets, though it might slow down your sheet a bit. But trust me, it’s worth it!

Let’s Get Started!

Before we dive into the formula, I recommend keeping only the required number of columns and rows in your sheet. This will help the formula perform better. Once you’ve trimmed your sheet down, let’s move on to the exciting part.

Below is a sample sheet I prepared for you. It contains data in the range A1:E1000. In cell F1, you will find the DMIN formula that returns the minimum value in each row of that column.

You can find the complete sample sheet, including the formula, at the end of this tutorial. But for now, let me break down the formula in cell F1 for you.

={"Min";ArrayFormula(if(len(A2:A),(DMIN(transpose({{"Row";sequence(rows(A2:A1000),1)},B1:E1000}),sequence(1000,1,2),transpose({"Row",B1:E1}))),))}

Making Adjustments for Your Data

Now that you’ve seen the formula in action, you might be wondering how to adapt it for your own table. Don’t worry; it’s simpler than you think. Here are the changes you need to make:

1. Change the range B1:E1000 to match your own numeric column range. This range represents the columns from which you want to find the row-wise min values.

2. Make sure the range B1:E1 contains the appropriate headers for your numeric columns. The DMIN function relies on these headers, so make sure they’re correct.

3. The formula part rows(A2:A1000) calculates the total number of rows in your sheet (excluding the header). If your table starts from a different row, make sure to adjust this part accordingly.

4. Finally, change len(A2:A1000) to len(A11:A1000) if your table starts from a different row, and replace A with the reference for the first column in your table.

Simply make these changes, and the DMIN formula will adapt to your specific table, giving you the desired row-wise min values.

The Magic Behind the Formula

Now that we’ve covered the adjustments you need to make, let’s dive into the logic behind the DMIN array formula for row-wise min in Google Sheets.

The DMIN function is typically used to retrieve the minimum value in a single column or multiple columns. However, we can leverage this function’s power to find the minimum value in each row as well.

To achieve this, we’ll first transpose the data and then apply the DMIN formula. This will transform the result into row-wise min values.

But transposing the data comes with its own challenges. Since DMIN is a database function, it heavily relies on the header row (fields) during the calculation. Therefore, we need to make some adjustments.

Let’s break it down step by step, starting with a limited range of data (A1:E6) to simplify the explanation.

The Formula for a Limited Range

Here’s the formula we’ll be using:

=ArrayFormula(DMIN(transpose({{"Row";sequence(rows(A2:A6),1)},B1:E6}),sequence(5,1,2),transpose({"Row",B1:E1})))

This formula consists of three main parts: the database, field, and criteria.

The Database

The database part is responsible for transposing the data to resemble the following format:

Row  | B | C | D | E
---------------------
1    | 7 | 8 | 5 | 3
2    | 2 | 4 | 6 | 1
3    | 9 | 3 | 2 | 7
4    | 6 | 5 | 1 | 9
5    | 8 | 7 | 4 | 2
6    | 1 | 2 | 3 | 5

This is achieved by replacing the original A1:A6 range with row numbers ({"Row";sequence(rows(A2:A6),1)}) and combining it with the rest of the range (B1:E6).

The Field

The field part determines which columns we want to find the minimum values from. In this case, the fields correspond to columns 2 to 6 (sequence(5,1,2)). We populate the field numbers vertically instead of horizontally.

The Criteria

The criteria part is represented by =transpose({"Row",B1:E1}), which includes the header row of the database. This ensures that the DMIN function behaves as expected.

Now, when you apply the DMIN formula to this transposed data, you will find the column-wise min values. However, since we transposed our original data, the result will be the desired row-wise min values.

To simplify the formula, you can separate the three parameters into different cells and code the formula accordingly. This way, you’ll have an even clearer understanding of the row-wise min values using the DMIN database function.

That’s all there is to it! I hope this article has enlightened you on how to use the DMIN formula to find the row-wise min values in Google Sheets. If you want to explore further, feel free to check out the complete sample sheet linked below.

Check out the complete sample sheet here

Stay tuned for more exciting Google Sheets tips and tricks. Happy sheeting!