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

Monday

How to Rank Data by Alphabetical Order in Google Sheets

Posted by   on

In a few scenarios, one may require to rank data by alphabetical order in Google Sheets. I’ve recently used this technique to sort a Pivot table by an nth text column.


To rank data by alphabetical order, we can use Match or Countif in Google Sheets.


It’s because the native RANK function doesn’t support ranking a text. After all, the ‘value’ parameter in it requires a number.


Syntax: RANK(value, data, [is_ascending])


So when you rank the alphabet “c” in the array "a";"c";"b", you would get #VALUE! error instead of 3.


As I have mentioned, we can use either the Match or Countif solutions to rank a text in Google Sheets.


Both are capable of returning multiple ranks. Here are a few examples.


Example to Rank Data by Alphabetical Order in Google Sheets


First, we will try the formulas without referring to data in a cell range. Instead, we will use the array within.


Non-Working Formula:


=rank("c","a";"c";"b")

Result: #VALUE!


Two Working Formulas:


=match("c",sort("a";"c";"b"),0)

Result: 3


=countif("a";"c";"b","<"&"c")+1

Result: 3


Can you please explain the logic behind the above two formulas, please?


Yep! Let me start with MATCH.


The Match here matches “c” in the sorted array "a";"b";"c" and returns the relative position of the matching value, which is 3.


Syntax: MATCH(search_key, range, [search_type])


The last parameter, i.e., search_type, does matter. You should set it to 0 (zero). Otherwise, if there are duplicates, the formula would return the relative position of the last occurrence.


That’s the logic behind using Match to rank data by alphabetical order in Google Sheets.


What about COUNTIF?


Syntax: COUNTIF(range, criterion)


The comparison operators work with numbers as well as text strings in Countif


If we use =countif("a";"c";"b","c"), it will return the count of “c”, which is 1.


If we use =countif("a";"c";"b","<"&"c")+1 it will return the rank of “c” in the given array.


How to Replace Array with Cell Range Here?


Above, we have used an array within the formula to return the rank of a text in Google Sheets.


Can we replace it (data) with a cell range?


Yep! Please see to it.


Rank Data by Alphabetical Order in Google Sheets

I have replaced the array "a";"c";"b" with B2:B in both Match and Countif formulas.


You can follow the below method when you would like to find the rank of all the strings in the data by alphabetical order.


Array Formulas to Rank Data by Alphabetical Order in Google Sheets


I want to find the rank of all text values in a list. I can then use that output in a Pivot table for the purpose I have already mentioned in the first paragraph of this tutorial.


Sample Data: A1:F8


Column to Rank Data by Alphabetical Order: B (B2:B8 as B1 contains filed label).


Here is how to do it using a non-array formula first.


In cell G2, insert the below formula and drag to G3:G8.


=COUNTIF($B$2:$B,"<"&$B$2:B2)+1

Array Formula to Rank Text in Google Sheets

range: $B$2:$B (used absolute cell range so won’t change when drag-down)


criterion: "<"&$B$2:B2 (used absolute+relative reference so changes part of the reference when dragging down)


Check each cell in G2:G8 to understand it. I mean, how relative and absolute cell references work.


If you prefer to use Match, insert the below formula in cell G2 and copy-paste (drag) down.


=match(B2,sort($B$2:$B),0)

search key: B2 (relative)


range: sort($B$2:$B) – absolute.


Can we use array formulas to rank data by alphabetical order?


Yes! Empty G1:G. Insert ="Helper";ArrayFormula(if(B2:B="",,COUNTIF(B2:B,"<"&B2:B)+1)) or ="Helper";ifna(ArrayFormula(match(B2:B,sort(B2:B),0)))in cell G1.


That’s all about how to rank data by alphabetical order in Google Sheets.


Thanks for the stay. Enjoy!


Related:-

No comments:
Write Comments

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