Are you tired of manually finding the first and second highest values in each row in Google Sheets? Well, fret no more! In this article, I’ll show you how to use the DMAX function to effortlessly retrieve those values. Trust me, it’s easier than you think!
Array Formula for Getting First and Second Highest Values in Each Row
To begin, let’s examine the formula for obtaining the second-highest value in each row using an array formula. But before we dive into that, let’s first understand how to get the first highest value in each row.
Previously, we relied on the LARGE function, which unfortunately doesn’t work as an array formula. However, I have come up with a simple solution using the DMAX function instead.
I won’t bore you with unnecessary details, but I’ll walk you through the step-by-step instructions to ensure you fully grasp the formula. Believe me, it’s a piece of cake!
Two Non-Array Formulas Using the LARGE Function
Let’s start with the non-array formulas for obtaining the first and second highest values in each row.
In cell H3, paste the following formula:
=large(B3:G3,1)Then, in cell I3, paste this formula:
=large(B3:G3,2)Using non-array formulas is perfectly fine if you only have a few rows of data. However, if your sheet contains numerous rows, it’s best to use array formulas.
Array formulas have several advantages. For instance, if you insert new rows, the array formula will automatically return the results in those rows as well. On the other hand, non-array formulas may require you to manually copy and paste the formula in each new row.
Array Formula to Get the First Highest Values in Each Row (DMAX)
Now, let’s delve into the array formula that will give us the first highest values in each row using the DMAX function. Don’t worry, I’ll break it down for you.
The DMAX function requires three arguments: database, field, and criteria. In our case, the database is the range from B3 to G7, the field represents the columns from which we want to retrieve the max values, and the criteria is the condition for determining the max values.
To better understand this, consider the following formula:
=ArrayFormula(dmax(TRANSPOSE({sequence(rows(B3:B7),1),B3:G7}),sequence(rows(B3:B7),1),{IF(,,);IF(,,)}))By transposing the range B3:G7 and applying the column-wise max, we can obtain the first highest values in each row. However, since we don’t have the required field labels and criteria, we need to use a workaround.
Here are the steps to follow:
- In cell A10, add a column with row numbers using the range B3:G7. We’ll only use the numeric columns in DMAX.
=ArrayFormula({sequence(rows(B3:B7),1),B3:G7})- Transpose the data orientation from step 1.
=ArrayFormula(TRANSPOSE({sequence(rows(B3:B7),1),B3:G7}))Now, we have a table (B10:E16) that we can utilize for the DMAX function.
- Find the column-wise max of this table.
In cell H3, insert the following formula:
=ArrayFormula(dmax(TRANSPOSE({sequence(rows(B3:B7),1),B3:G7}),sequence(rows(B3:B7),1),{IF(,,);IF(,,)}))To remove the helper range A10:E16, replace it in the formula with the formula from cell A10.
Congratulations! You now have the formula for obtaining the first highest values in each row in Google Sheets.
Array Formula to Get the Second Highest Values in Each Row (DMAX)
Getting the second highest values in each row is just as simple as the first. Follow these steps:
- Insert the previously mentioned formula in cell I3.
- Replace the range B3:G7 in the I3 formula with the following formula:
=if(B3:G7=H3:H7,0,B3:G7)Confused? Don’t worry, I’ll explain.
Since we already have the first largest values in cells H3:H7, we need to test these values against the range B3:G7 and replace the matching values with 0s. This can be achieved with a straightforward IF logical formula.
Here’s the complete formula for obtaining the second highest values in each row (insert in cell I3):
=ArrayFormula(dmax(TRANSPOSE({sequence(rows(B3:B7),1),if(B3:G7=H3:H7,0,B3:G7)}),sequence(rows(B3:B7),1),{IF(,,);IF(,,)}))And that’s it! You now have the formula for retrieving the second highest values in each row.
How Can I Include More Rows in the DMAX Formulas?
I understand that you may want to use the above formulas in a large dataset. Rest assured, these DMAX formulas are perfect for such scenarios. Feel free to extend the range in the formulas to accommodate your needs.
Simply delete any unnecessary rows and columns and insert new ones as required.
Here are the modified formulas for an open range in cells H3 and I3:
H3 Open Range Formula:
=ArrayFormula(dmax(TRANSPOSE({sequence(rows(B3:B),1),B3:G}),sequence(rows(B3:B),1),{IF(,,);IF(,,)}))
I3 Open Range Formula:
=ArrayFormula(dmax(TRANSPOSE({sequence(rows(B3:B),1),if(B3:G=H3:H,0,B3:G)}),sequence(rows(B3:B),1),{IF(,,);IF(,,)}))Please note that these formulas may return 0 values as the first and second highest values for rows that are entirely blank. To address this, you can utilize the IF function to fine-tune the formulas.
So, my friend, that’s all you need to know about retrieving the first and second highest values in each row in Google Sheets. I hope you found this tutorial helpful. If you have any questions or need further assistance, don’t hesitate to reach out.
Enjoy navigating the wonders of Google Sheets, and remember to visit Crawlan.com for more fantastic insights!
