Sort excel formula output?

 

I have made an excel column with a formula. Now I would like to sort this column by number... Just using sort won't work, because it sorts the formula ( =if(; =abs( etc.) and not the numbers that I see. Can somebody help me with this please?

Yes you can sort Excel/LibreOffice cells using just formulas only.

  • My unsorted numbers ( or words ) are listed horizontally. eg B29 – G29 ( 6 numbers ). I choose 29 so that it wont be confused with the 1 used in RANK function :D

  • My sorted numbers shall be in cells J29-O29.

  • The formula for cell J29 is

    • =IF(RANK($B29,$B29:$G29,1)=1,$B29,IF(RANK($C29,$B29:$G29,1)=1,$C29,IF(RANK($D29,$B29:$G29,1)=1,$D29,IF(RANK($E29,$B29:$G29,1)=1,$E29,IF(RANK($F29,$B29:$G29,1)=1,$F29,IF(RANK($G29,$B29:$G29,1)=1,$G29,$Q29))))))

  • The formula for cell K29 is … just convert all the “=1″ into “=2″

  • The formula for the rest is “=3″ for L29 and so on till “=6″ for O29.

  • The RANK function will rank every cell in the range. There will not be any unranked.

  • The last part .. , IF(RANK($G29,$B29:$G29,1)=1,$G29,$Q29) If there are more than one same number .. meaning there are more than one number of the same rank.. it would duplicated the previous number of the same rank.

  • To sort words, you need to first convert words into ASCII using the CODE function. The RANK function works only with numbers.

    • Are you trying to take the output value from a formula field and then have them sorted in the following column?

    If so try this: 

     =large(b$1:b$5000,rows($a$1:a1))
 rows($x$1:x1) =1 dragged down becomes
 rows($x$1:x2) ie =2

    It counts the number of rows in the range.

    If you are looking for sorting the numbers alone in a particular column you can try this: 

    =LARGE($B$1:$B$5000,COUNT(B1:B5000)

    =LARGE(ARRAY,K) this will return the kth largest number in the given array here =COUNT(B1:B5000) will return 7 which is the value of k.

    Our final formula will look like =LARGE($B$1:$B$5000,5000) which is the smallest number in the entire range.

    Press Ctrl+Enter by selecting the range from b1:b5000 so that it will sort the numbers from b1:b5000.

    链接地址: http://www.djcxy.com/p/10440.html

    上一篇: Internet Explorer拦截XML响应

    下一篇: 排序Excel公式输出？