Sorting by formula - 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 |
Sorting by formula - Biff
Yes, you are correct.
Put those formulas in your stash. Biff "Epinn" wrote in message ... 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 |
All times are GMT +1. The time now is 10:29 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com