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...

Tuesday

Max and Min Strings Based on Alphabetic Order in Google Sheets

Posted by   on

This post is all about the MIN() and MAX() equivalent formulas for strings in Google Sheets.


We can use the functions QUERY, SORTN, or a combo formula to find max and min strings based on alphabetic order in Google Sheets.


We will learn all of them. That will help us pick the best formula for finding the maximal and minimal strings in Google Sheets.


The below table contains a few strings and their corresponding value w.r.t. their position based on alphabetic order from lowest to highest.














































StringsPositions Based on
Alphabetic Order
ACR0
PQR3
WXYZ6
LMN1
OPQ2
WX3154
WX3165
WXYZ6
WXYZ6


If you check column 2, you can find that the max value is 6, and the min value is 0.


The corresponding strings in the first column are “WXYZ” and “ACR,” respectively.


Positions Based on Alphabetic Order – What does it mean?


If you SORT the strings in the first column, they will be arranged based on the values in column 2.


In A-Z sort, the values will be arranged in the order “ACR,” “LMN,” “OPQ,” and so on.


=sort(A2:A10)

In Z-A sort, the values will be arranged in the order “WXYZ,” “WXYZ,” “WXYZ,” “WX316,” and so on.


=sort(A2:A10,1,0)

Formula to Find Max and Min Strings in Google Sheets

We can use COUNTIF to get the string position in column B.


I have used the below array formula in B2.


=ArrayFormula(COUNTIF(A2:A10,"<"&A2:A10))

Formulas to Find Max and Min Strings Based on Alphabetic Order in Google Sheets


The real question is how to find the max and min strings in Google Sheets.


As I have mentioned, I have a few good options in front of me.


Before going to the simplest one, let’s make use of the numbers in column B. I’ll use them in a VLOOKUP formula.


Option 1 – Vlookup


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


Search_Key


Find the max/min value in the range B2:B10 using the MAX/MIN functions. Then use them as the search keys in two Vlookup formulas.


Range


The range will be B2:B10,A2:A10, a virtual array.


Index


The index (output column) will be 2.


Max String Formula:


=vlookup(max(B2:B10),B2:B10,A2:A10,2,0)

Min String Formula:


=vlookup(MIN(B2:B10),B2:B10,A2:A10,2,0)

In these two formulas, feel free to replace B2:B10 with the corresponding COUNTIF formula. So we won’t need to use any helper range.


Option 2 – Maximal and Minimal Strings Using QUERY Function in Google Sheets


No doubt, QUERY is one of the functions that make us addicted to Google Sheets.


It helps us perform various data manipulations.


The QUERY Max/Min functions work with non-numeric columns.


If you are one of my regular readers, you may have already experienced that here – How to Aggregate Strings Using Query in Google Sheets.


We can query column A as below to return the maximal string in Google Sheets.


=query(A2:A10,"Select max(A) label max(A)''")

Regarding minimal string, the following Query will return that.


=query(A2:A10,"Select min(A) label min(A)''")

You May Like:- How to Sum, Avg, Count, Max, and Min in Google Sheets Query.


Option 3 – The Best Formula to Find Max/Min String in Google Sheets


We are Google Sheets users. So let’s think differently.


In the beginning, I have used two SORT formulas to sort the strings in column A in ascending and descending order.


If you extract the first values in both those formula results, we can meet our requirements. But SORT doesn’t offer that.


We can either use Array_Constrain or Index with SORT or use another similar function.


There is a dedicated function in Google Sheets. It can not only sort a range but also limit the output to ‘n’ rows.


Yep! SORTN. That’s the function I am going to use.


MAX String:


=sortn(A2:A20,1,0,1,0)

MIN String:


=sortn(A2:A20)

The above two are the best way to find max and min strings based on alphabetic order in Google Sheets.


That’s all about MIN/MAX equivalent formulas for strings in Google Sheets. Thanks for the stay. Enjoy!


Resources (Unique Tutorials)

No comments:
Write Comments

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