How to arrange numbers in an array into low, medium, and high in Google Sheets?

There is no specific command or function in Google Sheets for such type of separating numbers into individual groups. I have written one formula for the same.

In this Google Sheets tutorial, let’s learn to separate or arrange an array of numbers into low, medium, and high using a formula.

In addition to this, you can learn to test the position of a number in an array. I mean whether the specified number is falling in the low, medium, or high category.

For the former solution, I will use a PERCENTRANK based formula together with the FILTER function. For the latter, in addition to the PERCENTRANK, I will use the IF logical test.

How to Arrange an Array of Numbers into Low, Medium, and High in Google Sheets

I have the below numbers in an array (A2:A).

The array of numbers are in A2:A. I have used three formulas to arrange the numbers in A2:A into low, medium, and high. I mean into three separate ranges.

The formulas are in the cells E2 (low), F2 (medium), and G2 (high).

Here is the main formula that will help us to filter the numbers in A2:A into low, medium, and high in Google Sheets.

In cell C2, insert the below PERCENTRANK array formula.

=ArrayFormula(PERCENTRANK(A2:A,A2:A))

The above formula returns the percentage ranks, aka percentile of all the numbers in A2:A in the data set (A2:A).

The MIN value in the data set will get 0 percentile, and the MAX value in the data set will get 1 percentile. That means the percentile of the numbers will be from 0 to 1.

So to classify the numbers into three individual arrays, we need to follow the below logic.

Low:

To arrange the array of numbers in A2:A into the Low category, we should filter A2:A if C2:C<=1/3.

Medium:

Filter A2:A if C2:C>1/3 and C2:C<=1/3*2

High:

Filter A2:A if C2:C>1/3*2

Based on the above, we can write the formula using the FILTER function as below.

E2 Formula # 1:

=filter($A$2:$A,$C$2:$C<=1/3)

F2 Formula # 2:

=filter($A$2:$A,$C$2:$C>1/3,$C$2:$C<=1/3*2)

G2 Formula # 3:

=filter($A$2:$A,$C$2:$C>1/3*2)

Finally, you can replace $C$2:$C in all the three formulas with the C2 formula, i.e., PERCENTRANK(A2:A,A2:A), and remove the formula from C2.

This way we can arrange an array of numbers into Low, Medium, and High category in Google Sheets.

Test Whether a Value Falls in Low, Medium, or High in a Range

I can explain this topic without much effort using the below GIF image.

The formula in cell I2 finds the position of a value in a dataset.

Here we are testing an external number (please see the cell H2) in the array (A2:A) for its position (rank). I mean whether the number in the cell H2 falls in low, medium, or high in the range.

The logic is the same as above. But, here, we will use IF instead of FILTER.

=ifna(
     IF(
        PERCENTRANK(
           A2:A,H2
        )<1/3,
        "Low",
        if(
           PERCENTRANK(A2:A,H2
           )<1/3*2,
           "Midium",
           "High"
        )
     ),"Number Doesn't Exist in the Array"
)

The PERCENTRANK formula returns the percentile of the number in F2 in the range A2:A. The IF tests the percentile as below.

If percent rank is;

The IFNA used for the last parameter, i.e., to return the text “Number Doesn’t Exist in the Array”. That’s all about how to arrange an array of numbers into Low, Medium, and High in Google Sheets.

Thanks for the stay, enjoy!

Resources:

• Percentile Rank Wise Conditional Formatting in Google Sheets.
• Average of Top N Percent of the Values in Google Sheets.
• How to Find Rank of a Non-Existing Number in an Existing Data Range.
• How to Use RANK Function in Google Sheets – Example Formula.
• Flexible Array Formula to Rank Without Duplicates in Google Sheets.
• How to Use the RANK.AVG Function in Google Sheets.
• How to Use the Percentile Function in Google Sheets.