SORT function

Sorts and returns the given array. SORT({ 3, 1, 2 })SORT({ 3; 1; 2 }) returns the array { 1, 2, 3 }{ 1; 2; 3 }.

If a sort order is not specified, the given values are sorted in ascending order (where the largest values appear last in the results).

To specify the sort order, specify either SortOrder.AscendingSortOrder,Ascending or 1 for ascending order (greater values appear last in the returned array) and SortOrder.DescendingSortOrder,Descending or -1 for descending order (greater values appear first in the returned array). This function accepts numbers in addition to descriptive names for the sort order to ensure compatibility with spreadsheets.

These formulas sort the array in ascending order and return the array { 1, 2, 3 }{ 1; 2; 3 }:

SORT({ 3, 1, 2 })SORT({ 3; 1; 2 })

SORT({ 3, 1, 2 }, 1)SORT({ 3; 1; 2 }; 1)

SORT({ 3, 1, 2 }, SortOrder.Ascending)SORT({ 3; 1; 2 }; SortOrder,Ascending)

These formulas sort the array in descending order and return the array { 3, 2, 1 }{ 3; 2; 1 }:

SORT({ 3, 1, 2 }, -1)SORT({ 3; 1; 2 }; -1)

SORT({ 3, 1, 2 }, SortOrder.Descending)SORT({ 3; 1; 2 }; SortOrder,Descending)

Sort details

This function can sort arrays containing errors, numbers, colors, logical values and text strings. If two numbers are compared, the larger number is considered greater than the smaller number. If two logical values are compared, TRUE is considered greater than FALSE. If one of the two compared values is blank, the blank value is considered greater than the non-blank value.

Color values are compared by first converting them to the Hue-Saturation-Lightness-Alpha (HSLA) color space, as though the HSLA Calcapp function was used. (The "alpha" value denotes the extent to which the color is transparent or opaque.)

If two colors are compared which differ in terms of their alpha values, the less transparent color is considered greater than the other color. If they have the same alpha value, the hue is considered next, where a color with a greater hue value is considered greater than the other value. If they have the same alpha and hue values, the saturation is considered next, where a color with a greater saturation value is considered greater than the other value. Finally, if they have the same alpha, hue and saturation values, the lightness is considered, where a color with a greater lightness value is considered greater than the other value.

Text strings are compared using lexicographic ordering, which is mostly the same as dictionary order. That means that "b" is considered greater than "a". Lower-case letters are considered greater than upper-case letters. This is different from how the <, <=, >, >=, = and <> operators work, which are case-insensitive.

When values of different types (say, numbers and text strings) are compared to one another, errors are considered greater than blank values, blank values are considered greater than numbers, which are considered greater than colors, which are considered greater than text strings, which in turn are considered greater than logical values.

Examples

SORT({ 3, 2, 1 })SORT({ 3; 2; 1 })

Returns { 1, 2, 3 }{ 1; 2; 3 } because arrays are sorted in ascending order by default (greater values appear last).

SORT({ 3, 2, 1 }, SortOrder.Ascending)SORT({ 3; 2; 1 }; SortOrder,Ascending)

Returns { 1, 2, 3 }{ 1; 2; 3 }, when the sort order is set to ascending (greater values appear last).

SORT({ 3, 2, 1 }, SortOrder.Descending)SORT({ 3; 2; 1 }; SortOrder,Descending)

Returns { 3, 2, 1 }{ 3; 2; 1 }, when the sort order is set to descending (greater values appear first).

REVERSEARRAY(SORT({ 3, 2, 1 }, SortOrder.Ascending))REVERSEARRAY(SORT({ 3; 2; 1 }; SortOrder,Ascending))

Returns { 3, 2, 1 }{ 3; 2; 1 }, despite the fact that the sort order is explicitly set to SortOrder.AscendingSortOrder,Ascending (greater values appear last), because REVERSEARRAY is passed the result.

SORT({ 3, 2, 1 }, 1)SORT({ 3; 2; 1 }; 1)

Returns { 1, 2, 3 }{ 1; 2; 3 }, when the sort order is set to ascending (greater values appear last). Here, 1 is used instead of SortOrder.AscendingSortOrder,Ascending.

SORT({ 3, 2, 1 }, -1)SORT({ 3; 2; 1 }; -1)

Returns { 3, 2, 1 }{ 3; 2; 1 }, when the sort order is set to descending (greater values appear first). Here, -1 is used instead of SortOrder.DescendingSortOrder,Descending.

SORT({ Color.Red, NA(), BLANK(), 2, TRUE, "test" })SORT({ Color,Red; NA(); BLANK(); 2; TRUE; "test" })

Returns { TRUE, "test", Color.Red, 2, BLANK(), NA() }{ TRUE; "test"; Color,Red; 2; BLANK(); NA() }, because logical values, text strings, color values numbers, blank values and errors are prioritized in that order.