Why is Sort range not working?

Question

In Google Sheets I have a column inProduction that contains one of the following text values: TRUE, FALSE, Z N/A.

When I sort my range I expect the following order:

# Ascending   # Descending

inProduction  inProduction
    FALSE         Z N/A
    TRUE          TRUE
    Z N/A         FALSE

However, when I sort my data range using Data > Sort range > Advanced range sorting options, in descending order by inProduction with "Data has header row" selected, Z N/A is incorrectly sorted to the bottom instead of the top.

I've confirmed that the inProduction column is formatted as text.

How can I get the correct sort?

Link to example spreadsheet

Example Incorrect Result:

inProduction   velocity(%)
TRUE           0.027192
TRUE           0.026422
FALSE          0.001519
FALSE          0.000254
FALSE          0.001207
Z N/A          18.17028428

Answer 1

Chances are that the values TRUE and FALSE are not text but Boolean values. The sort order used by Data > Sort sheet and Data > Sort range puts Booleans after text strings, when when you sort descending, they go to top.

You can verify whether those values are text or Boolean with the =istext() and islogical() functions.

To make it work, first format the column as Format > Number > Plain text, then enter the values as true and false or TRUE and FALSE. Alternatively, use formulas like ="true".