Why is Sort range not working?

Question

Minimal example: https://docs.google.com/spreadsheets/d/168bq72DGKNxNLXt-Jzbh95iQ2Gu93IzmPp38SB0od5E/edit?usp=sharing

inProduction    velocity(%)
TRUE    0.027192
TRUE    0.026422
TRUE    0.246903
TRUE    0.03061
TRUE    0.02716
FALSE   0.001519
FALSE   0.000254
FALSE   0.001207
FALSE   0.000156
FALSE   0.000177
FALSE   0.000501
Z N/A   18.17028428

Clicking

Data -> Sort range -> Advanced range sorting options -> Data has header row + Sort by inProduction Z->A

does not put the Z N/A entry on top

Things checked:

• All data format is set to Text in that column

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