How To Enable Gaming Mode on Android 12

Back in Februry, folks at XDA had spotted an unreleased Gaming Dashboard in the works for Android 12. But up until now, there were no clear...

Friday

How to Retrieve Column Header of Min Value in Google Sheets

Posted by   on

Google Sheets has abundant formula options for retrieving the column header of a min value in a row.


In this post, I am sharing with you two of the best formulas from among them. One is a non-array formula, and the other is, of course, an array formula.


Both of them work differently.


If there is more than one min value in a row, the non-array formula will return multiple column headers.


At the same time, the array formula will only return the first column header.


But the advantage of the array formula is it would return the min value header in every row.


Please note that here column header means field label in the topmost row of your table.


Formula to Return the Column Header of Min Value in Google Sheets


Assume five employees are working under you. You want to assess their daily sales and to find the day-wise underperformer.


Here is the sample data in hand.


You can find the employee names on the column header (header row) and their date-wise sales in corresponding columns.






















































































AmyDaveMaxineMalcolmEdgar
10/6/21 Thu31538
11/6/21 Fri62534
12/6/21 Sat
13/6/21 Sun
14/6/21 Mon25411
15/6/21 Tue100
16/6/21 Wed33412
17/6/21 Thu1
18/6/21 Fri64


We can use QUERY or SORTN like functions for this purpose. But I would prefer Filter here.


Assume the above table is in the range A1:F10. Then we can use the below Filter formula in cell H2. Drag it down until cell H10 to return the column header of min value in every row.


=ifna( filter( $B$1:$F$1, B2:F2=minifs(B2:F2,B2:F2,">0"), B2:F2<>"" )
)

Can you please explain this Google Sheets formula?


Yep! Here you go!



  1. The MINIFS return the min value of the sales quantity in B2:F2 excluding zero and blanks.

  2. The FILTER formula filters the min value headers (here the employee names) matching the MINIFS result.

  3. In a blank row or a row that only contains 0, the MINIFS will return 0. The second condition in FILTER, i.e., B2:F2<>"", is to return #N/A in that case.

  4. The IFNA removes the above error.

  5. When you drag the formula down, it applies to other rows.


See this example live (GIF).


Column Header of Min Value - Non Array Formula

If you are a Google Sheets enthusiast, you might want to know the ‘rule’ I have used to format the range B2:F10. Here it is.


=and(len($H2),$B2:$F2=minifs($B2:$F2,$B2:$F2,">0"))

You can use this rule in Format > Conditional formatting.


Array Formula to Retrieve the Column Header of Min Value


I have an array formula to retrieve the column header of the min value in Google Sheets. I have already mentioned the same. The formula has a limitation.


What’s that?


I mean, in the above example, the formula returns the names “Malcolm” and “Edgar” in row # 6 as the min value in that row is 1, which repeats twice.


My following array formula would only return the first min header, i.e., “Malcolm.”


=ArrayFormula( ifna( if(len(A2:A), vlookup( row(A2:A)&"~"& dmin( transpose(if(A2:F>0,A2:F,)), sequence(rows(A2:A),1), if(,,);if(,,) ), Query(split(flatten(row(A2:A)&"~"&if(B2:F>0,B2:F,)&"🐟"&B1:F1),"🐟"), "Select * where Col1 is not null and Col2 is not null") ,2,0 ), ) ) )

Column Header of Min Value - Array Formula

If you prefer to use this array formula to retrieve the column header of min value in each row in Google Sheets, you may want to go through the formula explanation.


But please keep remembering the disadvantage of this formula.


If there are multiple min values in a row, the array formula would only return the min header of the first occurrence from left to right.


In row # 6, it only returns the header “Malcolm.” The name “Edgar” is missing!


Formula Explanation


I am going to elaborate on the formula for you.


Generic Formula (Vlookup Based): vlookup(row_nos&min_each_row,unpivot_table,2,0)


In the above formula, I have used open ranges A2:A, A2:F, and B2:F. For the explanation, here I am using closed ranges A2:A10, A2:F10, and B2:F10.


Vlookup Syntax: VLOOKUP(search_key, range, index, [is_sorted])


Yep! We have used VLOOKUP to retrieve the column header of the min value excluding 0 in each row. Here are the expressions/references used in the arguments.


SEARCH KEY: row_nos&min_each_row
RANGE: unpivot_table
INDEX: 2
IS SORTED: 0


I know I must explain the search key and range in Vlookpup. So that, you can understand how the Vlookup returns the column header of min value in each row in Google Sheets.


SERARCH KEY


The search key here is the combination of min and row numbers (row_nos&min_each_row) in each row. Here is that portion of the formula.


row(A2:A10)&"~"&
dmin( transpose(if(A2:F10>0,A2:F10,)), sequence(rows(A2:A10),1), if(,,);if(,,)
)

It would return the following result, if you use it as an ArrayFormula.


min combined with row

I have used DMIN as per the logic detailed here – Row-Wise MIN Using DMIN in Google Sheets.


RANGE


Since we have the row numbers combined with the min values in every row, we require a similar range for vertical lookup.


We can then only retrieve the column header of the min values.


What can we do here? Let’s unpivot the range (dataset) in a specific way.


The range is actually the unpivot data in B1:F10 (unpivot_table). Additionally, the row numbers from A2:A10 combined with the values (sales quantities) in B2:F10.


Here is the relevant part from the formula (range closed). The FLATTEN function with SPLIT does the job.


Query(split(flatten(row(A2:A10)&"~"&if(B2:F10>0,B2:F10,)&"🐟"&B1:F1),"🐟"), "Select * where Col1 is not null and Col2 is not null")

It will work with the array formula as standalone in Sheets. Here is that output.



Related: A Simple Formula to Unpivot a Dataset in Google Sheets.


Here, we have used the Vlookup function to lookup the search keys in the first column of the range to return the names from the second column.


That’s all. Thanks for the stay. Enjoy!


Related: Column Header of Max Value in Google Sheets Using Array Formula.

No comments:
Write Comments

Hello Friends, welcome to autobloginc.blogspot.com we Hope You'll like it - COntact US
!!THANK YOU FOR YOUR SUPPORT!!