Sorting by formula - Biff

[Epinn]

Biff,

I think I have figured out why we use SMALL, COUNTIF, < etc. to sort text and just SMALL to sort numbers. If what I think is correct then it is really simple.

From Excel Help:
"Syntax

SMALL(array,k)

Array is an array or range of numerical data for which you want to determine the k-th smallest value."

The keyword is *numerical*. For text data set, we need to use COUNTIF and < to prepare an array of *numbers* for the SMALL functions to use.

{0;3;3;4;5;6;1....} Let me use 4 as an example. 4 means there are 4 items on the data set that are ranked alphabetically before the data item being checked.

Thanks for your help and I have learned so much. I have had fun but then I also ask why I spent so much time on learning how to sort by formula when I can just press the A->Z or Z->A buttons. Maybe this is a sign of me being a___ and I know I am not alone. <G>

About first and last values, yes you did say "alphabetically" when you presented me with the formulae. I was busy focusing on the other post about *dragging* down the formula to sort.

I think I can put "sort by formula" to bed now. Have to move on.

Cheers,

Epinn

 

[T. Valko]

Yes, you are correct.

Put those formulas in your stash.

Biff

Biff,

I think I have figured out why we use SMALL, COUNTIF, < etc. to sort text
and just SMALL to sort numbers. If what I think is correct then it is
really simple.

From Excel Help:
"Syntax

SMALL(array,k)

Array is an array or range of numerical data for which you want to
determine the k-th smallest value."

The keyword is *numerical*. For text data set, we need to use COUNTIF and <
to prepare an array of *numbers* for the SMALL functions to use.

{0;3;3;4;5;6;1....} Let me use 4 as an example. 4 means there are 4 items
on the data set that are ranked alphabetically before the data item being
checked.

Thanks for your help and I have learned so much. I have had fun but then I
also ask why I spent so much time on learning how to sort by formula when I
can just press the A->Z or Z->A buttons. Maybe this is a sign of me being
a___ and I know I am not alone. <G>

About first and last values, yes you did say "alphabetically" when you
presented me with the formulae. I was busy focusing on the other post about
*dragging* down the formula to sort.

I think I can put "sort by formula" to bed now. Have to move on.

Cheers,

Epinn